Solved

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

Posted on 2015-02-24
29 Views
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?

JohnAeris
0
Question by:JohnAeris
• 15
• 13
• +1

LVL 26

Expert Comment

ID: 40628271
Define "in breach".
0

Author Comment

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

LVL 26

Expert Comment

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

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

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

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

Author Comment

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

ID: 40628436
See above.
0

Author Comment

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

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

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:

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

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

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

)

SELECT
OrganisationName,
YearMonth,

FROM cteSubSet
WHERE DischargeWeeks > 12

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

LVL 69

Expert Comment

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

ID: 40628827
Scott & Zberteoc: apologies. Should have posted the code first.
0

LVL 26

Expert Comment

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

LVL 26

Expert Comment

ID: 40628996
Nevermind.
0

LVL 26

Expert Comment

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

LVL 26

Expert Comment

ID: 40629021
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

LVL 48

Expert Comment

ID: 40629773
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 Comment

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.

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

ID: 40630430
@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

LVL 26

Expert Comment

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

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

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
CROSS APPLY (SELECT ISNULL(ReferralIn.[DateDischarge],GETDATE()) AS DateCompare) AS CA

)

SELECT
OrganisationName
,SUM(
CASE
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 Comment

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

0

LVL 26

Expert Comment

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

LVL 26

Expert Comment

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

LVL 26

Expert Comment

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
from
OVR
where
OrganisationName=@OrganisationName
order by
YearMonth
select @sql='SELECT '''+@OrganisationName+''' as OrganisationName, '+replace(@sql+'@#\$',',@#\$','')
exec(@sql)
``````
0

LVL 26

Accepted Solution

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
cross join mnths m
group by
OrganisationName,
convert(varchar(7), isnull(DateDischarge,mnth),121)
)
select
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 Comment

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE sâ€¦