CODEWITHSUNDEEP
sqlsql-serverrow-number
Mandroid
Mandroid

Reputation: 25

Get Min value of each group in a table based on another col

I have table (Cost for a Product by location and month/date) in the format below and want to get Min of ChangeDate for whenever Cost has changed.

Cost            Loc     Prod        ChangeDate
--------------------------------------------------------
1.223000000000  5678    12345678    2010-01-03 00:00:00
1.223000000000  5678    12345678    2010-01-31 00:00:00
1.223000000000  5678    12345678    2010-02-28 00:00:00
1.000000000000  5678    12345678    2010-04-04 00:00:00
1.223000000000  5678    12345678    2010-05-02 00:00:00
1.223000000000  5678    12345678    2010-05-30 00:00:00
1.223000000000  5678    12345678    2010-07-04 00:00:00
1.277200000000  5678    12345678    2010-08-01 00:00:00
1.277200000000  5678    12345678    2010-08-29 00:00:00
1.277200000000  5678    12345678    2010-10-03 00:00:00

Expected output is:

Cost           Loc  Prod     CostChangeStartDate
------------------------------------------------
1.223000000000 5678 12345678 2010-01-03 00:00:00
1.000000000000 5678 12345678 2010-04-04 00:00:00
1.223000000000 5678 12345678 2010-05-02 00:00:00
1.277200000000 5678 12345678 2010-08-01 00:00:00

I tried using Row_Number() Over(PartitionBy OrderBy) but the problem is since Cost 1.223000000000 is repeated twice, I am unable to partition it properly so getting only 3 records.

Upvotes: 2

Views: 55

Answers (1)

Blorgbeard
Blorgbeard

Reputation: 103467

You can get rows that have a different cost to their previous row using LAG:

select * from (
    select *, lag(cost, 1) over (partition by Loc, Prod order by ChangeDate) prevCost
    from @products
) x
where prevCost is null or cost <> prevCost

Upvotes: 3

Related Questions