Solved

How do I calculate moving "weeks overdue" over a 12-month period in T-sql?

Posted on 2015-02-24
31
26 Views
Last Modified: 2016-06-19
NB SQL Server 2012 database, so can apply new Windowing Functions, if applicable.


Hello Experts!

The Table:

DateEvent is the patient start date.
DateDischarge is the patient discharge date.
WeeksOverdue is the count of weeks from DateEvent to today (simple DATEDIFF between DateEvent and GetDate())

NB table date format is UK English

Current Table

The Challenge:

Current setup solves the problem for THIS MONTH. I can see that the top 3 records are in breach for February 2015.

However, I need to do a 12 month historical trend.

So, in June 2014 only 1 the first record is in breach. In October 2014, the first three records are in breach.

As this is February 2015 the current 12-month period would be March 2014 to February 2015. This will obviously change next month.

Using T-SQL how would you approach this problem, elegantly?

Looking forward to your feedback!

JohnAeris
0
Comment
Question by:JohnAeris
  • 15
  • 13
  • +1
31 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40628271
Define "in breach".
0
 

Author Comment

by:JohnAeris
ID: 40628295
In breach = an exception. All patients should be seen within 12 weeks.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40628308
Now I am confused, 12 weeks or 12 months. I think you need to describe the process in more detail and what exactly you need to see with examples.
0
 

Author Comment

by:JohnAeris
ID: 40628372
All patients should be seen within 12 weeks.

This report tracks all patients who HAVE NOT been discharged within 12 weeks. So, discharge date could be NULL or a date 12 weeks over the DateEvent.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40628425
You use 2 terms: to be seen and discharged. Why do you mention to be seen if what you are interested in is discharged. Also you have patients in your example way over due past 12 weeks.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40628429
This will give you the report:
--drop table #tmp
create table #tmp(OrganisationName varchar(25),WeeksOverdue int, DateEvent datetime,DateDischarge datetime)
insert into #tmp
select 'Nursing',null, '2013-12-05',null union all
select 'Nursing',null, '2014-09-30',null union all
select 'Nursing',null, '2014-09-23',null union all
select 'Nursing',null, '2015-01-26',null

select 
	OrganisationName,
	datediff(w, DateEvent, isnull(DateDischarge, getdate()))-12 as WeeksOverdue,
	convert(varchar(25), DateEvent, 103) as DateEvent,
	DateDischarge
from 
	#tmp
where
	datediff(w, DateEvent, isnull(DateDischarge, getdate()))>12

Open in new window

I have no idea how did you get your numbers.
0
 

Author Comment

by:JohnAeris
ID: 40628432
For a patient to be discharged they must be seen.
All patients must be "seen" within 12 weeks. Once seen, they are discharged.

I am interested in all patients not discharged within 12 weeks.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40628436
See above.
0
 

Author Comment

by:JohnAeris
ID: 40628437
@Zberteoc: I explain what the numbers mean above.

Thanks for your attempt. This doesn't appear to allow for discharged dates that are greater than 12 weeks.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40628685
Yes it does, that is what the WHERE clause is for:

datediff(w, DateEvent, isnull(DateDischarge, getdate()))>12

if the date discharge is NULL it will be replaced with the current date if not it will be used to get the difference in weeks from the DateEvent and will return only the cases where that difference is > 12.
0
 

Author Comment

by:JohnAeris
ID: 40628741
Thanks for SQL Zberteoc! I ran it, and the output is confusing.

So, to clarify, based on the dataset above, the goal is to achieve something like the following:

Something like this
Basically, the SQL should

Take todays date,
Check for NULL discharge and check for discharges that happen this month, then count each occurrence where discharge date is over 12 weeks, or discharge that is Null.
Then do this same for last month (Jan-2015): is discharge null? Did discharge occur this month? Is this timeframe longer than 12 weeks?
Again for month before last: Dec-2014, and so on and so forth going back 12 months.

In your code above, you use GetDate for this month. This measures everything this month, so we'd need to do something else for Jan 2015, and again for December 2014 etc...

The solution I have is absolutely massive! Which is why I was looking for something more elegant. there must be a better way.
0
 

Author Comment

by:JohnAeris
ID: 40628753
This is my current solution, Looking for a more elegant way of achieving this goal


WITH cteSubSet AS

(
SELECT 		
OrganisationName
,[DateEvent]
,[DateDischarge]

	  --This month count
		,CASE
			--Check all NULLS from TODAY
			WHEN DateDischarge IS NULL 
				THEN DATEDIFF(week,DateEvent,GETDATE())
		
			--Check if anything discharged from 1st Jan 2015 onwards
			WHEN DateDischarge BETWEEN DATEADD(dd,1,eomonth(getdate(),-12)) AND eomonth(GETDATE())
				THEN DATEDIFF(week,DateEvent,DateDischarge)
			ELSE NULL END DischargeWeeks
		
		,CASE
			WHEN DateDischarge IS NULL THEN CONVERT(char(4),DATEPART(year,GETDATE())) + '-' + RIGHT('0' + RTRIM(MONTH(GETDATE())),2)
			ELSE CONVERT(char(4),DATEPART(year,DateDischarge)) + '-' + RIGHT('0' + RTRIM(MONTH(DateDischarge)),2)
		END AS YearMonth
		
			  	  
FROM [ReferralCaseLoad] 

WHERE (DateDischarge > EOMONTH(getdate(),-2) OR DateDischarge IS NULL)

)

SELECT
	OrganisationName,
	YearMonth,
	COUNT(DischargeWeeks) AS NurseCaseload12WeekBreach

FROM cteSubSet
WHERE DischargeWeeks > 12

GROUP BY 
	OrganisationName,
	CaseloadTeam,
	YearMonth,
	YearMonthLabel;
GO

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40628761
SELECT
    t.OrganisationName,
    DATEDIFF(DAY, t.DateEvent, ISNULL(t.DateDischarge, GETDATE())) / 7 AS WeeksOverdue,
    t.DateEvent,
    t.DateDischarge
FROM
    #tmp t
WHERE
    t.DateEvent >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -11, 0) AND
    t.DateEvent <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) AND
    ((t.DateDischarge IS NULL AND GETDATE() >= DATEADD(DAY, 12 * 7, t.DateEvent)) OR
     (t.DateDischarge >= DATEADD(DAY, 12 * 7, t.DateEvent)))

Edit: Ignore this code I guess, just saw your latest posts.  [That would have been a good description to start with.]
0
 

Author Comment

by:JohnAeris
ID: 40628827
Scott & Zberteoc: apologies. Should have posted the code first.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40628975
Can you tell me how did you get the number 64 for the first case?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 40628996
Nevermind.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40629015
Is this better? Not for a whole year though:
select 
	OrganisationName,
	convert(varchar(7), isnull(DateDischarge,getdate()),121) as YearMonth,
	sum(case when datediff(wk, DateEvent, isnull(DateDischarge, getdate()))>12 then 1 else 0 end) as NurseCaseload12WeekBreach
from 
	#tmp t
group by 
	OrganisationName,
	convert(varchar(7), isnull(DateDischarge,getdate()),121) 

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40629021
Actually probably this si better:
select 
	OrganisationName,
	convert(varchar(7), isnull(DateDischarge,getdate()),121) as YearMonth,
	count(*) as NurseCaseload12WeekBreach
from 
	#tmp t
where
	datediff(wk, DateEvent, isnull(DateDischarge, getdate()))>12
group by 
	OrganisationName,
	convert(varchar(7), isnull(DateDischarge,getdate()),121) 

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40629773
multi-column "expected result"For a multi-column output with each column representing a different month (in the past) I think you need something like this as the query:
SELECT
      OrganisationName
    , SUM(
            CASE WHEN CA.DateCompare >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -1, 0)
                 AND  CA.DateCompare <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())   , 0)
            THEN 1 ELSE 0 END
         ) AS "MNTH_1"
    , SUM(
            CASE WHEN CA.DateCompare >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -2, 0)
                 AND  CA.DateCompare <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -1, 0)
            THEN 1 ELSE 0 END
         ) AS "MNTH_1"
    , SUM(
            CASE WHEN CA.DateCompare >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -3, 0)
                 AND  CA.DateCompare <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -2, 0)
            THEN 1 ELSE 0 END
         ) AS "MNTH_2"
    , SUM(
            CASE WHEN CA.DateCompare >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -4, 0)
                 AND  CA.DateCompare <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -3, 0)
            THEN 1 ELSE 0 END
         ) AS "MNTH_3"
    , SUM(
            CASE WHEN CA.DateCompare >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -5, 0)
                 AND  CA.DateCompare <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -4, 0)
            THEN 1 ELSE 0 END
         ) AS "MNTH_4"
    , SUM(
            CASE WHEN CA.DateCompare >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -6, 0)
                 AND  CA.DateCompare <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -5, 0)
            THEN 1 ELSE 0 END
         ) AS "MNTH_5"
    , SUM(
            CASE WHEN CA.DateCompare >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -7, 0)
                 AND  CA.DateCompare <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -6, 0)
            THEN 1 ELSE 0 END
         ) AS "MNTH_6"
    , SUM(
            CASE WHEN CA.DateCompare >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -8, 0)
                 AND  CA.DateCompare <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -7, 0)
            THEN 1 ELSE 0 END
         ) AS "MNTH_7"
    , SUM(
            CASE WHEN CA.DateCompare >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -9, 0)
                 AND  CA.DateCompare <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -8, 0)
            THEN 1 ELSE 0 END
         ) AS "MNTH_8"
    , SUM(
            CASE WHEN CA.DateCompare >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -10, 0)
                 AND  CA.DateCompare <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -9, 0)
            THEN 1 ELSE 0 END
         ) AS "MNTH_9"
    , SUM(
            CASE WHEN CA.DateCompare >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -11, 0)
                 AND  CA.DateCompare <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -10, 0)
            THEN 1 ELSE 0 END
         ) AS "MNTH_10"
    , SUM(
            CASE WHEN CA.DateCompare >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -12, 0)
                 AND  CA.DateCompare <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -11, 0)
            THEN 1 ELSE 0 END
         ) AS "MNTH_11"

FROM ReferralCaseLoad
CROSS APPLY (
              SELECT ISNULL(DateDischarge,GETDATE())
            ) CA (DateCompare)
GROUP BY
        OrganisationName

;

Open in new window


I may be "one month off" when I defined those ranges but they can be adjusted to suit.

However please note that unlike that spreadsheet mock-up shown above, the column names remain static like these:
| ORGANISATIONNAME | MNTH_0 | MNTH_1 | MNTH_2 | MNTH_3 | MNTH_4 | MNTH_5 | MNTH_6 | MNTH_7 | MNTH_8 | MNTH_9 | MNTH_10 | MNTH_11 |
|------------------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|---------|---------|
|              ABC |      1 |      2 |      1 |      1 |      2 |      1 |      1 |      1 |      2 |      1 |       2 |       1 |

Open in new window


To achieve column headings that change each month would require more complex "dynamic sql"

This is the sample data I worked with. Perhaps you could provide something a little more realistic but in a similar reusable format?
CREATE TABLE ReferralCaseLoad
	([OrganisationName] varchar(30), [DateEvent] datetime, [DateDischarge] datetime)
;
	
INSERT INTO ReferralCaseLoad
	([OrganisationName], [DateEvent])
VALUES
	('ABC', '2014-02-25 00:00:00'),
	('ABC', '2014-03-21 00:00:00'),
	('ABC', '2014-04-16 00:00:00'),
	('ABC', '2014-05-06 00:00:00'),
	('ABC', '2014-05-31 00:00:00'),
	('ABC', '2014-06-27 00:00:00'),
	('ABC', '2014-07-19 00:00:00'),
	('ABC', '2014-08-15 00:00:00'),
	('ABC', '2014-09-01 00:00:00'),
	('ABC', '2014-09-22 00:00:00'),
	('ABC', '2014-10-19 00:00:00'),
	('ABC', '2014-11-13 00:00:00'),
	('ABC', '2014-11-30 00:00:00'),
	('ABC', '2014-12-25 00:00:00'),
	('ABC', '2015-01-18 00:00:00'),
	('ABC', '2015-02-11 00:00:00'),
	('ABC', '2015-03-04 00:00:00'),
	('ABC', '2015-03-30 00:00:00'),
	('ABC', '2015-04-25 00:00:00'),
	('ABC', '2015-05-19 00:00:00'),
	('ABC', '2015-06-10 00:00:00'),
	('ABC', '2015-06-28 00:00:00'),
	('ABC', '2015-07-20 00:00:00'),
	('ABC', '2015-08-15 00:00:00'),
	('ABC', '2015-09-07 00:00:00'),
	('ABC', '2015-09-26 00:00:00')
;

Open in new window

0
 

Author Comment

by:JohnAeris
ID: 40630416
@PortletPaul:
Great code! Performs better than mine too - faster with a nicer looking execution plan.

It appears I can't escape the searched case statement. Not too fussed with display names, can deal with that in reporting tools like Excel.

I add this code for the CURRENT MONTH following your logic:

,SUM(CASE WHEN CA.DateCompare >= DATEADD(D,1,EOMONTH(GetDate(),-1)) 
			AND CA.DateCompare < GETDATE()
			THEN 1 ELSE 0 END
	) AS [CurrentMonth]

Open in new window


If I read this right, the code checks for null discharge date, adds today's date if null, or uses discharge date. Then adds up 1 and 0 if searched case condition is met.

The missing piece: a check to see if the number of weeks between event data and discharge date is 12 + weeks in any given month.
0
 

Author Comment

by:JohnAeris
ID: 40630430
@Zberteoc:

WHERE  datediff(wk, DateEvent, isnull(DateDischarge, getdate()))>12

Open in new window


Your code above is great as it evaluates whether number of weeks is over 12 weeks.

Any idea how to replicate this for every single month period in the last 12 months, elegantly? that is, GETDATE() would need to change to the end of each month whilst evaluating 1 million rows.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40630605
I still don't understand how you define those months. What is a moth? In your example I see Feb 15, Mar15 and then Dec14... Confusing,.

What is suppose to be the first month and what the last month your report should show?
0
 

Author Comment

by:JohnAeris
ID: 40630630
@Zberteoc: Good question.

The report should calculate dynamically from TODAY. TODAY is 25th Feb 2016, so the first month of the report is Feb 2015. 2nd month = Jan 2015, 3rd month  = Dec 2014, 4th Month = Nov 2014 so on and so forth.

So, if today's date is 27th April 2015, then first month is APRIL 2015, followed by MARCH 2015, then FEB 2015 etc...
0
 

Author Comment

by:JohnAeris
ID: 40630660
Hi Experts:
So, I combined Zberteoc and PortletPaul's code below and added some 2012 T-SQL and a CTE. This works and creates the correct result!

Does anyone have a more elegant alternative solution to this? Bear in mind the the month headings are not important and can be changed with a reporting tool.


WITH cteCaseLoad AS

(
	SELECT 
		 OrganisationName
		,[DateEvent]
		,[DateDischarge]
		,CA.DateCompare -- This is the NEW DISCHARGE DATE taking in TODAY.

	FROM 
		ReferralCaseLoad  
			CROSS APPLY (SELECT ISNULL(ReferralIn.[DateDischarge],GETDATE()) AS DateCompare) AS CA

)

SELECT
	OrganisationName
	,SUM(
	CASE 
		WHEN (DateCompare >= DATEADD(D,1,EOMONTH(GetDate(),-1)) 
			AND DateCompare <= GETDATE()) 
			AND datediff(wk, DateEvent, DateCompare)>12
		THEN 1
	ELSE 0 END 
	) AS 'This Month'

	,SUM(
	CASE 
		WHEN (DateCompare >= DATEADD(dd, 1, EOMONTH(GetDate(),-2))
				AND  DateCompare <=  EOMONTH(GetDate(),-1))
				AND datediff(wk, DateEvent, EOMONTH(GetDate(),-1))>12
		THEN 1 
	ELSE 0 END 
		) AS 'Last Month'

	,SUM(
	CASE 
		WHEN (DateCompare >= DATEADD(dd, 1, EOMONTH(GetDate(),-3))
				AND  DateCompare <=  EOMONTH(GetDate(),-2))
				AND datediff(wk, DateEvent, EOMONTH(GetDate(),-2))>12
		THEN 1 
	ELSE 0 END 
		) AS '3 months ago'


	,
	SUM(
	CASE 
		WHEN (DateCompare >= DATEADD(dd, 1, EOMONTH(GetDate(),-4))
				AND  DateCompare <=  EOMONTH(GetDate(),-3))
				AND datediff(wk, DateEvent, EOMONTH(GetDate(),-3))>12
		THEN 1 
	ELSE 0 END 
		)	AS '4 months ago'


	,
	SUM(
	CASE 
		WHEN (DateCompare >= DATEADD(dd, 1, EOMONTH(GetDate(),-5))
				AND  DateCompare <=  EOMONTH(GetDate(),-4))
				AND datediff(wk, DateEvent, EOMONTH(GetDate(),-4))>12
		THEN 1 
	ELSE 0 END 
		)	AS '5 months ago'


	,
	SUM(
	CASE 
		WHEN (DateCompare >= DATEADD(dd, 1, EOMONTH(GetDate(),-6))
				AND  DateCompare <=  EOMONTH(GetDate(),-5))
				AND datediff(wk, DateEvent, EOMONTH(GetDate(),-5))>12
		THEN 1 
	ELSE 0 END 
		)	AS '6 months ago'

	,
	SUM(
	CASE 
		WHEN (DateCompare >= DATEADD(dd, 1, EOMONTH(GetDate(),-7))
				AND  DateCompare <=  EOMONTH(GetDate(),-6))
				AND datediff(wk, DateEvent, EOMONTH(GetDate(),-6))>12
		THEN 1 
	ELSE 0 END 
		)	AS '7 months ago'


	,
	SUM(
	CASE 
		WHEN (DateCompare >= DATEADD(dd, 1, EOMONTH(GetDate(),-8))
				AND  DateCompare <=  EOMONTH(GetDate(),-7))
				AND datediff(wk, DateEvent, EOMONTH(GetDate(),-7))>12
		THEN 1 
	ELSE 0 END 
		)	AS '8 months ago'


	,
	SUM(
	CASE 
		WHEN (DateCompare >= DATEADD(dd, 1, EOMONTH(GetDate(),-9))
				AND  DateCompare <=  EOMONTH(GetDate(),-8))
				AND datediff(wk, DateEvent, EOMONTH(GetDate(),-8))>12
		THEN 1 
	ELSE 0 END 
		)	AS '9 months ago'

	,
	SUM(
	CASE 
		WHEN (DateCompare >= DATEADD(dd, 1, EOMONTH(GetDate(),-9))
				AND  DateCompare <=  EOMONTH(GetDate(),-8))
				AND datediff(wk, DateEvent, EOMONTH(GetDate(),-8))>12
		THEN 1 
	ELSE 0 END 
		)	AS '10 months ago'

	,
	SUM(
	CASE 
		WHEN (DateCompare >= DATEADD(dd, 1, EOMONTH(GetDate(),-10))
				AND  DateCompare <=  EOMONTH(GetDate(),-9))
				AND datediff(wk, DateEvent, EOMONTH(GetDate(),-9))>12
		THEN 1 
	ELSE 0 END 
		)	AS '11 months ago'


	,
	SUM(
	CASE 
		WHEN (DateCompare >= DATEADD(dd, 1, EOMONTH(GetDate(),-11))
				AND  DateCompare <=  EOMONTH(GetDate(),-10))
				AND datediff(wk, DateEvent, EOMONTH(GetDate(),-10))>12
		THEN 1 
	ELSE 0 END 
		)	AS '12 months ago'


FROM cteCaseLoad

GROUP BY 

OrganisationName

Open in new window

0
 

Author Comment

by:JohnAeris
ID: 40630732
PS Script with random test data attached (Generated in SQL Server 2012).

ReferralCaseLoad-Test-Script.txt
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40631008
Here is a script that uses the dynamic SQL in order to accomodate the changing column heders for months:
declare
	@sql varchar(max)=''
;WITH 
E1(N) AS 
(		--10E+1 or 10 rows
		SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
		SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
		SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),                         
E2(N) AS 
(	--10E+2 or 100 rows	
	SELECT 1 FROM E1 a, E1 b
),
E4(N) AS 
(	--10E+4 or 10,000 rows max
	SELECT 1 FROM E2 a, E2 b
),
nums as
(			
	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as N FROM E4
),
mnths as
(
	SELECT N,dateadd(mm,-N+1,getdate()) as mnth FROM nums where N<=12
),
OVR as
(
select 
	OrganisationName,
	convert(varchar(7), isnull(DateDischarge,mnth),121) as YearMonth,
	sum(case when datediff(wk, DateEvent, isnull(DateDischarge, mnth))>12 then 1 else null end) as NurseCaseload12WeekBreach
from 
	#tmp t
	cross join mnths m
group by 
	OrganisationName,
	convert(varchar(7), isnull(DateDischarge,mnth),121)
)
select 
	@sql=@sql+cast(NurseCaseload12WeekBreach as varchar)+' as ['+YearMonth+'],'
from 
	OVR
order by 
	YearMonth
select @sql='SELECT '+replace(@sql+'@#$',',@#$','')
exec(@sql)

Open in new window


The top 3 queries in the CTE are only to produce numbers from 1 to 10000 which then are used to generate teh last 12 months(N<=12) and I would recommend to be put in a functions that could be used in other similar situations.

Her would be the function:
CREATE FUNCTION [dbo].[fnTally]()
RETURNS TABLE
AS
/*******************************************************************************\
Function	: fnTally

Purpose		: returns a set with numbers from 1 to 10,000 
			  to be used in parsing and sequential data generation whithout loop
			  
Parameters	: no parameters

Invoke		:
	
		select * from [dbo].[fnTally]()
		select N from [dbo].[fnTally]()
		select substring('abcdef',N,1) as chr from [dbo].[fnTally]() where N<len('abcdef') -- parsing a string
		select dateadd(dd, N, '2007-01-01') as dte from [dbo].[fnTally]() --gets dates for about 30 years

\*******************************************************************************/
RETURN
	WITH 
	E1(N) AS 
	( --10E+1 or 10 rows
		 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
		 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
		 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
	),                         
   E2(N) AS 
   ( --10E+2 or 100 rows	
		SELECT 1 FROM E1 a, E1 b
	),
   E4(N) AS 
   ( --10E+4 or 10,000 rows max
		SELECT 1 FROM E2 a, E2 b
	)
			 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as N FROM E4
	;

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40631020
This is what the code becomes using the function:
declare
	@sql varchar(max)=''
;WITH mnths as
(
	select N,dateadd(mm,-N+1,getdate()) as mnth from [dbo].[fnTally]() where N<=12
),
OVR as
(
select 
	OrganisationName,
	convert(varchar(7), isnull(DateDischarge,mnth),121) as YearMonth,
	sum(case when datediff(wk, DateEvent, isnull(DateDischarge, mnth))>12 then 1 else null end) as NurseCaseload12WeekBreach
from 
	#tmp t
	cross join mnths m
group by 
	OrganisationName,
	convert(varchar(7), isnull(DateDischarge,mnth),121)
)
select 
	@sql=@sql+cast(NurseCaseload12WeekBreach as varchar)+' as ['+YearMonth+'],'
from 
	OVR
order by 
	YearMonth
select @sql='SELECT '+replace(@sql+'@#$',',@#$','')
exec(@sql)

Open in new window


You can't get more simpler and more elegant than that. The only problem is if you will get more that 1 OrganisationName. But we can deal with that too.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40631043
Here is the updated code using the function that will filter by Organisationname:
declare
	@OrganisationName varchar(100)='Nursing',
	@sql varchar(max)=''
;WITH mnths as
(
	select N,dateadd(mm,-N+1,getdate()) as mnth from [dbo].[fnTally]() where N<=12
),
OVR as
(
select 
	OrganisationName,
	convert(varchar(7), isnull(DateDischarge,mnth),121) as YearMonth,
	sum(case when datediff(wk, DateEvent, isnull(DateDischarge, mnth))>12 then 1 else null end) as NurseCaseload12WeekBreach
from 
	#tmp t
	cross join mnths m
group by 
	OrganisationName,
	convert(varchar(7), isnull(DateDischarge,mnth),121)
)
select 
	@sql=@sql+cast(NurseCaseload12WeekBreach as varchar)+' as ['+YearMonth+'],'
from 
	OVR
where
	OrganisationName=@OrganisationName
order by 
	YearMonth
select @sql='SELECT '''+@OrganisationName+''' as OrganisationName, '+replace(@sql+'@#$',',@#$','')
exec(@sql)

Open in new window

0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 40631058
Ok Here is last version and with the result from the data provided:
declare
	@OrganisationName varchar(100)='Nursing',
	@sql varchar(max)=''
;WITH mnths as
(
	select N,dateadd(mm,-N+1,getdate()) as mnth from [dbo].[fnTally]() where N<=12
),
OVR as
(
select 
	OrganisationName,
	convert(varchar(7), isnull(DateDischarge,mnth),121) as YearMonth,
	sum(case when datediff(wk, DateEvent, isnull(DateDischarge, mnth))>12 then 1 else 0 end) as NurseCaseload12WeekBreach
from 
	[dbo].[ReferralCaseLoad] t
	cross join mnths m
group by 
	OrganisationName,
	convert(varchar(7), isnull(DateDischarge,mnth),121)
)
select 
	@sql=@sql+cast(NurseCaseload12WeekBreach as varchar)+' as ['+YearMonth+'],'
from 
	OVR
where
	OrganisationName=@OrganisationName
order by 
	YearMonth
select @sql='SELECT '''+@OrganisationName+''' as OrganisationName, '+replace(@sql+'@#$',',@#$','')
exec(@sql)
GO

-- RESULT:
OrganisationName 2013-09     2013-11     2013-12     2014-01     2014-02     2014-03     2014-04     2014-05     2014-06     2014-07     2014-08     2014-09     2014-10     2014-11     2014-12     2015-01     2015-02
---------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Nursing          108         0           0           0           36          449         152         124         138         177         145         174         199         200         170         244         222

Open in new window

The performance in milliseconds:

Time Statistics                  
  Client processing time      4            4.0000
  Total execution time      27            27.0000
  Wait time on server replies      23            23.0000
0
 

Author Comment

by:JohnAeris
ID: 40633408
(@Zberteoc: back-to-back meetings, will check code soon and get back to you. Thanks for the effort! :))
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now