CODEWITHSUNDEEP
sqlsql-server
CandleWax
CandleWax

Reputation: 2219

How to find rows based on a condition within a group in sql?

I need to find all rows where admit dates between different episodes are within 90 days for the same patient. An episode is simply various hospital visits grouped together as long as they are related.

In the below example, patient_num 5743 has three episodes. Here I need to check admit_dates between this patient's episodes. If any of the admit dates in an episode are within 90 days of another episode then I want to return all the rows for those episodes.

dataset (table1):

episode_id   patient_num  admit_date
1            5743         2016-03-29
1            5743         2016-04-26
2            5743         2016-04-26
3            5743         2017-04-21
5            3242         2016-04-27
5            3242         2016-04-28
6            3242         2016-11-21
6            3242         2016-11-24

expected result:

episode_id   patient_num  admit_date
1            5743         2016-03-29
1            5743         2016-04-26
2            5743         2016-04-26

I attempted using CTE:

WITH a_cte
 AS (
 SELECT episode_id,
        patient_num,
        admit_date
 FROM table1)
 SELECT episode_id,
        patient_num,
        admit_date
 FROM table1
      LEFT JOIN a_cte ON a_cte.episode_id = b.episode_id
 WHERE a_cte.admit_date = b.admit_date
 ORDER BY b.patient_num,
          b.episode_id;

Final working code:

SELECT a.episode_id,
       a.patient_num,
       a.admit_date
FROM table1 AS a
WHERE EXISTS
(
    SELECT *
    FROM table1 AS b
    WHERE b.admit_date BETWEEN a.admit_date AND DATEADD(day, 89, a.admit_date)
          AND b.patient_num = a.patient_num
          AND a.episode_id = b.episode_id
)
ORDER BY a.patient_num,
         a.episode_id;

Upvotes: 0

Views: 54

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

If any of the admit dates in an episode are within 90 days of another episode then I want to return all the rows for those episodes.

Anytime you need something like this (if any then all), you should think of EXISTS(). In pseudocode:

SELECT ... 
WHERE EXISTS(SELECT ... WHERE Subquery.PK=OuterQuery.PK and Subquery.Condition=true)

This gets ALL rows from the table where ANY row that has the same PK meets the condition you want to test.

Upvotes: 1

Whippet
Whippet

Reputation: 1

It sounds like you need to use a window function like LAG to bring the date of the previous record onto the same row, the use datediff to get the days between them.

Then filter on the datediff(lag(admitdate) > 90

A link for lag and datediff functions:

https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017

Datediff

https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017

Hope this helps

Upvotes: 0

Related Questions