Fiona Baker Fiona Baker - 3 months ago 6
SQL Question

When IDs are identical check that the Ordinal is greater than the previous submission

Example query

USE HES
SELECT T1.ID, T2.DATE, T1.ORDINAL
FROM TABLE1 AS T1
LEFT JOIN TABLE2 AS T2
ON T1.ID = T2.ID AND T1.PARTYEAR = T2.PARTYEAR
WHERE
T1.MONTHYEAR = '201501'


Results from example query

ID Date Ordinal

1 01/01/2016 1
1 02/01/2016 2
1 03/01/2016 3
2 04/01/2016 1
2 05/01/2016 2
3 06/01/2016 1
3 07/01/2016 2
3 08/01/2016 3
4 09/01/2016 1
4 10/01/2016 1


Question

Each user has a unique ID, for each ID how would I to check that each data submission contains an Ordinal that is greater than the one that was previously submitted.

So, in the example query results above, ID 4 contains an issue.

I'm fairly new to SQL, I've been searching for similar examples but with no success.

Any help would be greatly appreciated.

Answer

Use LAG with OVER clause:

WITH cte AS
(
    SELECT T1.ID, T2.DATE, T1.ORDINAL, LAG(T1.ORDINAL) OVER(PARTITION BY T1.ID ORDER BY T1.ORDINAL) AS LagOrdinal
    FROM TABLE1 AS T1   
    LEFT JOIN TABLE2 AS T2  
        ON T1.ID = T2.ID AND T1.PARTYEAR = T2.PARTYEAR  
    WHERE
        T1.MONTHYEAR = '201501'
)
SELECT ID, DATE, ORDINAL, CASE WHEN ORDINAL > LagOrdinal THEN 1 ELSE 0 END AS OrdinalIsGreater
    FROM cte;
Comments