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
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