NB SQL Server 2012 database, so can apply new Windowing Functions, if applicable.
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 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!