CODEWITHSUNDEEP
sqlsql-serversql-server-2016
Emel Uras
Emel Uras

Reputation: 404

How to get min and max dates for an item for each time its price changes in SQL Server

I have a table like this:

wkdate     itemname price sold
-------------------------------
2016-07-01 X        3.00    8  
2016-07-07 X        3.00    4
2016-07-14 X        3.00   10
2016-07-21 X        9.00    3
2016-07-28 X        9.00    2
2016-08-05 X        3.00    4
2016-08-12 X        3.00   12

I want to get a result table showing min and max date and TotalSold between those dates based on for each itemname and price. I can't just group by based on itemname and price though.

Expected output would be like this:

min(wkdate) max(wkdate) itemname price sold
-------------------------------------------
2016-07-01  2016-07-14   X       3.00  22
2016-07-21  2016-07-28   X       9.00   5
2016-08-25  2016-08-12   X       3.00  16

How can I do it?

Upvotes: 0

Views: 532

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270473

This is a type of gaps-and-islands problem. You can use the difference of row numbers for this:

select item, price, min(wkdate), max(wkdate),
       sum(sold)
from (select t.*,
             row_number() over (partition by item order by wkdate) as seqnum,
             row_number() over (partition by item, price order by wkdate) as seqnum_p
      from t
     ) t
group by item, price, (seqnum - seqnum_p)
order by item, min(date);

It is a little tricky to explain how this works. Well, not that hard. But usually if you just stare at the results of the subquery, you'll see how the difference in sequence numbers define the groups that you are looking for.

Upvotes: 2

Related Questions