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...

I was a little bored but here is my solution. 😀

DECLARE @patients

TABLE(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...

Just by looking at this I would try splitting the query by the OR statement into two separate queries and UNION them together.

...

FROM dimension

INNER JOIN dates

ON dates.date BETWEEN...

Here is my solution...*edit had to remove numbers CTE :-P*

;WITH NUMBERS(value)

AS(SELECT 60

UNION ALL

SELECT 50

UNION ALL

SELECT 50

UNION ALL

SELECT 45

UNION ALL

SELECT 30

UNION ALL

SELECT 25

UNION ALL

SELECT 15

UNION ALL

SELECT 10

UNION ALL

SELECT 10

)

, RANKED...