SQL query to generate summary file base on change in price per item

Question

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

Answer 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

Fiddle