Using a customised date column

Transact-SQL
1

Hi

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.

2

Try this:

select pio.OPTION_NAME as [Type of Incident]
      ,sum(case
              when pati.incident_date between @startdate2013 and @enddate2013
              then 1
              else 0
           end
          ) as [2013 Incidents]
      ,sum(case
              when pati.incident_date between @startdate2014 and @enddate2014
              then 1
              else 0
           end
          ) as [2014 Incidents]
      ,sum(case
              when pati.incident_date between @startdate2015 and @enddate2015
              then 1
              else 0
           end
          ) as [2015 Incidents]
  from PATIENT_INCIDENTS as pati
       inner join PATIENT_INC_OPTIONS as pio
               on pio.SEQNO=pati.OPTION_ID
 where pati.PARENT_ID=9
 group by pio.OPTION_NAME
 order by pio.OPTION_NAME
;
3 
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'
--Auto calculated
--force start date to first of month at midnight (00:00:00.000)
set @startdate2015 = dateadd(month, datediff(month, 0, @startdate2015), 0)
set @enddate2015 = dateadd(year, 1, @startdate2015)
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]
	,SUM(case when pati.incident_date >= @startdate2013 and pati.incident_date < @enddate2013 then 1 else 0 end) 
	    as [2013 Incidents]
	,SUM(case when pati.incident_date >= @startdate2014 and pati.incident_date < @enddate2014 then 1 else 0 end) 
	    as [2014 Incidents]
	,SUM(case when pati.incident_date >= @startdate2015 and pati.incident_date < @enddate2015 then 1 else 0 end) 
	    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
ORDER BY 
pio.OPTION_NAME
;
4

Thanks... I get the same results from each rewritten query, but I am a bit concerned that the start and end dates are wrong.

Using my original date setting:

--Set these for the 12 months starting the month of the report
SET @startdate2015 = '20140601 00:00:00.000'
SET @enddate2015 = '20150531 23:59:59.999'
--Auto calculate other dates
set @StartDate2013=dateadd(y,-2,@StartDate2015);
set @enddate2013=dateadd(y,-2,@enddate2015);
set @StartDate2014=dateadd(y,-1,@StartDate2015);
set @enddate2014=dateadd(y,-1,@enddate2015);--Test of Dates
--Test of Dates
SELECT @startdate2013 AS Start2013
	,@enddate2013 AS End2013
	,@startdate2014 AS Start2014
	,@enddate2014 AS End2014
	,@startdate2015 AS Start2015
	,@enddate2015 AS End2015

I get these results:

Which are wrong, but I can't figure out why.

When I use ScottPletcher's date format for setting dates, same thing happens.

Whey would this be and how can I fix it? I need it to start on 1 June and end 31 May for each year. Do I need to manually put in each date?

5

Right, sorry. Those results need put into a temp table or a derived table and then query them to get the final totals.

6 
set @StartDate2013=dateadd(yy,-2,@StartDate2015);
set @enddate2013=dateadd(yy,-2,@enddate2015);
set @StartDate2014=dateadd(yy,-1,@StartDate2015);
set @enddate2014=dateadd(yy,-1,@enddate2015);

when summing it would work, as a particular date when incident_date is equal or greater than startdate and less than enddate

1 Like
7

Thanks guys, I just twigged onto bitsmed's use of >= and < and the resultant logic. I am so used to using BETWEEN dates.

Using "yy" instead of "y" in the DATEADD expression worked a treat too.