return row where column value changed from last change

user_7927813 0
Viewed 106

I have a table and i want to know the minimum date since the last change grouped by 2 columns

in the data, I want to know the lates PartNumberID by location, with the min date since the last change.

*Expected row it's not part of the table

DATA:

Location RecordAddedDate PartNumberID ExpectedRow
7 2022-06-23 1 I want this row
8 2022-06-23 1 I want this row
8 2022-06-24 1
8 2022-06-25 1
9 2022-06-23 1 I want this row
15 2022-06-23 1
15 2022-06-24 1
15 2022-06-25 2
15 2022-06-26 1 I want this row
15 2022-06-27 1

Expected output:

Location RecordAddedDate PartNumberID
7 2022-06-23 1
8 2022-06-23 1
9 2022-06-23 1
15 2022-06-26 1

I'm on sql

I have tried with but I dont know how to stop when the value change

with cte as (
  select t.LocationID, t.RecordAddedDate, t.PartNumberID
FROM mytable t 
    INNER JOIN (select PL.LocationID, PL.RecordAddedDate, PL.PartNumberID
FROM            mytable  PL INNER JOIN
                             (SELECT        PSCc.LocationID, MAX(PSCc.RecordAddedDate) AS DateSetup
                               FROM            mytable  PSCc
                               WHERE        PSCc.RecordDeleted = 0
                               GROUP BY PSCc.LocationID) AS PSCc ON PSCc.LocationID = PL.LocationID AND PSCc.DateSetup = RecordAddedDate) as tt on t.RecordAddedDate<=tt.RecordAddedDate and t.LocationID= tt.LocationID and t.PartNumberID= tt.PartNumberID
)
select * 
from cte c
where not exists(
select 1 from cte
where cte.LocationID = c.LocationID 
and cte.PartNumberID=c.PartNumberID
and cte.RecordAddedDate<c.RecordAddedDate
)
order by LocationID,RecordAddedDate

Thank you

sql-server-2012 sql
1 Answers

use lag() to find the last change (order by RecordAddedDate desc) in PartNumberID.

cumulative sum sum(isChange) to group the related rows under same group no. grp = 0 with be the rows of the last change

To get the min - RecordAddedDate, use row_number()

with 
cte1 as
(
    select *,
           isChange = case when PartNumberID
                           =    isnull(lag(PartNumberID) over (partition by Location 
                                                                   order by RecordAddedDate desc), 
                                       PartNumberID)
                           then 0
                           else 1
                           end
    from   mytable
),
cte2 as
(
    select *, grp = sum(isChange) over (partition by Location order by RecordAddedDate desc)
    from   cte1
),
cte3 as
(
    select *, rn = row_number() over (partition by Location order by RecordAddedDate)
    from   cte2 t
    where  t.grp = 0
)
select *
from   cte3 t
where  t.rn = 1

db<>fiddle demo

user_1856470
Related