CODEWITHSUNDEEP
sql-serverreporting-services
Paul Farr
Paul Farr

Reputation: 109

Reporting on counts of dates for each month given only StartDate and EndDate rather than EventDate

In an SSRS reporting project, I need to data on the trend results of inspections of establishments over the past 12 months (rolling). Unlike a standard BI reporting application where grouping could be done on a fixed field such as ‘InspectionDate’ , the results of every inspection are valid between ‘StartDate’ and ‘EndDate’. When a new inspection is performed, the previous record for that establishment gets an end date and a new record is created with a new start date and an end date of null. I need to report aggregated data for the status it would have been for each month.

So currently, the approach I am taking to produce the data for this is as follows:-

1) Replicate live data to reporting server and add 12 new fields P0 to P11 to flag which months the record was valid for over the previous 12 months.

2) Run 12 update queries in the following form...

UPDATE tblDistributionReports
SET P1 = 1
WHERE DATEDIFF(MONTH,GETDATE(),StartDate) <= -1  
AND (ENDDATE IS NULL OR DATEDIFF(MONTH,GETDATE(),EndDate) >= -1 )
Etc .....

3) UNPIVOT the table to give me one row for every month an inspection was valid

4) Re - PIVOT the results to give me aggregated counts and averages of scores for each month

So – This works though is a little clunky. Is there a more graceful and efficient way of doing this that I have not though of?

Many thanks

Upvotes: 1

Views: 627

Answers (1)

Jamie F
Jamie F

Reputation: 23809

I would clean this up with a couple of objectives:

  • Run just one query to return all data.

  • Return all the data in one long table, not pivoted by months. Let SSRS do the pivoting.

I.E. RESULTS FROM QUERY:

MonthStart     MonthEnd         InspectionID    ThingBeingMeasured
Jan 1, 2011    Feb 1, 2011      1                14
Feb 1, 2011    March 1, 2011    1                14
March 1, 2011  April 1, 2011    1                14
March 1, 2011  April 1, 2011    2                9
April 1, 2011  May 1, 2011      2                9

The query below should give you a decent framework for the query side of things:

;
WITH MonthsCTE AS (
SELECT
   DATEADD(
      year,
      -1,
      DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
   as MonthStart,
   DATEADD(
      year,
      -1,
      DATEADD(month, DATEDIFF(month, 0, GETDATE()) +1, 0))
   as MonthEnd
UNION ALL
SELECT
   DATEADD(Month, 1, MonthStart),
   DATEADD(MONTH, 1, MonthEnd)
FROM
   MonthsCTE
WHERE MonthEnd < GETDATE())

SELECT
   MonthsCTE.MonthStart,
   MonthsCTE.MonthEnd,
   InspectionID,
   SUM(NumberOfViolations) AS SumOfViolations
FROM Inspections
INNER JOIN
   MonthsCTE
ON COALESCE(Inspections.EndDate, GETDATE() ) >= MonthsCTE.MonthStart
   AND Inspections.StartDate < MonthsCTE.MonthEnd
GROUP BY
   MonthsCTE.MonthStart,
   MonthsCTE.MonthEnd,
   InspectionID

First I create a CTE with the month start dates and end dates for the past year. Then I join that to the inspections table, but in the condition for the join, I use the appropriate inequality operators to join one inspection row to multiple rows in the months table.

Then, in SSRS, you should use a matrix data element, not a table. The column group will be Month Start, the row group = InspectionID, and place SUM(ThingBeingMeasured) into the cells.

Upvotes: 2

Related Questions