MSSQL расчет средней стоимости материала

[ТипичныйСтудент]

Добрый вечер! ОЧЕНЬ прошу помощи! Задача следующая, дана таблица, нужно рассчитать среднюю стоимость:

declare @tbl table (dt datetime, pr int, obj int, kolvo numeric(18,6), cena money, summa money, sredcena money, ostatok numeric(18,6));
declare @a int;
insert into @tbl values('2012-01-01', 1, 1, 10, 20, null, null, null)
insert into @tbl values('2012-01-05', 1, 1, 15, 50, null, null, null)
insert into @tbl values('2012-01-02', 1, 2, 5, 28, null, null, null)
insert into @tbl values('2012-01-05', -1, 1, 6, 26, null, null, null)
insert into @tbl values('2012-01-03', 1, 2, 3, 53, null, null, null)
insert into @tbl values('2012-01-06', -1, 2, 5, 48, null, null, null)
UPDATE @tbl SET summa = cena*kolvo;
select * from @tbl

т.е. надо найти итоговую сумму всех сумм и разделить ее на общее количество. И так для каждого наименования товара (наименования пока 1 и 2 в столбце obj.
Столбец pr содержит информацию о том, поступил товар (1) или его списали (-1, если он поступил, то в остаток=остаток (изначально обнуленный)+кол-во, если списали, то остаток=остаток-кол-во, опять же для каждого товара ИНДИВИДУАЛЬНЫЙ РАСЧЕТ!!

дайте хотя бы идеи как это сделать...
я думала сделать выборку по наименованию, посчитать количество товара каждого наименования (сколько 1, сколько 2 и т.д.), затем что-то вроде :

set @a = (select count(*) from @tbl where obj = 1);
update top (@a) @tbl set ostatok = case  when pr=1  then @ost + kolvo when pr=-1 then @ost - kolvo
end
from @tbl;

но сие чудо работать отказывается....

[Serge_Bliznykov]

Вы, как мне кажется, путаете и себя и нас.
что Вы пытаетесь сделать?
Остаток - он же ОДИН для каждого товара (как, кстати и средняя цена). Зачем эти данные, которые можно (и нужно) получать запросом писать в таблицу, да ещё и в каждую строку таблицы!

[Аватар]

Если уж рассчитывать остаток и среднюю цену с записью в таблицу, то напрашивается вторая таблица. Если вообще их нужно в таблицу писать. Что касается расчета средней цены остатка, то судя по данным прихода и расхода аж бегом остаток в количественном выражении может быть нулевым, а в стоимостном не нулевым. Или наоборот. Что тогда? Особенно первый вариант интересен, в связи с делением на ноль. Второй кстати тоже, поскольку можно поиметь нулевую цену, или отрицательную в случае разнознаковых сумм

[ТипичныйСтудент]

уу я далека от бухгалтерии...была задача с такими исходными данными и задание: рассчитать средние цены, с учетом прихода и расхода...
вот допустим пришло 5го числа 5 шт по 10р и 10го 3шт по 8р, а списываться будут 2шт по СРЕДНЕЙ цене...вот ее и надо найти..
ср_цена=((5*10) +(3*8))/(5+7)
если не ошибаюсь...
вот
и после списывания значит осталось уже 7шт по 6,16, но 15го числа поступило еще 10 по 7, теперь кол-во равно17 шт, а
ср_цена = ((7*6,16)+(10*7))/17....

[Stilet]

Т.е. тебе нужно списать непроданный товар что ли?

[ТипичныйСтудент]

мне нужно найти среднюю цену товара) товар поступает с разными ценами на склад, а списывается по одной...но если 5го он списался по одной, то 10го цена (средняя) может и поменяться...все зависит от стоимости новой поступившей партии....
вот как мне дали задачу, так я ее и написала(
я не пойму как сделать, чтобы сначала отобрать один товар со всей таблице, впихнуть значение формулы в столбец средняя цена, посчитав при этом остаток...мне бы с кодом подсказали....

[Stilet]

update tbl t1
set 
 t1.sredcena=(select avg(cena) from tbl t2 group by t2.obj where t2.obj=t1.obj and t1.pr=-1)
where t1.pr=-1

Это если я правильно понял твою упрямую задачу, которую решать-то нужно сооовсем по другому )

[ТипичныйСтудент]

а если не секрет, то как по другому? хотя бы в общих чертах...
и что такое t1??

[Stilet]

что такое t1?

Это алиас таблицы. У нас с двух разных запросах одна и та же таблица фигурирует, кузяво ее различать под разными именами чтоб запросы не путались.

как по другому?

Ни в коем случае никаких остатков не хранить. Это вычисляемая величина, а не статическая. Это сейчас, пока твоя БД состоит из 10-50 записей, ассортимент маленький и движения товара практически нет это допустимая форточка из положения, но когда пойдет движение, когда ассортимент товара будет много раз корректироваться все новыми и новыми условиями поставки-продажи, возможно придется делать кучу переоценок, вот тогда твои остаточки сядут минусом в сальдо. А это как правило приводит любого бухгалтера к троекратному "инхварту Микарда".
Остатки и средние величины нужно расчитывать исходя из периода реализации товара (ну или списания).

[ТипичныйСтудент]

Сообщение от Stilet

update tbl t1
set 
 t1.sredcena=(select avg(cena) from tbl t2 group by t2.obj where t2.obj=t1.obj and t1.pr=-1)
where t1.pr=-1

Это если я правильно понял твою упрямую задачу, которую решать-то нужно сооовсем по другому )

блин, я понимаю, что вопрос тупой, но как правильно прописать алиасы t1 и t2?? ибо ругается и просит их объявить