Compare the records from the recent Monday to the immediate last Monday and flag as old or new

Transact-SQL
1

Hi All,

I have a dataset that has the the different schedule codes by date at a PO number level..I am trying to identify the records from the most recent monday and compare it to the immediate last monday and then flag the combination of (POnumber /schedule_code) to be old (meaning this combination existed the last monday otherwise I have to flag it as new.. I have to consider only the most recent monday and the immediate last monday for consideration..can you please help me here..

Input table

create table ##input
(snapshotdate date,
ponumber varchar(100),
schedulecode int)

insert into ##input values
('7/19/2022','FD17891','3'),
('7/19/2022','KL89123','15'),
('7/19/2022','YU12643','5'),
('7/18/2022','D78123','10'),
('7/18/2022','C12678','5'),
('7/18/2022','J12341','2'),
('7/18/2022','B12314','5'),
('7/18/2022','JK1234','2'),
('7/17/2022','K123121','5'),
('7/17/2022','V12312','4'),
('7/17/2022','X12890','3'),
('7/16/2022','S7832','5'),
('7/16/2022','F12345','5'),
('7/11/2022','D78123','10'),
('7/11/2022','XC12341','2'),
('7/11/2022','BH1231','4'),
('7/11/2022','J12341','1'),
('7/11/2022','C12678','5'),
('7/9/2022','U12341','3'),
('7/9/2022','JK12891','5'),
('7/9/2022','N1290','10'),
('7/8/2022','I18967','5'),
('7/8/2022','HJ1230','2')

output table

create table ##output
(snapshotdate date,
ponumber varchar(100),
schedulecode int,
neworold text)

insert into ##output values
('7/18/2022','D78123','10','Old'),
('7/18/2022','C12678','5','Old'),
('7/18/2022','J12341','2','New'),
('7/18/2022','B12314','5','New'),
('7/18/2022','JK1234','2','New')

In the above case 07/18 is the most recent monday and 07/11 is the immediate last monday for comparison.

2 

SELECT 
    ponumber,
    most_recent_Monday,
    MAX(CASE WHEN snapshotdate = most_recent_Monday THEN schedulecode END) AS schedulecode,
    MAX(CASE WHEN snapshotdate = previous_Monday THEN 'Old' ELSE 'New' END) AS oldornew
FROM ##input
CROSS APPLY (
    SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()) % 7, CAST(GETDATE() AS date)) AS most_recent_Monday,
        DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()) % 7 - 7, CAST(GETDATE() AS date)) AS previous_Monday
) AS Mondays
WHERE snapshotdate IN (previous_Monday, most_recent_Monday)
GROUP BY ponumber, most_recent_Monday
HAVING MAX(CASE WHEN snapshotdate = most_recent_Monday THEN 1 ELSE 0 END) = 1
ORDER BY ponumber
3

Hi @ScottPletcher : Thank you so much for your response!...Just wanted to understand if the above query is using a combination of Ponumber and schedulecode for comparison..

4

Oops, no, just ponumber. I accidentally left out the schedulecode.