StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POGetting a Count on a Dynamic Date Range
text
Body
copied!<p>I have asked information on this query before but the question I am asking now is entirely different from the question before. See <a href="https://stackoverflow.com/questions/17149744/empty-result-set-from-multi-part-query">here</a> for previous question if wanted.</p> <p>This code below, is supposed to calculate a number for a person, it should use the following information:</p> <p>Gets a Length Of Stay and gives each range a score, <-- this works</p> <p>Acuity score, <-- this works</p> <p>Comorbid Score, <-- this works</p> <p>ER Visits score. <-- this does not work</p> <p>I will use myself as an example:</p> <p>Say I come to the Emergency Room June 18th 2013 and get admitted to the hospital. Upon discharge I will have a length of stay, which is scored based on the case statement in <code>@T1</code>, the Acuity is also calculated there. </p> <p>In the second query, I should get a count of how many times I came to the emergency room in the previous 6 months <strong><em>based on the June 18th 2013 date</em></strong>. This is the point that is giving me the greatest grief and is the point at which my entire query breaks down. What is tricky for me is that every time a person comes in, they get a visit id, but the person has only one MRN as their unique identifier. So I have to count, how many times does someone come in, in the last 180 days based on the date of the discharge of each visit. For example:</p> <p>6-18-2013 <code>MCP</code> comes to the ER and gets admitted</p> <p>6-20-2013 <code>MCP</code> gets discharged</p> <p>Length of stay = 2</p> <p>Acuity = 3</p> <p>ER Visits for <code>MCP</code> 6-18-2013 (don't count this) 6-01-2013 (count this), 5-15-2013 (count this) 2-19-2013 (count this) 1-01-2013 (do not count this) ER Visits = 3 not 5</p> <p>It is important to note that a persons' visit is scored, not the person themselves, this is due to the date range limit on the ER visits. So I could have a score for my June 18th Visit and have a different score on a July 30th visit.</p> <p>I have given the query in it's entirety so far so that my logic from start to finish can be evaluated. I am using SQL Server 2008.</p> <p><strong><em>LOS and Acute IP Scoring Query</em></strong></p> <pre><code>-- VARIABLE DECLARATION AND INITIALIZATION SET ANSI_NULLS OFF GO DECLARE @SD DATETIME DECLARE @ED DATETIME -- QUERY ONE THAT GETS THE SCORING FOR THE LOS AND ACUITY --########--] -- THESE ARE PATIENT ADMIT DATES SET @SD = '2013-01-01' SET @ED = '2013-01-31' -- @T1 --------------------------------------------------------------] -- TABLE DECLARATION WHERE ALL RESULTS WILL GET DEPOSITED OF THE FIRST -- QUERY WILL GET DEPOSITED. THIS TABLE WILL GET USED IN CONJUNCTION -- WITH TWO OTHER TABLES IN ORDER TO COMPUTE THE FINAL DECLARE @T1 TABLE ( ENCOUNTER_ID VARCHAR(200) , MRN VARCHAR(200) , [PT AGE] VARCHAR(200) , [PT NAME] VARCHAR(500) , [DAYS STAY] VARCHAR(200) , [LACE DAYS SCORE] INT , [ACUTE ADMIT SCORE] VARCHAR(100) , ARRIVAL DATETIME ) ----------------------------------------------------------------------] -- @T1 RECORD INSERTIONS #############################################] INSERT INTO @T1 SELECT A.PT_NO , A.MED_REC_NO , A.PT_AGE , A.PT_NAME , A.DAYS_STAY , A.LACE_DAYS_SCORE , A.ACUTE_ADMIT_LACE_SCORE , A.ADM_DATE --####################################################################] -- DAYS STAY, ACUTE ADMIT AND RELATED SCORING ------------------------- FROM (SELECT PT_NO , Med_Rec_No , Pt_Age , Pt_Name , Days_Stay , CASE WHEN Days_Stay < 1 THEN 0 WHEN Days_Stay = 1 THEN 1 WHEN Days_Stay = 2 THEN 2 WHEN Days_Stay = 3 THEN 3 WHEN Days_Stay BETWEEN 4 AND 6 THEN 4 WHEN Days_Stay BETWEEN 7 AND 13 THEN 5 WHEN Days_Stay >= 14 THEN 6 END AS LACE_DAYS_SCORE , CASE WHEN PLM_PT_ACCT_TYPE = 'I' THEN 3 ELSE 0 END AS ACUTE_ADMIT_LACE_SCORE , ADM_DATE FROM SMSDSS.BMH_PLM_PTACCT_V WHERE DSCH_DATE BETWEEN @SD AND @ED AND Plm_Pt_Acct_Type = 'I' )A --SELECT * FROM @T1 </code></pre> <p><strong><em>The results of the above should look like this:</em></strong></p> <pre><code>ENCOUNTER_ID| MRN | AGE | PT NAME | DAYS STAY | LACE DAYS STAY |ACUTE SCR | ARRIVAL 123456789 | 123 | 65 | MCP | 5 | 4 | 3 | 6/18/2013 </code></pre> <p><strong><em>The ER Count Query As fixed by @JoaoLeal:</em></strong></p> <pre><code>-- ER VISITS QUERY DECLARE @CNT TABLE ( MRN VARCHAR(100) , VISIT_ID VARCHAR(100) , VISIT_DATE DATETIME , VISIT_COUNT INT ) INSERT INTO @CNT SELECT A.MRN , A.VISIT_ID , A.VISIT_DATE , COUNT(B.VISIT_ID) AS VISIT_COUNT FROM (SELECT MED_REC_NO AS MRN, VST_START_DTIME AS VISIT_DATE, PT_NO AS VISIT_ID FROM smsdss.BMH_PLM_PtAcct_V WHERE (( PLM_PT_ACCT_TYPE = 'I' AND ADM_SOURCE NOT IN ('RA', 'RP' ) ) OR PT_TYPE = 'E') AND vst_start_dtime BETWEEN @SD AND @ED)A LEFT JOIN (SELECT MED_REC_NO AS MRN, VST_START_DTIME AS VISIT_DATE, PT_NO AS VISIT_ID FROM smsdss.BMH_PLM_PtAcct_V WHERE (( PLM_PT_ACCT_TYPE = 'I' AND ADM_SOURCE NOT IN ('RA', 'RP' ) ) OR PT_TYPE = 'E') AND vst_start_dtime BETWEEN @SD AND @ED)B ON A.MRN = B.MRN AND A.VISIT_DATE > B.VISIT_DATE AND A.VISIT_DATE-180 <B.VISIT_DATE GROUP BY A.MRN, A.VISIT_ID, A.VISIT_DATE ORDER BY A.MRN --SELECT * FROM @CNT </code></pre> <p><strong><em>The Desired Output is something like this:</em></strong></p> <pre><code>MRN | Visit_ID | Visit_Date | Visit_Count 123 | 12345678 | 6/18/2013 | 1 </code></pre> <p><strong><em>The Co-morbidity Query</em></strong></p> <pre><code>-- CO MORBIDITY QUERY DECLARE @CM TABLE ( ENCOUNTER_ID VARCHAR(200) , [MRN CM] VARCHAR(200) , NAME VARCHAR(500) , [CC GRP ONE SCORE] VARCHAR(20) , [CC GRP TWO SCORE] VARCHAR(20) , [CC GRP THREE SCORE] VARCHAR(20) , [CC GRP FOUR SCORE] VARCHAR(20) , [CC GRP FIVE SCORE] VARCHAR(20) , [CC LACE SCORE] INT ) --##################################################################### INSERT INTO @CM SELECT C.PT_NO , C.MED_REC_NO , C.PT_NAME , C.PRIN_DX_CD_1 , C.PRIN_DX_CD_2 , C.PRIN_DX_CD_3 , C.PRIN_DX_CD_4 , C.PRIN_DX_CD_5 , CASE WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 0 THEN 0 WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 1 THEN 1 WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 2 THEN 2 WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 3 THEN 3 WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 4 THEN 4 WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 5 THEN 5 WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) >= 6 THEN 6 END AS CC_LACE_SCORE FROM ( SELECT distinct PT_NO , MED_REC_NO , PT_NAME , CASE WHEN PRIN_DX_CD IN ( List of Codes ) THEN 1 ELSE 0 END AS PRIN_DX_CD_1 , CASE WHEN PRIN_DX_CD IN ( List of Codes ) THEN 2 ELSE 0 END AS PRIN_DX_CD_2 , CASE WHEN PRIN_DX_CD IN ( List of Codes ) THEN 3 ELSE 0 END AS PRIN_DX_CD_3 , CASE WHEN PRIN_DX_CD IN ( List of Codes ) THEN 4 ELSE 0 END AS PRIN_DX_CD_4 , CASE WHEN PRIN_DX_CD IN ( List of Codes ) THEN 6 ELSE 0 END AS PRIN_DX_CD_5 FROM smsdss.BMH_PLM_PtAcct_V WHERE dsch_Date BETWEEN @SD AND @ED )C GROUP BY C.PT_NO , C.MED_REC_NO , C.PT_NAME , C.PRIN_DX_CD_1 , C.PRIN_DX_CD_2 , C.PRIN_DX_CD_3 , C.PRIN_DX_CD_4 , C.PRIN_DX_CD_5 ORDER BY (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) --SELECT * FROM @CM </code></pre> <p><strong><em>Desired Output like this:</em></strong></p> <pre><code>Visit_ID | MRN | Name | Grp 1 | Grp 2 | Grp 3 | Grp 4 | Grp 5 | Total 1234567 | 123 | MCP | 0 | 2 | 3 | 4 | 0 | 6 </code></pre> <p><strong><em>Put it altogether:</em></strong></p> <pre><code>DECLARE @LACE_MSTR TABLE ( MRN VARCHAR(200) ,ENCOUNTER VARCHAR(200) , AGE VARCHAR(30) , NAME VARCHAR (500) , [LACE DAYS SCORE] INT , [LACE ACUTE IP SCORE] INT , [LACE ER SCORE] INT , [LACE COMORBID SCORE] INT ) INSERT INTO @LACE_MSTR SELECT Q1.MRN , Q1.ENCOUNTER_ID , Q1.[PT AGE] , Q1.[PT NAME] , Q1.[LACE DAYS SCORE] , Q1.[ACUTE ADMIT SCORE] , CASE WHEN Q1.[MRN COUNT] IS NULL THEN 0 WHEN Q1.[MRN COUNT] = 1 THEN 1 WHEN Q1.[MRN COUNT] = 2 THEN 2 WHEN Q1.[MRN COUNT] = 3 THEN 3 WHEN Q1.[MRN COUNT] >= 4 THEN 4 ELSE 0 END AS [LACE ER SCORE] , Q1.[CC LACE SCORE] FROM ( SELECT DISTINCT T1.ENCOUNTER_ID , T1.MRN , T1.[PT AGE] , T1.[PT NAME] , T1.[LACE DAYS SCORE] , T1.[ACUTE ADMIT SCORE] , CNT.[MRN COUNT] , CM.[CC LACE SCORE] FROM @T1 T1 LEFT OUTER JOIN @CNT CNT ON T1.MRN = CNT.MRN JOIN @CM CM ON CM.ENCOUNTER_ID = T1.ENCOUNTER_ID )Q1 SELECT DISTINCT ENCOUNTER , MRN , AGE , NAME , [LACE DAYS SCORE] , [LACE ACUTE IP SCORE] , [LACE ER SCORE] , [LACE COMORBID SCORE] , [LACE DAYS SCORE]+[LACE ACUTE IP SCORE]+[LACE ER SCORE]+[LACE COMORBID SCORE] AS [TOTAL LACE] FROM @LACE_MSTR GROUP BY ENCOUNTER , MRN , AGE , NAME , [LACE DAYS SCORE] , [LACE ACUTE IP SCORE] , [LACE ER SCORE] , [LACE COMORBID SCORE] </code></pre> <p>This Query does a join on the encounter number for the <code>@CM ON @T1</code> tables and by MRN on the ER visit count and T1 table. For some very odd reason, when I do the final join like <code>@CM on @T1</code> by MRN, I will get multiple records for a single Encounter number:</p> <pre><code>ENCOUNTER | MRN | CM SCORE 12345 | 99999 | 9 12345 | 99999 | 11 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload