CODEWITHSUNDEEP
sql-servert-sql
niclas_4
niclas_4

Reputation: 3674

Output only Unique Rows for given Timerange

So I wanna have all CHARGE where the HPDATUM to the next is more than 1 year ago.

Current query looks like this:

SELECT DISTINCT CHARGE, HPDATUM FROM dbo.table123 WHERE CHARGE = '8811985' GROUP BY CHARGE, HPDATUM

This is an example query with a where restriction to the CHARGE which will be gone in the final query to get all CHARGE.

This is an example output of the query above:

CHARGE  HPDATUM

8811985  1999-10-08  | NEW ENTRY
8811985  2003-06-25  | NEW ENTRY
8811985  2003-06-26  | DUPLICATE TO THE ONE ABOVE
8811985  2004-09-04  | NEW ENTRY
8811985  2004-09-05  | DUPLICATE TO THE ONE ABOVE
8811985  2005-01-09  | DUPLICATE TOO

Upvotes: 0

Views: 43

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522141

We can try using LEAD/LAG here to compare the current HPDATUM date against the ones that come before/next in the sequence, for each charge group.

WITH cte AS (
    SELECT CHARGE, HPDATUM,
        LAG(HPDATUM, 1, DATEADD(year, -1, HPDATUM)) OVER (PARTITION BY CHARGE ORDER BY HPDATUM) HPDATUM_PREV,
        LEAD(HPDATUM, 1, DATEADD(year, 1, HPDATUM)) OVER (PARTITION BY CHARGE ORDER BY HPDATUM) HPDATUM_NEXT
    FROM dbo.table123
)

SELECT CHARGE, HPDATUM
FROM cte
WHERE
    DATEDIFF(year, HPDATUM, HPDATUM_PREV) >= 1 AND
    DATEDIFF(year, HPDATUM, HPDATUM_NEXT) >= 1;

If you only want to check in one direction, then remove one of the two lead/lag checks. Also, if you only want to see a single charge, then add a WHERE clause to the CTE.

Upvotes: 4

Squirrel
Squirrel

Reputation: 24793

use window function LAG () to get previous row value of HPDATUM and compare

CTE AS
(
    SELECT  t.CHARGE, t.HPDATUM,
            PREV_HPDATUM = LAG (t.HPDATUM) OVER (ORDER BY HPDATUM)
    FROM    dbo.table123 t
    WHERE   t.CHARGE = '8811985'
)
SELECT  CHARGE, HPDATUM
FROM    CTE
WHERE   PREV_HPDATUM    IS NULL
OR      HPDATUM     > DATEADD(YEAR, 1, PREV_HPDATUM)

Upvotes: 0

Related Questions