T sql count number of visits in last 6 months

Transact-SQL
1

Hi,
I am working on patient data at hospital and need to calculate the number of ER visits and number of Inpatient visits in the last 6 months from the date of the current admission. Below is the sample data for a patient ( unique ID is MRN and for each visit there will be a distinct Account number).

imageMRN AccountNumber AdmitDt DischDt Type
MRN123 PA123 1/1/2017 1/20/2017 I
MRN123 PA234 4/1/2017 4/1/2017 ER
MRN123 PA345 4/5/2017 4/10/2017 I
MRN123 PA456 5/1/2017 5/1/2017 ER
MRN123 PA567 5/5/2017 5/10/2017 I
MRN123 PA678 5/15/2017 5/15/2017 ER
MRN123 PA789 7/1/2017 7/10/2017 I

I need to add two more columns ( number of ED visits in last 6 months and number of Inpatient visits in last 6 months) and expect the results to look like
MRN AccountNumber AdmitDt DischDt Type ERVisitsinlast6month Inpatientvisitsinlast6months
MRN123 PA123 1/1/2017 1/20/2017 I 0 0
MRN123 PA234 4/1/2017 4/1/2017 ER 0 1
MRN123 PA345 4/5/2017 4/10/2017 I 1 1
MRN123 PA456 5/1/2017 5/1/2017 ER 1 2
MRN123 PA567 5/5/2017 5/10/2017 I 1 2
MRN123 PA678 5/15/2017 5/15/2017 ER 2 3
MRN123 PA789 7/1/2017 7/10/2017 I 3 2

please suggest any ideas.
Thanks,

2

To write code, we need useable data -- executable CREATE TABLE and INSERT statements that create the data --
not just a screen shot / dump of data.

3

Based on your sample data, your results look wrong:

  • ERVisitsinlast6month for PA567
  • Inpatientvisitsinlast6months for PA789
4

I am not sure your numbers are correct - you are showing that there is one impatient visit and 0 ER visits on the row for PA234 - but that row is an ER visit and you have 1 inpatient visit prior - shouldn't it be 1 and 1?

On the next line - shouldn't it be 1 ER visit and 2 inpatient visits at that point in time? Or are you only counting the number of visits prior to the current Admit Date?

To get to your desired results:

Declare @visitTable Table (MRN char(6), AccountNumber char(5), AdmitDate date, DischDate date, VisitType char(2));

 Insert Into @visitTable
 Values ('MRN123', 'PA123', '1/1/2017', '1/20/2017', 'I')
      , ('MRN123', 'PA234', '4/1/2017', '4/1/2017', 'ER')
      , ('MRN123', 'PA345', '4/5/2017', '4/10/2017', 'I')
      , ('MRN123', 'PA456', '5/1/2017', '5/1/2017', 'ER')
      , ('MRN123', 'PA567', '5/5/2017', '5/10/2017', 'I')
      , ('MRN123', 'PA678', '5/15/2017', '5/15/2017', 'ER')
      , ('MRN123', 'PA789', '7/1/2017', '7/10/2017', 'I');

 Select *
   From @visitTable     vt
  Cross Apply (Select coalesce(sum(Case When t.VisitType = 'ER' Then 1 Else 0 End), 0) As ERVisitsLast6Months
                    , coalesce(sum(Case When t.VisitType = 'I' Then 1 Else 0 End), 0) As InpatientVisitsLast6Months
                 From @visitTable       t
                Where t.MRN = vt.MRN
                  And t.AdmitDate < vt.AdmitDate
                  And t.DischDate >= dateadd(month, -6, vt.AdmitDate)) cnt
  Order By
        AdmitDate;

If you want to include the current admission count on that row change the line:

              And t.AdmitDate < vt.AdmitDate

to

              And t.AdmitDate <= vt.AdmitDate

I am assuming that an admission where the patient was discharged less than 6 months ago based on the current admission counts as being within the 6 month range. If not - then adjust that portion to only include the admissions you want to consider...

5

Thanks Jeff for taking time and suggesting the code when i tried to run that on my end the two new last 6 months columns are always zero they are not counting any previous visits.
Any suggestion is greatly appreciated.
To the question about counting the current encounter its not counted in the last 6 months visits its only the prior visits that are counted.
Thanks,

6

This runs fine on the sample data provided - if it is not working against a different table then there is something different about that table and data.

If your counts are all zeroes - then i would suspect that the Visit Types are not actually 'I' or 'ER'.

7

Thank you it was problem on my end as the dates was restricted to an year instead of all in the table to reduce the processing time.
it works fine now.
Thanks for all your help.