On the Firebird 2.5 server in the database there are tables: goods, income, sale.

Goods: goods_id, name Income: Income_id, Goods_id, incomeqty (quantity), recdate Sale: sales_id,income_id,Goods_id,saleqty,recdate

There is a query that selects incomes, expenses, and balances (difference) in the time interval:

 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

But I want to add starting balances in it.
starting balances up to the time interval = sum (exits) to the time interval-sum (expenses) to the time interval.
The end balances at the end of the time interval = starting balances + exits in the interval-costs in the interval. starting balance in my opinion should get this:

 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

Where and how to include in the main request a request for getting starting balances?

UPDATE 1:

rewrote the request, but for some reason the final residues incorrectly receive (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 and the final query give the following result: picture

UPDATE 2

The reason was NULL. Everywhere you need to turn NULL into 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
  • Well, if it is in the forehead, then attach this request to the goods as well as the one that the interval collects, according to the left join - Mike

1 answer 1

In order to calculate the remainder in the request, you must remove the restriction on the first date of the analyzed period and add work with dates to the aggregation function using 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

I would also like to draw attention to the design

 sum(sum_income - sum_sale)

If sum_income or sum_sale is NULL, subtraction will return NULL. Because of this, you can get an error when summarizing. It will be more correct to write this line in the form

 sum(COALESCE(sum_income,0) - COALESCE(sum_sale,0)), sum(sum_income) - sum(sum_sale)
  • okdel , How was NULL and coalesce the reason. I searched all day and finally found it. Thank you too. And what do you mean in "it is necessary to remove the restriction on the first date of the analyzed period"? - Delphi159
  • sale.recdate between: d1 and: d2 replace with sale.recdate <=: d2. - Okdel