CODEWITHSUNDEEP
sql-servert-sql
Floyd
Floyd

Reputation: 71

Best way to compare dates in multiple rows

Assume the following table...

CREATE TABLE Dummy_Data 
(
    ID INT, 
    TextField VARCHAR(20), 
    DateField DATE
)

INSERT INTO Dummy_Data (ID, TextField, DateField)
VALUES (1, 'Random Text', '2018-01-04'),
       (1, 'Random Text', '2018-02-04'),
       (1, 'Random Text', '2018-05-01'),
       (2, 'Random Text', '2017-01-14'),
       (2, 'Random Text', '2017-05-01'),
       (2, 'Random Text', '2018-01-01'),
       (2, 'Random Text', '2018-02-01'),
       (3, 'Random Text', '2018-01-04')

I would like to return the MAX(DateField) for each ID if the DATEDIFF is less than 90 days between each row.

If the DATEDIFF is greater than 90 days between each row I would like to return the MAX(DateField) for each grouping per say.

Example:

So taking rows 1 to 3 from the above we know that the DATEDIFF in days between each row is less than 90 so I would only want to return: 

ID   TextField      DateField
-------------------------------
1 -  Random Text    2018-05-01

However: rows 4 to 7 have an occurrence where the DATEDIFF is greater than 90 days so I would want to return:

ID   TextField     DateField
-------------------------------
2    Random Text   2017-01-14
2    Random Text   2017-05-01
2    Random Text   2018-02-01

Thank you to anyone that can resolve this predicament.

Upvotes: 3

Views: 951

Answers (1)

xQbert
xQbert

Reputation: 35323

RexTester DEMO (using express 2014) hopefully works in 2012..

Lead(), CTE, and DateDiff are supported, so I can't think why it wouldn't...

WITH CTE AS (
  SELECT ID
       , textField
       , DateField
       , case when datediff(dd,datefield, lead(datefield) over (partition by ID order by DateField ASC)) > 90 
                OR lead(datefield) over (partition by ID order by DateField ASC) is null then 1 
         else 0 end bInclude
  FROM  Dummy_Data)

SELECT ID, textFIeld, DateField, binclude
FROM CTE
WHERE bInclude = 1;

we use LEAD() to look ahead at the next datefield for an ID. If null or if > 90 days we mark that record with a 1; otherwise it's a 0 then we only include the 1's.

Giving us:

+----+----+-------------+---------------------+----------+
|    | ID |  textFIeld  |      DateField      | binclude |
+----+----+-------------+---------------------+----------+
|  1 |  1 | Random Text | 01.05.2018 00:00:00 |        1 |
|  2 |  2 | Random Text | 14.01.2017 00:00:00 |        1 |
|  3 |  2 | Random Text | 01.05.2017 00:00:00 |        1 |
|  4 |  2 | Random Text | 01.02.2018 00:00:00 |        1 |
|  5 |  3 | Random Text | 04.01.2018 00:00:00 |        1 |
+----+----+-------------+---------------------+----------+

Upvotes: 2

Related Questions