I need to compare the number of incidents in a year - where the year is a rolling year, not a calendar year. eg. for the May 2015 report (presented to the board in June), the total should count all incidents from 1 June 2014 to 31 May 2015; for the June 2015 report (presented to the board in July), should count all incidents from 1 July 2014 to 30 June 2015.
I also need to compare this to the previous 2 12 month periods, in the same rolling 12 months format.
I also need this by the category of incident.
So I have written this script, but there must be some major errors here, as you will see from the results table below.
use [exoMashTrust-test] declare @startdate2013 datetime ,@enddate2013 datetime ,@startdate2014 datetime ,@enddate2014 datetime ,@startdate2015 datetime ,@enddate2015 datetime --Set these for the 12 months ending the month of the report set @startdate2015= '20140601 00:00:00.000' set @enddate2015='20150531 23:23:59.999' --Auto calculated set @StartDate2013=dateadd(y,-2,@StartDate2015); set @enddate2013=dateadd(y,-2,@enddate2015); set @StartDate2014=dateadd(y,-1,@StartDate2015); set @enddate2014=dateadd(y,-1,@enddate2015); --Restraint Incidents by Category --This is for rolling 12 months select pio.OPTION_NAME as [Type of Incident] ,(select count(pati.incident_date) where pati.incident_date between @startdate2013 and @enddate2013) as [2013 Incidents] ,(select count(pati.incident_date) where pati.incident_date between @startdate2014 and @enddate2014) as [2014 Incidents] ,(select count(pati.incident_date) where pati.incident_date between @startdate2015 and @enddate2015) as [2015 Incidents] from PATIENT_INCIDENTS as pati join PATIENT_INC_OPTIONS as pio on pio.SEQNO=pati.OPTION_ID where pati.PARENT_ID=9 group by pio.OPTION_NAME ,pati.incident_date ORDER BY pio.OPTION_NAME ;
The results are doubling up the type of incident and putting NULL's in, I would have thought there should be zero's.
Hopefully someone can tell me where I have gone wrong.