SQL Query to show gaps between multiple date ranges for REPORT BUILDER 3.0

Question

I have a report created in Report Builder 3.0 using SQL Server Management Studio 2008 2 Simple charts: 1. Showing DistinctCount of all visits to a walk in clinic by month/day for the last 30 days; and, 2. Showing the same but counting only those visits with a police escort

The problem I am having is that there is not always a visit every day with a police escort - so some of the days do not have NULL values, but are missing altogether!

I have found several solutions to this issue using SSRS but they are too complicated for me to switch into Report Builder (I am a newb at both). Can someone walk me through how I would fix this in Report Builder?

NOTE: I am unable to add or change tables (I have read-only access), so those options are out. The instructions showing how to add a temp table sound interesting, but too complicated for me as is - Here is the code for the two tables (I couldn't figure out how to do one dataset for both - when I restricted the 2nd tablix to just police, it screwed up the 1st tablix).

Please keep in mind that I am NEW to this and I use Report Builder not SQL. Thanks.

ServiceByDatesHrly

SELECT
    ClientChartPartition.ClientChartPartitionID AS [ClientChartPartition ClientChartPartitionID]
    ,ClientChartPartition.ClientChartID AS [ClientChartPartition ClientChartID]
    ,ClientChart.ClientChartID AS [ClientChart ClientChartID]
    ,VisitPlan.VisitPlanID
    ,VisitPlan.ClientChartPartitionID AS [VisitPlan ClientChartPartitionID]
    ,VisitPlan.VisitDate
    ,VisitPlan.ServiceTypeDesc
    ,Name.ParticipantID
    ,Name.LastName 
FROM
    ClientChartPartition 
    INNER JOIN
        ClientChart 
        ON ClientChartPartition.ClientChartID = ClientChart.ClientChartID 
    INNER JOIN
        VisitPlan 
        ON ClientChartPartition.ClientChartPartitionID = VisitPlan.ClientChartPartitionID 
    INNER JOIN
        Name 
        ON Name.ParticipantID = ClientChartPartition.ClientChartPartitionID 
    WHERE
    Name.LastName NOT LIKE N'TestClient%'

PoliceEscort

SELECT
    VisitPlan.VisitPlanID
    ,VisitPlan.ClientChartPartitionID
    ,VisitPlan.VisitDate
    ,VisitPlan.ServiceTypeDesc
    ,FormRelation.ParentRelationID
    ,FormRelation.ChildRelationID
    ,Form.FormID
    ,Field.ParentID
    ,Field.FieldIndexCode
    ,Field.ResultXML
    ,Name.ParticipantID
    ,Name.LastName 
FROM
    VisitPlan 
    INNER JOIN
        FormRelation 
        ON VisitPlan.VisitPlanID = FormRelation.ParentRelationID 
    INNER JOIN
        Form 
        ON FormRelation.ChildRelationID = Form.FormID 
    INNER JOIN
        Field 
        ON Form.FormID = Field.ParentID 
    INNER JOIN
        Name 
        ON VisitPlan.ClientChartPartitionID = Name.ParticipantID 
WHERE
    VisitPlan.ServiceTypeDesc = N'MAPP_REFDATA_VISIT_PLAN_TYPE_WALK_IN_SERVICE' 
    AND Name.LastName NOT LIKE N'TestClient' 
    AND Form.FormTemplateID = N'ab395124-679a-4e7c-b98d-d9a9c11cb2a3'  --Mental Health and Physical Health Screen
    AND Field.FieldIndexCode = N'CRC_0070a' -- Mental Health and Physical Health Screen
    AND Field.ResultXML LIKE N'%<value>2</value>%'  -- Police

Does your system maintain one table like 'calendar' which define one day is one holiday, work day etc? — Sphinx
You can do this easily if you have access to a date table or create a table with dates.. cross join your results to this table and you should be able to fill up the empty dates with 0s in your dataset.. Try and use SSRS only for presenting the data.. not manipulating it.. leave that to the dataset. — Harry
Unfortunately, I am not able to add anything - it is a read-only database (for me). To my knowledge there is not a calendar table in the system. I will ask my sys admin but if they answer the way they usually do it will be 'we don't support that..." Fussimist — Fussimist

Alan Schofield Alan Schofield · Accepted Answer · 2018-02-21T00:09:42

You will need some kind of Date table, then you simply LEFT JOIN you existing data to it. The best way is to create a permanent date table but if this is not possible, then you can create one temporarily in your SSRS dataset.

Assuming your current dataset query look something like

SELECT VisitDate, COUNT(*) AS Vists FROM mytable GROUP BY VisitDate

Then we can modify it like this.

WITH tmpDates AS (
  SELECT CAST('2018-01-21' AS DATETIME) AS dt
  UNION ALL
  SELECT DATEADD(dd, 1, dt)
    FROM tmpDates td
   WHERE DATEADD(dd, 1, dt) <= CAST('2018-02-20' AS DATETIME)
  )
SELECT td.dt as VisitDate, COUNT(*) as Visits
  FROM tmpDates td
  LEFT JOIN mytable m on td.dt = m.VisitDate
GROUP BY VisitDate

Obviously you can pass the dates in as parameters.

Basically what we do here is create a CTE that just UNIONS a bunch of dates, each one a day later than the previous. It's not very efficient but it will be fine for small date ranges like yours. Then we are selecting all those records and LEFT joining to your original table by date then grouping/counting by date. If no data exists in your original table for a particular date then you will have a row returned with the date and 0 count.

SQL Query to show gaps between multiple date ranges for REPORT BUILDER 3.0

ServiceByDatesHrly

PoliceEscort

1 Answers