Suppose I have a first table like this:
tbl1:
item date1 date2
1 2020-06-21 2020-06-28
2 2020-05-13 2020-05-24
3 2020-06-20 2020-06-28
I also have a second table with the items, a date and a quantity
tbl2:
item quantity date
1 5 2020-06-24
1 8 2020-06-20
1 12 2020-06-27
1 9 2020-06-29
2 10 2020-05-24
2 11 2020-05-15
2 18 2020-05-18
2 9 2020-05-14
3 7 2020-06-18
3 12 2020-06-21
3 13 2020-06-24
3 8 2020-06-28
Now I want to sum the quantities from tbl2 where the date is between the columns date1 and date2. So my result would look like:
tbl1:
item date1 date2 sum
1 2020-06-21 2020-06-28 17
2 2020-05-13 2020-05-24 48
3 2020-06-20 2020-06-28 33
I've been starring at it for a while now and I really want to avoid a loop.
Is there an efficient way of obtaining the desired result??
You can just join them, group by table1 and sum:
select t1.*, sum(t2.quantity)
from table1 t1;
left join table2 t2 on t2.item = t1.item and
t2.date >= t1.date1 and
t2.date <= t1.date2
group by t1.item, t1.date1, t1.date2
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments