#sql #firebird
На Firebird 2.5 server в БД есть таблицы: goods, income, sale.
Goods: goods_id, name
Income: Income_id, Goods_id, incomeqty (quantity), recdate
Sale: sales_id,income_id,Goods_id,saleqty,recdate
Есть запрос,который выбирает приходы,расходы и остатки(разницу) во временном интервале:
SELECT g.name, g.goods_id,
coalesce(dt2.sum_income,0),
coalesce(dt2.sum_sales,0),
coalesce(dt2.differ_between,0)
FROM goods g
LEFT JOIN
(SELECT goods_id,
sum(sum_income) as sum_income,
sum(sum_sale) as sum_sale,
sum(sum_income - sum_sale) as differ_between
from (select goods_id, sum(incomeqty) as sum_income, 0 as sum_sale, 'income' as which
from income
where income.recdate between :d1 and :d2
group by goods_id
union all
select goods_id, 0, sum(salesqty), 'sale'
from sales
where sale.recdate between :d1 and :d2
group by goods_id) dt1
group by goods_id
) dt2 on g.goods_id = dt2.goods_id
Но я хочу в нём добавить стартовые остатки.
стартовые остатки до интервала времены =sum(прыходы) до интервала времены-sum(расходы)
до интервала времены.
Конечные остатки в конце интервала времены=стартовые остатки+прыходы в интервале-расходы
в интервале.
стартовые остатки по-моему должен получить так:
select income.goods_id, sum(incomeqty) as start_income, 0 as start_sale, 'income'
as which
from income
where income.recdate < :d1
group by goods_id
union all
select goods_id,0, sum(saleqty) as start_sale, 'sale'
from sale
where sale.recdate < :d1
group by goods_id
group by goods_id
Где и как включить в главном запросе запрос получения стартовых остатков?
UPDATE 1:
переделал запрос,но почему-то конечные остатки неправильно получаю(endqnt): 5000+1000-20-5=6000?
SELECT
G.Name,
G.Doods_id,
coalesce(dt2.startqnt,0) as startqnt,
coalesce(dt2.income,0)as income,
coalesce(dt2.sales,0)as sales,
coalesce(dt2.writeoff,0)as writeoff,
coalesce(dt2.endqnt,0) as endqnt
FROM Goods G
LEFT JOIN
(select --dt2
Pr_k,
sum(startincome) as startincome,
--приходы до
sum(startsale) as startsale,
--продажи до
sum(startwriteoff) as startwriteoff,
--списывание до
sum(income) as income,
--приходы в интервале
sum(sale) as sales,
--продажи в интервале
sum(writeoff) as writeoff,
--списывание в интервале
sum(startincome-startsale-startwriteoff) as startqnt,
--остатки стартовые
sum(startincome-startsale-startwriteoff+income-sale-writeoff) as endqnt --конечные
остатки
from(
select
i.goods_id,
sum(case when i.recdate<'01/01/2010' then i.incomeqty end) as startincome,
0,
0, --списывание товара
sum(case when i.recdate between '01/01/2010' and '01/01/2020' then i.incomeqty
end) as income,
0,
0,
'income' as which
from income i
where i.recdate <='01/01/2020'
group by goods_id
union all
select
s.goods_id,
0,
sum(case when s.recdate<'01/01/2010' then s.saleqty end) as startsale,
0,
0,
sum(case when s.recdate between '01/01/2010' and '01/01/2020' then
s.saleqty end) as sale,
0,
'sale'
from sale s
where s.recdate <= '01/01/2020'
group by goods_id
union all
select
w. goods_id,
0,
0,
sum(case when w.writeoffDate<'01/01/2010' then w.writeoffQty end) as
startwriteoff,
0,
0,
sum(case when w.writeoffDate between '01/01/2010' and '01/01/2020' then
w.writeoffQty end) as writeoff,
'writeoff'
from writeoff w
where w.writeofDate < '01/01/2020'
group by goods_id) dt1
group by Goods_id )dt2
on G.Goods_id=dt2.Goods_id
Derived Table DT1 и конечный запрос дают такой результат:
UPDATE 2
Причина была в NULL. Везде надо преврашать NULL в 0:
SELECT
G.Name,
G.Doods_id,
coalesce(dt2.startqnt,0) as startqnt,
coalesce(dt2.income,0)as income,
coalesce(dt2.sales,0)as sales,
coalesce(dt2.writeoff,0)as writeoff,
coalesce(dt2.endqnt,0) as endqnt
FROM Goods G
LEFT JOIN
(select
--dt2
Pr_k,
sum(startincome) as startincome,
--приходы до
sum(startsale) as startsale,
--продажи до
sum(startwriteoff) as startwriteoff,
--списывание до
sum(income) as income,
--приходы в интервале
sum(sale) as sales,
--продажи в интервале
sum(writeoff) as writeoff,
--списывание в интервале
sum(coalesce(startincome,0)-coalesce(startsale,0)-coalesce(startwriteoff,0)
as startqnt, --остатки стартовые
sum(coalesce(startincome,0)-coalesce(startsale,0)-coalesce(startwriteoff,0)+coalesce(income,0)-coalesce(sale,0)-coalesce(writeoff,0))
as endqnt --конечные остатки
from(
select
i.goods_id,
sum(case when i.recdate<'01/01/2010' then coalesce(i.incomeqty,0) end) as startincome,
0,
0, --списывание товара
sum(case when i.recdate between '01/01/2010' and '01/01/2020' then coalesce(i.incomeqty,0)
end) as income,
0,
0,
'income' as which
from income i
where i.recdate <='01/01/2020'
group by goods_id
union all
select
s.goods_id,
0,
sum(case when s.recdate<'01/01/2010' then coalesce(s.saleqty,0) end) as startsale,
0,
0,
sum(case when s.recdate between '01/01/2010' and '01/01/2020' then
coalesce(s.saleqty,0) end) as sale,
0,
'sale'
from sale s
where s.recdate <= '01/01/2020'
group by goods_id
union all
select
w. goods_id,
0,
0,
sum(case when w.writeoffDate<'01/01/2010' then coalesce(w.writeoffQty,0) end) as
startwriteoff,
0,
0,
sum(case when w.writeoffDate between '01/01/2010' and '01/01/2020' then
coalesce(w.writeoffQty,0) end) as writeoff,
'writeoff'
from writeoff w
where w.writeofDate < '01/01/2020'
group by goods_id) dt1
group by Goods_id )dt2
on G.Goods_id=dt2.Goods_id
Ответы
Ответ 1
Для того что бы посчитать остаток в запросе необходимо убрать ограничение на первую дату анализируемого периода и добавить работы с датами в агрегирующую функцию с помощью CASE SELECT g.name, g.goods_id, coalesce(dt2.balance_before,0), --остаток на начало coalesce(dt2.sum_income,0), --приход в периоде coalesce(dt2.sum_sales,0), --рассход в периоде coalesce(dt2.differ_between,0), --разница за период coalesce(dt2.balance_after,0) --остаток на конец FROM goods g LEFT JOIN (SELECT goods_id, sum(case when sale.recdate < :d1 then sum_income - sum_sale end) balance_before, sum(case when sale.recdate between :d1 and :d2 then sum_income end) as sum_income, sum(case when sale.recdate between :d1 and :d2 then sum_sale end) as sum_sale, sum(case when sale.recdate between :d1 and :d2 then sum_income - sum_sale end) as differ_between sum(sum_income - sum_sale) as balance_after from (select goods_id, sum(incomeqty) as sum_income, 0 as sum_sale, 'income' as which from income where income.recdate <= :d2 group by goods_id union all select goods_id, 0, sum(salesqty), 'sale' from sales where sale.recdate <= :d2 group by goods_id) dt1 group by goods_id ) dt2 on g.goods_id = dt2.goods_id Так же хотелось бы обратить внимание на конструкцию sum(sum_income - sum_sale) В случае если sum_income или sum_sale равно NULL вычитание вернет NULL. Из-за этого можно получить ошибку при подведении итогов. Корректнее будет записать эту строчку в виде sum(COALESCE(sum_income,0) - COALESCE(sum_sale,0)), sum(sum_income) - sum(sum_sale)
Комментариев нет:
Отправить комментарий