• Status: Solved
• Priority: Medium
• Security: Public
• Views: 49

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

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

## 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
JohnAeris
• 15
• 13
• +1
1 Solution

Commented:
Define "in breach".
0

Author Commented:
In breach = an exception. All patients should be seen within 12 weeks.
0

Commented:
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 Commented:
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

Commented:
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

Commented:
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
``````
I have no idea how did you get your numbers.
0

Author Commented:
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

Commented:
See above.
0

Author Commented:
@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

Commented:
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 Commented:
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:

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 Commented:
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

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

)

SELECT
OrganisationName,
YearMonth,

FROM cteSubSet
WHERE DischargeWeeks > 12

GROUP BY
OrganisationName,
YearMonth,
YearMonthLabel;
GO
``````
0

Senior DBACommented:
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 Commented:
Scott & Zberteoc: apologies. Should have posted the code first.
0

Commented:
Can you tell me how did you get the number 64 for the first case?
0

Commented:
Nevermind.
0

Commented:
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)
``````
0

Commented:
Actually probably this si better:
``````select
OrganisationName,
convert(varchar(7), isnull(DateDischarge,getdate()),121) as YearMonth,
from
#tmp t
where
datediff(wk, DateEvent, isnull(DateDischarge, getdate()))>12
group by
OrganisationName,
convert(varchar(7), isnull(DateDischarge,getdate()),121)
``````
0

freelancerCommented:
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"

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

;
``````

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

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)
;

([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')
;
``````
0

Author Commented:
@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]
``````

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 Commented:
@Zberteoc:

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

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

Commented:
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 Commented:
@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 Commented:
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
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'

GROUP BY

OrganisationName
``````
0

Author Commented:
PS Script with random test data attached (Generated in SQL Server 2012).

0

Commented:
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)
``````

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
;
``````
0

Commented:
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)
``````

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

Commented:
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)
``````
0

Commented:
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
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
``````
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 Commented:
(@Zberteoc: back-to-back meetings, will check code soon and get back to you. Thanks for the effort! :))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 15
• 13
• +1
Tackle projects and never again get stuck behind a technical roadblock.