March 19, 2019 at 1:00 pm
Good Day Folks!
I need a listing of members who had a hospital visit.
Please omit any visits that are within 30 days of the previous visit for the same member
In other words the visit for member [a] on 20180103 should not get listed
Give it a try....:)
IF OBJECT_ID('tempdb..#Hospital') IS NOT NULL DROP TABLE #Hospital;
Create table #Hospital ( mem VARCHAR(100) , vd VARCHAR(8) );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180101' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'b', '20180501' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180103' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'c', '20180501' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180201' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180501' );
/*
I need a listing of members who had a hospital visit.
Please omit any visits that are within 30 days of the previous visit for the same member
In other words the visit for member [a] on 20180103 should not get listed
*/
March 19, 2019 at 1:14 pm
I think you'd have to use LAG() to look at the previous visit for a given patient. Then use DATEDIFF() to get the days between the two dates, and if within 30 days, remove from the result set.
March 19, 2019 at 2:07 pm
Done!
IF OBJECT_ID('tempdb..#Hospital') IS NOT NULL DROP TABLE #Hospital;
Create table #Hospital ( mem VARCHAR(100) , vd VARCHAR(8) );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180101' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'b', '20180501' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180103' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'c', '20180501' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180901' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180501' );
/*
I need a listing of members who had a hospital visit.
Please omit any visits that are within 30 days of the previous visit for the same member
In other words the visit for member [a] on 20180103 should not get listed
*/
;
With a as
(
SELECT *,
LAG(vd) OVER(PARTITION BY mem ORDER BY vd ASC) AS Prev
FROM #Hospital
)
,
b as
(
Select * , DATEDIFF(D, prev, vd ) as DIFF
from
a
)
,
c as
(
Select
mem, vd
FROM
b
WHERE
ISNULL(DIFF,0 ) = 0
OR
ISNULL(DIFF,0 ) > 30
)
Select *
from c
March 19, 2019 at 2:16 pm
Yeah, like that!
March 19, 2019 at 3:40 pm
mw_sql_developer - Tuesday, March 19, 2019 2:07 PMDone!
IF OBJECT_ID('tempdb..#Hospital') IS NOT NULL DROP TABLE #Hospital;
Create table #Hospital ( mem VARCHAR(100) , vd VARCHAR(8) );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180101' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'b', '20180501' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180103' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'c', '20180501' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180901' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180501' );/*
I need a listing of members who had a hospital visit.
Please omit any visits that are within 30 days of the previous visit for the same memberIn other words the visit for member [a] on 20180103 should not get listed
*/;
With a as
(
SELECT *,
LAG(vd) OVER(PARTITION BY mem ORDER BY vd ASC) AS Prev
FROM #Hospital
)
,
b as
(
Select * , DATEDIFF(D, prev, vd ) as DIFF
from
a
)
,
c as
(
Select
mem, vd
FROM
b
WHERE
ISNULL(DIFF,0 ) = 0
OR
ISNULL(DIFF,0 ) > 30)
Select *
from c
You can simplify this by specifying the optional arguments for LAG(). You also don't need to have that many CTEs.
With a as
(
SELECT *,
DATEDIFF(DAY, LAG(vd, 1, '20000101') OVER(PARTITION BY mem ORDER BY vd ASC), vd) AS DIFF
FROM #Hospital
)
Select mem, vd
from a
WHERE DIFF > 30
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 19, 2019 at 5:21 pm
This will prevent all visits that have a visit within 30 days of the previous, but it will omit blocks of close visits that might have more than 30 days between the first and last.
SELECT *
FROM #Hospital h1
WHERE NOT EXISTS(SELECT *
FROM #Hospital h2
WHERE h2.mem = h1.mem
AND CONVERT(date,h2.vd) >= DATEADD(dd,-30,CONVERT(date,h1.vd))
AND CONVERT(date,h2.vd) < CONVERT(date,h1.vd))
This statement only leaves out a, 20180103;WITH CTE AS (
SELECT *
FROM #Hospital h1
WHERE NOT EXISTS(SELECT *
FROM #Hospital h2
WHERE h2.mem = h1.mem
AND CONVERT(date,h2.vd) >= DATEADD(dd,-30,CONVERT(date,h1.vd))
AND CONVERT(date,h2.vd) < CONVERT(date,h1.vd))
),
CTE2 AS
(
SELECT *
FROM #Hospital h1
WHERE NOT EXISTS(SELECT *
FROM CTE h2
WHERE h2.mem = h1.mem
AND CONVERT(date,h2.vd) >= DATEADD(dd,-30,CONVERT(date,h1.vd))
AND CONVERT(date,h2.vd) < CONVERT(date,h1.vd))
)
SELECT *
FROM CTE2
March 19, 2019 at 5:24 pm
drew.allen - Tuesday, March 19, 2019 3:40 PMmw_sql_developer - Tuesday, March 19, 2019 2:07 PMDone!
IF OBJECT_ID('tempdb..#Hospital') IS NOT NULL DROP TABLE #Hospital;
Create table #Hospital ( mem VARCHAR(100) , vd VARCHAR(8) );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180101' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'b', '20180501' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180103' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'c', '20180501' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180901' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180501' );/*
I need a listing of members who had a hospital visit.
Please omit any visits that are within 30 days of the previous visit for the same memberIn other words the visit for member [a] on 20180103 should not get listed
*/;
With a as
(
SELECT *,
LAG(vd) OVER(PARTITION BY mem ORDER BY vd ASC) AS Prev
FROM #Hospital
)
,
b as
(
Select * , DATEDIFF(D, prev, vd ) as DIFF
from
a
)
,
c as
(
Select
mem, vd
FROM
b
WHERE
ISNULL(DIFF,0 ) = 0
OR
ISNULL(DIFF,0 ) > 30)
Select *
from cYou can simplify this by specifying the optional arguments for LAG(). You also don't need to have that many CTEs.
With a as
(
SELECT *,
DATEDIFF(DAY, LAG(vd, 1, '20000101') OVER(PARTITION BY mem ORDER BY vd ASC), vd) AS DIFF
FROM #Hospital
)
Select mem, vd
from a
WHERE DIFF > 30Drew
That query doesn't include 'a', '20180201', I'm not sure from the question if it should but it is more than 30 days away from the last visit your query displays.
March 20, 2019 at 12:46 pm
Jonathan AC Roberts - Tuesday, March 19, 2019 5:24 PMdrew.allen - Tuesday, March 19, 2019 3:40 PMmw_sql_developer - Tuesday, March 19, 2019 2:07 PMDone!
IF OBJECT_ID('tempdb..#Hospital') IS NOT NULL DROP TABLE #Hospital;
Create table #Hospital ( mem VARCHAR(100) , vd VARCHAR(8) );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180101' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'b', '20180501' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180103' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'c', '20180501' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180901' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180501' );/*
I need a listing of members who had a hospital visit.
Please omit any visits that are within 30 days of the previous visit for the same memberIn other words the visit for member [a] on 20180103 should not get listed
*/;
With a as
(
SELECT *,
LAG(vd) OVER(PARTITION BY mem ORDER BY vd ASC) AS Prev
FROM #Hospital
)
,
b as
(
Select * , DATEDIFF(D, prev, vd ) as DIFF
from
a
)
,
c as
(
Select
mem, vd
FROM
b
WHERE
ISNULL(DIFF,0 ) = 0
OR
ISNULL(DIFF,0 ) > 30)
Select *
from cYou can simplify this by specifying the optional arguments for LAG(). You also don't need to have that many CTEs.
With a as
(
SELECT *,
DATEDIFF(DAY, LAG(vd, 1, '20000101') OVER(PARTITION BY mem ORDER BY vd ASC), vd) AS DIFF
FROM #Hospital
)
Select mem, vd
from a
WHERE DIFF > 30Drew
That query doesn't include 'a', '20180201', I'm not sure from the question if it should but it is more than 30 days away from the last visit your query displays.
This was directly modeled on the post I responded to, since he indicated that it matched his expected results. I saw no reason to rewrite it to produce results different from the expected results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 20, 2019 at 12:53 pm
drew.allen - Wednesday, March 20, 2019 12:46 PMJonathan AC Roberts - Tuesday, March 19, 2019 5:24 PMdrew.allen - Tuesday, March 19, 2019 3:40 PMmw_sql_developer - Tuesday, March 19, 2019 2:07 PMDone!
IF OBJECT_ID('tempdb..#Hospital') IS NOT NULL DROP TABLE #Hospital;
Create table #Hospital ( mem VARCHAR(100) , vd VARCHAR(8) );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180101' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'b', '20180501' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180103' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'c', '20180501' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180901' );
INSERT INTO #Hospital( mem , vd ) VALUES ( 'a', '20180501' );/*
I need a listing of members who had a hospital visit.
Please omit any visits that are within 30 days of the previous visit for the same memberIn other words the visit for member [a] on 20180103 should not get listed
*/;
With a as
(
SELECT *,
LAG(vd) OVER(PARTITION BY mem ORDER BY vd ASC) AS Prev
FROM #Hospital
)
,
b as
(
Select * , DATEDIFF(D, prev, vd ) as DIFF
from
a
)
,
c as
(
Select
mem, vd
FROM
b
WHERE
ISNULL(DIFF,0 ) = 0
OR
ISNULL(DIFF,0 ) > 30)
Select *
from cYou can simplify this by specifying the optional arguments for LAG(). You also don't need to have that many CTEs.
With a as
(
SELECT *,
DATEDIFF(DAY, LAG(vd, 1, '20000101') OVER(PARTITION BY mem ORDER BY vd ASC), vd) AS DIFF
FROM #Hospital
)
Select mem, vd
from a
WHERE DIFF > 30Drew
That query doesn't include 'a', '20180201', I'm not sure from the question if it should but it is more than 30 days away from the last visit your query displays.
This was directly modeled on the post I responded to, since he indicated that it matched his expected results. I saw no reason to rewrite it to produce results different from the expected results.
Drew
Yes, I wasn't sure, from the original question it said just to exclude [a] on 20180103
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply