Не могу создать запрос для посчитания остатков склада (объединять 2 запроса)

[delphi159]

На 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,0, 'sale'
            from sale
            where sale.recdate < :d1
            group by goods_id
         group by goods_id

Где и как включить в главном запросе запрос получения стартовых остатков?

[Аватар]

В одном запросе сумму до и сумму после можно так получить

SELECT goods_id,
    SUM(CASE WHEN recdate<:d1 THEN sum_income ELSE 0 END) AS sum_saleIN,
    SUM(CASE WHEN recdate>=:d1 THEN sum_income ELSE 0 END) AS sum_sale
  FROM income
  WHERE recdate<=:d2
  GROUP BY goods_id

Аналогично для расхода. Был бы приход и расход в одной таблице все стало бы гораздо проще

PS

В огнептице case может чуть по другому, не помню

[delphi159]

Аватар, переделал запрос,но почему-то конечные остатки неправильно получаю(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 as startsale,
           0 as startwriteoff,                         --списывание товара
           sum(case when i.recdate between '01/01/2010'  and '01/01/2020' then i.incomeqty end) as income,
           0 as sale,
           0 as writeoff,
           '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 и конечный запрос дают такой результат:

[Аватар]

А где dt2.Goods_id ?

[delphi159]

Сообщение от Аватар

А где dt2.Goods_id ?

Если вы о рисунке, тогда где "ID" написано (в БД оны по-другому называются).

[Аватар]

Я о запросе. В подзапросе с псевдонимом dt2 нет поля Goods_id. Как тогда быть с on G.Goods_id=dt2.Goods_id ?

[delphi159]

Сообщение от Аватар

Я о запросе. В подзапросе с псевдонимом dt2 нет поля Goods_id. Как тогда быть с on G.Goods_id=dt2.Goods_id ?

Извините, где pr_K написано это и есть Goods_id.

[Аватар]

     sum(startincome-startsale-startwriteoff) as startqnt,                                --остатки стартовые 
     sum(startincome-startsale-startwriteoff+income-sale-writeoff) as endqnt    --конечные остатки

Если одно из слагаемых NULL, как думаешь что будет в результате? Просуммируй отдельно, а во внешем запросе сложи с учетом возможного NULL для каждого слагаемого, используя coalesce, или лучше isnull если такой есть в огнептице

[delphi159]

К сожалению и coalesce() не помогло:

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
     Goods_id,
     coalesce(sum(startincome),0) as startincome,                 
     coalesce(sum(startsale),0) as startsale,                 
     coalesce(sum(startwriteoff),0) as startwriteoff,    
     coalesce(sum(income),0) as income,              
     coalesce(sum(sale),0) as  sales,                
     coalesce(sum(writeoff),0) as writeoff,                                    
     coalesce(sum(startincome-startsale-startwriteoff),0) as startqnt,                                 
     coalesce(sum(startincome-startsale-startwriteoff+income-sale-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

[Аватар]

sum(startincome-startsale-startwriteoff

Так нельзя. Если, например, startsale NULL, то и startincome-startsale-startwriteoff будет null. Не важно, что в других полях