1

i'm trying to do LEFT JOIN with SUM 2 of 4 tables, and i'm stuck

In the expected result in the bottom below, there is new field called begin_stock and end_stockresult of daily_stock.qty which is filtered by dates_stat but does not need to use sum() because the values is final.

You can try in in here: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=07defd60df696a8717fc8e3096592545

Heres the tables:

product:

id  product_name 
1   abc            
2   aaa            
3   bbb             
4   ddd

usage: need to use sum()


id  product_id  used    date_out
1   1           10      2020-12-18
2   1           20      2020-12-19
3   2           20      2020-12-18

product_restock: need to use sum()

id  product_id  restock_amount  date_in
1   1           10              2020-12-18
2   1           20              2020-12-19
3   4           10              2020-12-19

daily_stock

id  product_id  qty dates_stat
1   1           10  2020-12-18
2   2           10  2020-12-18
3   3           10  2020-12-18
4   4           10  2020-12-18
5   1           10  2020-12-19
6   2           -10 2020-12-19
7   3           10  2020-12-19
8   4           20  2020-12-19

Expected result from date 2020-12-18 to 2020-12-19:

id      product_name  begin_stock   restock used end_stock
------  ------------  ------------- ------- ---- -------------
  1     abc           10            30      30   10
  2     aaa           10            0       20   -10 
  3     bbb           10            0       0    10    
  4     ddd           10            10      0    20
GMB
  • 216,147
  • 25
  • 84
  • 135
Diand
  • 878
  • 7
  • 12

2 Answers2

1

You can use aggregation and lateral joins:

select p.*, ds.*, pr.*, u.*
from product p
cross join lateral (
    select 
        max(case when ds.date_stat = date '2020-12-18' then qty end) as begin_stock,
        max(case when ds.date_stat = date '2020-12-19' then qty end) as end_stock
    from daily_stock ds
    where 
        ds.product_id = p.id
        and ds.date_stat in (date '2020-12-18', date '2020-12-19')
) ds
cross join lateral (
    select coalesce(sum(u.used), 0) as used
    from usage u
    where 
        u.product_id = p.id 
        and u.date_out >= date '2020-12-18'
        and u.date_out <= date '2020-12-19'
) u
cross join lateral (
    select coalesce(sum(pr.restock_amount), 0) as restock
    from product_restock pr
    where 
        pr.product_id = p.id 
        and pr.date_in >= date '2020-12-18'
        and pr.date_in <= date '2020-12-19'     
) pr

Demo on DB Fiddle:

 id | product_name | begin_stock | end_stock | restock | used
-: | :----------- | ----------: | --------: | ------: | ---:
 1 | abc          |          10 |        10 |      30 |   30
 2 | aaa          |          10 |       -10 |       0 |   20
 3 | bbb          |          10 |        10 |       0 |    0
 4 | ddd          |          10 |        20 |      10 |    0
GMB
  • 216,147
  • 25
  • 84
  • 135
  • hello, thanks for the answer, but still found an error: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=da4ac4f3424f3eb06d12e678ca8ca5c8 – Diand Dec 18 '20 at 23:51
  • @Diand: I fixed the query - and tested id. – GMB Dec 18 '20 at 23:53
  • hmm, is there a way to put the ```end_stock``` at the end of the table instead? it seem that ```begin_stock & end_stock``` cannot be separated.. – Diand Dec 19 '20 at 00:03
  • @Diand: that's just a small change to the outer `select` clause: `select p.*, ds.begin_stock, pr.*, u.*, ds.end_stock from ...` – GMB Dec 19 '20 at 00:04
  • ah thank you so much!, fixed! anyway, do you think is there any way to optimise the query? i mean to make it simpler? – Diand Dec 19 '20 at 00:11
  • @Diand: I don't think the query can easily be shortened. You have multiple matches in each table, which does require pre-aggregation. – GMB Dec 19 '20 at 00:12
  • 1
    well, thanks anyway man! have a nice day! – Diand Dec 19 '20 at 00:15
  • one more thing, what if all the ```datefield``` is actually ```datetimefield```? how to do a filter with date only? like "2020-12-12" im getting null with given formula.. – Diand Dec 19 '20 at 00:29
0
    with start_end as
(
    select ds.product_id,ds.date_stat,ds.qty
    ,row_number() over(partition by ds.product_id order by ds.date_stat asc) start_dt
    ,row_number() over(partition by ds.product_id order by ds.date_stat desc) end_dt
    from daily_stock ds
)
,sum_of_restock_amount as
(
    select 
    pr.product_id ,sum(pr.restock_amount) restock
    from product_restock pr
    group by pr.product_id  
)
,sum_of_usage as
(
    select 
    u.product_id ,sum(u.used) used
    from usage u
    group by u.product_id
)
select pn.*,st.qty,res.restock,us.used,en.qty
from product  pn
left join start_end st
on pn.id = st.product_id
    and st.start_dt = 1
    and st.date_stat between '2020-12-18' and '2020-12-19'
left join sum_of_restock_amount res
on pn.id = res.product_id
left join sum_of_usage us
on pn.id = us.product_id
left join start_end en
on pn.id = en.product_id
    and en.end_dt = 1
    and en.date_stat between '2020-12-18' and '2020-12-19'
funicany
  • 12
  • 1