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:

Expected output:

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

CodePudding user response：

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

CodePudding user response：

Check if this will suit your needs, the way i know to group by 2 columns, and be precise with the second, is to group by the first one, and then CROSS APPLY to get the other one, CROSS APPLY is a more efficient alternative to put a sub-select in the SELECT PART. The PartNumberId i got using the location and the minimum date, in case there are more PartNumberId for the same location and minimum date, the lower one will be selected:

SELECT Location,
       MIN(RecordAddedDate) RecordAddedDate,
       MIN(TabPartNumber.PartNumberID) PartNumberID
FROM MyTable
CROSS APPLY (SELECT TOP 1 PartNumberID
            FROM MyTable MyTable_inner 
            WHERE MyTable_inner.Location = MyTable.Location
            AND MyTable_inner.RecordAddedDate = MyTable.RecordAddedDate
            ORDER BY PartNumberID) TabPartNumber
GROUP BY Location

If you find my answer useful, i would appreciate if you mark as accepted and vote up =D