Calculate Length of Stay grouped by calendar months

Question

here is what I am dealing with: I am given a table (Visit) with the following schema with sample data.

VISIT

VisitNo   Location  AdmissionDate  DischargeDate LengthOfStay
1         A         2012-04-28     2012-05-30     32
2         A         2012-04-20     2013-05-20     90
3         B         2012-04-01     2012-05-01     30
4         B         2012-05-01     2012-05-03     2
.....................................................

The requirements is that the returned data set is in the following structure. Total LengthOfStay of each location by Calendar Month(YYYYMM).

CalendarMonth  TotalLengthOfStayEachMonth(AdmissionToDate)  Location
201204          xxx                                          x
201205          yyyy                                         y
201206          zzzz                                         z
.........      ...............

The calculation of TotalLengthOfStayEachMonth is a little tricky. The days are counted since the AdmissionDate (admissionToDate), not literally 30 days per month basis. For example, The first record in the VISIT table has;

• 2 days of stay in 201204
• 2+30 = 32 of days in 201205 and so forth...

Thanks in advance for your suggestions... FYI, we have hundreds of locations, thousand of vists and 5 years of data.

Answer 1

While I'd prefer seeing your desired output, perhaps something like this will help get you started:

SELECT 
    CONVERT(char(6), AdmissionDate, 112)  as CalendarMonth  ,
    SUM(DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,AdmissionDate)+1,0)))-DAY(AdmissionDate)) as TotalLengthOfStayEachMonth,
    Location
FROM VISIT
GROUP BY CONVERT(char(6), AdmissionDate, 112), Location

Adding the Location will possibly give you duplicate CalendarMonths, but I assume that's ok.

EDIT --

I played around with this a little more as I realized some months would be missing dates if the DischargeDate was greater than 1 month over the AdmissionDate, and came up with this -- it uses the spt_values table:

select 
   CONVERT(char(6), AdmissionDate+v.number, 112)  as CalendarMonth,
   COUNT(*) Days,
   Location
from Visit t
inner join master..spt_values v
  on v.type='P' and v.number <= DATEDIFF(d, AdmissionDate, DischargeDate)
group by Location, CONVERT(char(6), AdmissionDate+v.number, 112)
order by CONVERT(char(6), AdmissionDate+v.number, 112), Location

Here is the SQL Fiddle.

-- Another Edit

While reviewing another answer, and not really understanding your situation (need that desired output), if your desired output is 2 days in April for your first record, then you just need to add and v.number <> 0 to the join above -- easy enough, just wasn't sure what you were requesting. This should be the simplest solution:

select 
   CONVERT(char(6), AdmissionDate+v.number, 112)  as CalendarMonth,
   COUNT(*) Days,
   Location
from Visit t
inner join master..spt_values v
  on v.type='P' and v.number <= DATEDIFF(d, AdmissionDate, DischargeDate) and v.number <> 0
group by Location, CONVERT(char(6), AdmissionDate+v.number, 112)
order by CONVERT(char(6), AdmissionDate+v.number, 112), Location

More fiddle.

Hope some of this helps.

Good luck.

Answer 2

This will do (SQLFiddle):

with everyday (VisitNo, Location, dateOfStay)
AS (
  SELECT VisitNo, Location, dateadd(dd, 1, AdmissionDate)
  FROM VISIT
  UNION ALL
  SELECT e.VisitNo, e.Location, dateadd(dd, 1, e.dateOfStay)
  FROM VISIT v INNER JOIN everyday e ON v.VisitNo = e.VisitNo and
    e.dateofStay < v.DischargeDate
  )
SELECT CONVERT(VARCHAR(7), dateofstay, 121), VisitNo, Location, count(*) 
FROM everyday 
GROUP BY CONVERT(VARCHAR(7), dateofstay, 121), VisitNo, Location
ORDER BY 2,1
OPTION (MAXRECURSION 500);

If you want to group by Location only, use this one:

with everyday (VisitNo, Location, dateOfStay)
AS (
  SELECT VisitNo, Location, dateadd(dd, 1, AdmissionDate)
  FROM VISIT
  UNION ALL
  SELECT e.VisitNo, e.Location, dateadd(dd, 1, e.dateOfStay)
  FROM VISIT v INNER JOIN everyday e ON v.VisitNo = e.VisitNo and
    e.dateofStay < v.DischargeDate
  )
SELECT CONVERT(VARCHAR(7), dateofstay, 121), Location, count(*) 
FROM everyday 
GROUP BY CONVERT(VARCHAR(7), dateofstay, 121), Location
ORDER BY 2,1
OPTION (MAXRECURSION 500);