Home > Blockchain >  return row where column value changed from last change
return row where column value changed from last change

Time:07-13

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

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

  • Related