CODEWITHSUNDEEP
sqlsql-server
Anupriya Athalye
Anupriya Athalye

Reputation: 11

How to select a specific previous row which meet some condition in SQL Server

I have a table with PatientID, DiagnosisID and Date column. The scenario is that if the patient has come for same diagnosis multiple times in a month only the rows with a date difference greater than or equal to 3 should be counted as Visit Count for that particular month.

Example :

RowNumber PatientID DiagnosisID DiagnosisDate
1            P1         D1        29-12-2018
2            P1         D1        01-01-2019
3            P1         D1        05-01-2019
4            P1         D1        06-01-2019
5            P1         D1        08-01-2019
6            P1         D1        09-01-2019
7            P1         D1        13-01-2019
8            P1         D1        31-01-2019
9            P1         D1        01-02-2019
10           P1         D1        07-02-2019

  1. Visit count should be counted as 1 for Dec-2018

  2. DateDiff>=3 between 1st and 2nd row (29 dec and 1st Jan) hence 1st Jan is valid Visit.

  3. DateDiff>=3 between 2st and 3nd row (1st Jan and 5th Jan) hence 5th Jan is valid Visit.

  4. DateDiff<=3 between 3rd and 4th row (5th jan and 6th Jan) hence 6th Jan IS NOT A VALID Visit.

  5. DateDiff<=3 between 4th and 5th row (6th Jan and 8th Jan) hence 8th Jan should be compared with previous valid visit i.e. 5th Jan here the datediff>=3 so 8th Jan is the new valid visit and preceeding rows should be compared with 8th jan.

  6. DateDiff<=3 between 5th and 6th row (8th Jan and 9th Jan) hence 9th Jan IS NOT A VALID VISIT.

  7. DateDiff>=3 between 6th and 7th row (13th Jan and 9th Jan) hence 13th Jan is new Valid visit.

  8. DateDiff>=3 between 7th and 8th row (31st Jan and 13th Jan) hence 31st Jan is new Valid visit.

  9. DateDiff<=3 between 8th and 9th row (1st Feb and 31st Jan) hence 1st Feb IS NOT A VALID Visit.

  10. DateDiff>=3 between 9th and 10th row (7th Feb and 1st Feb) hence 7th Feb is a valid visit.

Final output :

Dec-2018 visit count = 1 (29th Dec)
Jan-2019 visit count = 5 (1, 5, 8, 13 & 31st Jan)
Feb-2019 visit count = 1 ( 7th Feb )

Basically the Datediff between current row and prev row should be greater than equal to 3, if this conditiona is false current row should be compared with a previous valid visit.

I have tried it implementing it with recursive CTE, but I am not able to find a terminating condition i.e. the comparison should stop at previous valid visit.

Upvotes: 1

Views: 256

Answers (1)

chrszcpl
chrszcpl

Reputation: 46

use LAG function to test DATEDIFF

WITH test_tab as
(
select RowNumber, PatientID, DiagnosisID, DiagnosisDate,
DATETIFF(day, lag(PlantFK,1,'19900101') OVER (PARTITION BY PatientID, DiagnosisID order by DiagnosisDate),DiagnosisDate) days
from table
)
Select * from test_tab ;

filtler >=3 

WITH test_tab as
    (
    select RowNumber, PatientID, DiagnosisID, DiagnosisDate,
    DATETIFF(day, lag(PlantFK,1,'19900101') OVER (PARTITION BY PatientID, DiagnosisID order by DiagnosisDate),DiagnosisDate) days
    from table
    )
    Select * from test_tab WHERE days>=3

and group by Year/month

WITH test_tab as
        (
        select RowNumber, PatientID, DiagnosisID, DiagnosisDate,
        DATETIFF(day, lag(PlantFK,1,'19900101') OVER (PARTITION BY PatientID, DiagnosisID order by DiagnosisDate),DiagnosisDate) days
        from table
        )
Select YEAR(DiagnosisDate) year,Month(DiagnosisDate) montch,Count(RowNumber)
     from test_tab 
    WHERE days>=3
    GROUP BY YEAR(DiagnosisDate) ,Month(DiagnosisDate)

Upvotes: 0

Related Questions