May 1, 2017 at 3:05 pm
create table #t( admdt datetime, dischdt datetime, ID int );
INSERT INTO #t(admdt, dischdt,ID) VALUES ( GETDATE()-100, NULL,1 );
INSERT INTO #t(admdt, dischdt,ID) VALUES ( GETDATE()-8, GETDATE()+1,2 );
INSERT INTO #t(admdt, dischdt,ID) VALUES ( GETDATE()-8, GETDATE()+2 ,3);
INSERT INTO #t(admdt, dischdt,ID) VALUES ( GETDATE()+2, GETDATE()+3,2 );
INSERT INTO #t(admdt, dischdt,ID) VALUES ( GETDATE()+4, GETDATE()+5,2 );
INSERT INTO #t(admdt, dischdt,ID) VALUES ( GETDATE()+3, GETDATE()+5,3 );
INSERT INTO #t(admdt, dischdt,ID) VALUES ( GETDATE()-100,GETDATE()+1, 4 );
INSERT INTO #t(admdt, dischdt,ID) VALUES ( GETDATE()+2,GETDATE()+3, 4 );
INSERT INTO #t(admdt, dischdt,ID) VALUES ( GETDATE()+5,GETDATE()+6, 4 );
INSERT INTO #t(admdt, dischdt,ID) VALUES ( GETDATE()-100,NULL, 5 );
INSERT INTO #t(admdt, dischdt,ID) VALUES ( GETDATE()-100,NULL, 6 );
INSERT INTO #t(admdt, dischdt,ID) VALUES ( GETDATE()-100,NULL, 7 );
--Help me write the SQL here
--I need a report ( Select stmt ) with the following columns ID, admt1, dischdt1, admt2, dischdt2
--The select stmt should only consider a span of GETDATE()-7 to GETDATE()+7
--If a patient(ID) has a discharge or admit within the (GETDATE()-7 to GETDATE()+7) period then I need to capture the earliest 2 admit dates and discharge dates
--The following SQL gets the rows I needed. However, how can i have one line per ID and then have the smallest admts listed and their corresponding discharge dates listed.
--I thnk using PIVOT may help.
--So it may look like
--ID admdt1 dischdt1 admdt2 dischdt2
--1 1/21/17 NULL NULL NULL
--2 4/23/17 5/2/17 5/3/17 5/4/17
Select * FROM
(
Select
* ,
ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY admdt ) as RN
FROM #t
WHERE
dischdt BETWEEN GETDATE() - 7 AND GETDATE() + 7
OR
admdt BETWEEN GETDATE() - 7 AND GETDATE() + 7
OR
dischdt IS NULL
) a
WHERE
a.RN < 3
drop table #t;
May 1, 2017 at 3:21 pm
Can you show us what the full results from that sample data set should look like?
At most two of each date in the columns?
PIVOT may work, but probably too complicated. A CASE statement for each column and a GROUP BY on ID may work
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2017 at 5:14 pm
Give this a try...
WITH
cte_AddRN AS (
SELECT
t.admdt,
t.dischdt,
t.ID,
RN = ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.admdt)
FROM
#t t
WHERE
t.admdt BETWEEN DATEADD(dd, -7, GETDATE()) AND DATEADD(dd, 7, GETDATE())
OR
t.dischdt BETWEEN DATEADD(dd, -7, GETDATE()) AND DATEADD(dd, 7, GETDATE())
OR
t.dischdt IS NULL
)
SELECT
arn.ID,
admdt1 = MAX(CASE WHEN arn.RN = 1 THEN arn.admdt END),
dischdt1 = MAX(CASE WHEN arn.RN = 1 THEN arn.dischdt END),
admdt2 = MAX(CASE WHEN arn.RN = 2 THEN arn.admdt END),
dischdt2 = MAX(CASE WHEN arn.RN = 2 THEN arn.dischdt END)
FROM
cte_AddRN arn
WHERE
arn.RN <= 2
GROUP BY
arn.ID;
May 2, 2017 at 11:18 am
Jason A. Long - Monday, May 1, 2017 5:14 PMGive this a try...
WITH
cte_AddRN AS (
SELECT
t.admdt,
t.dischdt,
t.ID,
RN = ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.admdt)
FROM
#t t
WHERE
t.admdt BETWEEN DATEADD(dd, -7, GETDATE()) AND DATEADD(dd, 7, GETDATE())
OR
t.dischdt BETWEEN DATEADD(dd, -7, GETDATE()) AND DATEADD(dd, 7, GETDATE())
OR
t.dischdt IS NULL
)
SELECT
arn.ID,
admdt1 = MAX(CASE WHEN arn.RN = 1 THEN arn.admdt END),
dischdt1 = MAX(CASE WHEN arn.RN = 1 THEN arn.dischdt END),
admdt2 = MAX(CASE WHEN arn.RN = 2 THEN arn.admdt END),
dischdt2 = MAX(CASE WHEN arn.RN = 2 THEN arn.dischdt END)
FROM
cte_AddRN arn
WHERE
arn.RN <= 2
GROUP BY
arn.ID;
Thx Jason .. This worked
May 2, 2017 at 4:23 pm
Jason A. Long - Monday, May 1, 2017 5:14 PMGive this a try...
WITH
cte_AddRN AS (
SELECT
t.admdt,
t.dischdt,
t.ID,
RN = ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.admdt)
FROM
#t t
WHERE
t.admdt BETWEEN DATEADD(dd, -7, GETDATE()) AND DATEADD(dd, 7, GETDATE())
OR
t.dischdt BETWEEN DATEADD(dd, -7, GETDATE()) AND DATEADD(dd, 7, GETDATE())
OR
t.dischdt IS NULL
)
SELECT
arn.ID,
admdt1 = MAX(CASE WHEN arn.RN = 1 THEN arn.admdt END),
dischdt1 = MAX(CASE WHEN arn.RN = 1 THEN arn.dischdt END),
admdt2 = MAX(CASE WHEN arn.RN = 2 THEN arn.admdt END),
dischdt2 = MAX(CASE WHEN arn.RN = 2 THEN arn.dischdt END)
FROM
cte_AddRN arn
WHERE
arn.RN <= 2
GROUP BY
arn.ID;
Jason... have a look at the following article for a simplification of what your good code does. You only need 2 comparisons to solve all 6 possibilities of two overlapping date ranges.
http://www.sqlservercentral.com/articles/T-SQL/105968/
Not including the mistake in the OPs table of using NULL as an EndDate , here's the diagram of the only 6 possibilities and, like I said, they can all be solved with just 2 comparisons. See the article.
| Desired |
Left "Outside"| Period |Right "OutSide"
<-------------|---------|-------------->
| |
S--1--E | S--3--E | S--2--E
| |
S--5--E S--4--E
| |
S----------|----6----|----------E
| |
DS DE
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2017 at 5:13 pm
Jeff Moden - Tuesday, May 2, 2017 4:23 PMJason... have a look at the following article for a simplification of what your good code does. You only need 2 comparisons to solve all 6 possibilities of two overlapping date ranges.
http://www.sqlservercentral.com/articles/T-SQL/105968/Not including the mistake in the OPs table of using NULL as an EndDate , here's the diagram of the only 6 possibilities and, like I said, they can all be solved with just 2 comparisons. See the article.
| Desired |
Left "Outside"| Period |Right "OutSide"
<-------------|---------|-------------->
| |
S--1--E | S--3--E | S--2--E
| |
S--5--E S--4--E
| |
S----------|----6----|----------E
| |
DS DE
Good call Jeff. Yes... It makes perfect sense...
The WHERE clause of the CTE can be reduced to the following...
WHERE
(
t.admdt <= DATEADD(dd, 7, GETDATE())
AND
t.dischdt >= DATEADD(dd, -7, GETDATE())
)
OR
t.dischdt IS NULL
I also just noticed that I committed one of my own biggest pet peeves... I was using OR logic w/o proper use of parentheses... :crazy:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply