Calculate length of stay grouped by calendar months
here is what i mean: I am assigned 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 requirement is that the returned dataset 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
......... ...............
Calculating TotalLengthOfStayEachMonth is a little more complicated. Days are counted from the day of admission to the appointment (admissionToDate), not literally 30 days a month. For example, the first entry in the VISIT table has:
- 2 days stay in 2012
- 2 + 30 = 32 days in 201205, etc.
Thanks in advance for your suggestions ... FYI, we have hundreds of locations, thousands of whists and 5 years of data.
source to share
While I prefer to see the desired output, perhaps something like this will help you get 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 probably give you duplicate CalendarMonths, but I guess that's okay.
EDIT -
I played around with this a bit as I realized that some months would be missing if the DischargeDate exceeded 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 .
- other editing
Looking at the other answer and not understanding your situation (you need the output you want), if your desired result is 2 days in April for your first entry, you just need to add and v.number <> 0 to the connection above - simple enough, it just wasn't confidence in what you requested. 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 details on fiddle .
Hope it helps.
Good luck.
source to share
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 only want to group by location, use the following command:
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);
source to share