I need help writing a query to generate a summary file of quantity purchase per item, and per cost from a purchase history file. To run the query the ORDER BY would be ITEM_NO, PO_DATE, AND COST.
SAMPLE DATE - PURCHASE HISTORY
OUTPUT FILE - SUMMARY
Asked
Active
Viewed 41 times
1 Answers
1
We can group by item_no and cost and get all the info we need.
select item_no
,cost
,min(po_date) as start_date
,max(po_date) as end_date
,sum(qty) as qty
from (
select *
,count(chng) over(partition by item_no order by po_date) as grp
from (
select *
,case when lag(cost) over(partition by item_no order by po_date) <> cost then 1 end as chng
from t
) t
) t
group by item_no, cost, grp
order by item_no, start_date
| item_no | cost | start_date | end_date | qty |
|---|---|---|---|---|
| 12345 | 1.25 | 2021-01-02 00:00:00 | 2021-01-04 00:00:00 | 150 |
| 12345 | 2.00 | 2021-02-01 00:00:00 | 2021-02-03 00:00:00 | 60 |
| 78945 | 5.25 | 2021-06-10 00:00:00 | 2021-06-12 00:00:00 | 90 |
| 78945 | 4.50 | 2021-10-18 00:00:00 | 2021-10-19 00:00:00 | 150 |
DannySlor
- 4,574
- 1
- 3
- 11
-
Yes, but it would not give me the correct result if the file is not sorted by ITEM_NO and PO_DATE. – user3537446 Sep 27 '22 at 15:59
-
DannySlor - I've tried your script, and it did not work. Could I send you my sample data? – user3537446 Sep 27 '22 at 17:39
-
I've tried your script, but it did not work. Could I send you my sample data, only 131 records? – user3537446 Sep 27 '22 at 17:48
-
@user3537446 send me a fiddle with it. – DannySlor Sep 27 '22 at 18:38
-
I was able to get it to work by adding the item_no to the order by in the over section "count(chng) over(order by item_no, po_date) as grp" - thanks for pointing me in the right direction. – user3537446 Sep 27 '22 at 18:40
-
1@user3537446 typo, see updated answer – DannySlor Sep 27 '22 at 19:10