financial length of stay by each month
-
Hi Everyone,
I am trying to calculate a financial length of stay by each month.
Now, i have a table with the below structure.(second table with 5 columns)
I want to group the length of stay with YearMonth format.
For example in the below table i have patient 1 who has 17days in Feb1989 and 14days in March(length of days is 17+14=31)
and patient 2 has 1day in Feb and 3 days in March (total length of stay is 1+3=4).
My output should look like
i have data from 1989 to 2011 with approximately 243 different months to date like jan1989,feb1989........Dec2011.
I know we can group by yearmonth but i not getting data and i am struggling to write a query which gives me the above format.
Please any one kindly help me in this i am not that good coder i tried by all means and trying to find some solution.
-
I would suggest, to get a tested answer to your question, that you post the table definition(s) along with sample data and required results in a readily consumable format. To do so please click on the first link in my signature block and read the article (which contains the necessary T-SQL to do what I have requested.
Remember the people here who want to assist (are volunteers) and you can help them to help you with a tested solution only if you provide them with readily consumable data which a .png is not.
-
I was a little bored but here is my solution. 😀
DECLARE @patientsTABLE(patient_id INT,
visitno INT,
admission_date DATE,
discharge_date DATE,
length_of_stay INT
)
INSERT @patients
SELECT 1,11,'2/12/1989','3/15/1989',31
UNION ALL
SELECT 2,22,'2/28/1989','3/4/1989',4
UNION ALL
SELECT 3,33,'3/18/1989','3/21/1989',3
UNION ALL
SELECT 4,44,'3/22/1989','3/25/1989',3
UNION ALL
SELECT 5,55,'4/4/1989','4/5/1989',1
UNION ALL
SELECT 6,66,'4/19/1989','4/25/1989',6
UNION ALL
SELECT 7,77,'4/24/1989','4/28/1989',4
UNION ALL
SELECT 8,88,'5/29/1989','6/11/1989',13
UNION ALL
SELECT 9,99,'7/26/1989','7/27/1989',1
;WITH tally
AS (
SELECT TOP 38000 n = 18262 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns c1 CROSS JOIN sys.all_columns c2
)/*Dates from 1/1/1950 to around 1/1/2050*/
, convertdates
AS (SELECT
startdate = DATEDIFF(d,0,admission_date),
enddate= DATEDIFF(d,0,discharge_date)
FROM @patients
)
SELECT
yearmonth = LEFT(DATENAME(mm,d),3)+'-'+RIGHT(DATENAME(YY,d),2),
[length of stay for each month] = COUNT(d)
FROM
(SELECT d = DATEADD(d,n,0)
FROM convertdates INNER JOIN tally ON n BETWEEN startdate AND enddate) x
GROUP BY LEFT(DATENAME(mm,d),3)+'-'+RIGHT(DATENAME(YY,d),2)
-
i really aplogize to everyone out there, i am a newbie to this community i just gone to the links you mentioned i felt so bad about my request.
i will definitely try to follow the minimum etiquette before i ask for any help!
@SQL Padawan thanks for your response and i really appreciate it.
-
I've changed the select statement to actually order by Year then Month for more flexibility. :hehe:
SELECT
yearmonth = LEFT(DATENAME(m,DATEADD(m,m,-1)),3)+'-'+RIGHT(y,2),
[length of stay for each month] = COUNT(m)
FROM (
SELECT
m = MONTH(d),y = YEAR(d)
FROM
(SELECT d = DATEADD(d,n,0)
FROM convertdates INNER JOIN tally ON n BETWEEN startdate AND enddate) x
) x2
GROUP BY y,m
ORDER BY y,m
-
SQLKiller
I really aplogize to everyone out there, i am a newbie to this community i just gone to the links you mentioned i felt so bad about my request.
i will definitely try to follow the minimum etiquette before i ask for any help!
No need to apologize we were all Newbies.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply