Handling non existent values in sql query expression for ssrs chart

Question

I am using the following query in an ssrs line chart. It counts how many orders are recorded each month based on each order date.

My problem is that when a month has no orders, rather than saying zero or null it removes the row for that month all together. I would prefer for it to count it as zero but null would be ok too.

Basically, I want to always have twelve rows whether they contain information or not.

How can I fix this? Is there an expression I can use or something? Or am I missing something completely obvious?

SELECT
MONTH(Ord.OrdDate) AS 'MONTH',
COUNT(CASE WHEN @Worker_ID1 IS NULL OR @Worker_ID1 = Worker.ID THEN 1 END) AS 'Worker1',
COUNT(CASE WHEN @Worker_ID2 IS NULL OR @Worker_ID2 = Worker.ID THEN 1 END) AS 'Worker2',
COUNT(CASE WHEN @Worker_ID3 IS NULL OR @Worker_ID3 = Worker.ID THEN 1 END) AS 'Worker3',
COUNT(CASE WHEN @Worker_ID4 IS NULL OR @Worker_ID4 = Worker.ID THEN 1 END) AS 'Worker4',
COUNT(CASE WHEN @Worker_ID5 IS NULL OR @Worker_ID5 = Worker.ID THEN 1 END) AS 'Worker5'

FROM Ord
JOIN Prod ON Ord.Prod_ID = Prod.ID
JOIN ProdType ON Prod.ProdType_ID = ProdType.ID
JOIN Grouping ON Ord.Grouping_ID = Grouping.ID
JOIN Worker ON Grouping.Worker_ID = Worker.ID

WHERE @Year = YEAR(Ord.OrdDate)
AND (@ProdType_ID IS NULL OR @ProdType_ID = ProdType.ID)

GROUP BY MONTH(Ord.OrdDate)

This isn't strictly an answer, so I use comment. You might consider grouping by MONTH(Ord.OrdDate) and Worker.ID and then using the pivot table provided by ssrs-2008 (a matrix report if I correctly recall.) You can change your predicate to say AND Worker.ID IN (\@Worker_ID1, \@Worker_ID2, \@Worker_ID3, \@Worker_ID4, \@Worker_ID5). If any of those are NULL, they are implicitly excluded from the IN clause. — Griffin, Mar 13 '12 at 21:19

Colin · Answer 1 · 2012-03-14T16:33:47.197

3

As the above answers mentioned, you will need an outer join and some kind of calendar table. This is untested, but I think will work for you:

with dateCTE as
(
     select cast('2012-01-01' as datetime) dateValue -- start date
     union all
     select DateAdd(mm, 1, dateValue)
     from    dateCTE   
     where   dateValue < '2012-12-30' -- end date
 )
SELECT
MONTH(dateCTE.dateValue) AS 'MONTH',
COUNT(CASE WHEN @Worker_ID1 IS NULL OR @Worker_ID1 = Worker.ID THEN 1 END) AS 'Worker1',
COUNT(CASE WHEN @Worker_ID2 IS NULL OR @Worker_ID2 = Worker.ID THEN 1 END) AS 'Worker2',
COUNT(CASE WHEN @Worker_ID3 IS NULL OR @Worker_ID3 = Worker.ID THEN 1 END) AS 'Worker3',
COUNT(CASE WHEN @Worker_ID4 IS NULL OR @Worker_ID4 = Worker.ID THEN 1 END) AS 'Worker4',
COUNT(CASE WHEN @Worker_ID5 IS NULL OR @Worker_ID5 = Worker.ID THEN 1 END) AS 'Worker5'

FROM dateCTE
LEFT JOIN Ord on MONTH(dateCTE.datevalue) = MONTH(Ord.OrdDate)
JOIN Prod ON Ord.Prod_ID = Prod.ID
JOIN ProdType ON Prod.ProdType_ID = ProdType.ID
JOIN Groupord ON Ord.Groupord_ID = Groupord.ID
JOIN Worker ON Groupord.Worker_ID = Worker.ID

WHERE (@Year = YEAR(Ord.OrdDate) or ORD.prod_id is null)
AND (@DrugType_ID IS NULL OR @ProdType_ID = ProdType.ID)

GROUP BY MONTH(dateCTE.dateValue)
OPTION  (MAXRECURSION 0)

edited Mar 14 '12 at 16:33

answered Mar 13 '12 at 21:30

Colin

846
7
16

+1 CTEs are great for generating a date or number table on the fly. – Jamie F Mar 13 '12 at 21:57
@JamieF Too bad this is hard-coded to a specific year, that kind of takes away from it a bit. There are a few other issues - for example, the date format fails if, say, `SET LANGUAGE FRENCH`, and the CTE returns an extra month (run the CTE on its own, it has 13 months). – Aaron Bertrand Mar 13 '12 at 22:07
This was really just to give the idea. OP also appears to filter results for only one year, and since the group is by month, as long as months 1-12 are in the CTE, you will still get the same grouping. – Colin Mar 13 '12 at 22:23
That is exactly my problem. The table dosn't contain information from every month. I need something that will group by 12 months even if all of them are null. – NebDaMin Mar 14 '12 at 13:55

score 0 · Accepted Answer · answered Apr 05 '12 at 20:25

Got it working by removing the where clause and filtering in the count aggregate.

SELECT
MONTH(Ord.OrdDate) AS 'MONTH',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) AND Worker.ID = @Worker_ID1 AND @ProdType_ID = ProdType.ID THEN 1 END) AS 'Worker1',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) AND Worker.ID = @Worker_ID2 AND @ProdType_ID = ProdType.ID THEN 1 END) AS 'Worker2',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) AND Worker.ID = @Worker_ID3 AND @ProdType_ID = ProdType.ID THEN 1 END) AS 'Worker3',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) AND Worker.ID = @Worker_ID4 AND @ProdType_ID = ProdType.ID THEN 1 END) AS 'Worker4',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) AND Worker.ID = @Worker_ID5 AND @ProdType_ID = ProdType.ID THEN 1 END) AS 'Worker5'

FROM Ord
JOIN Grouping ON Ord.Grouping_ID = Grouping.ID
JOIN Worker ON Grouping.Worker_ID = Worker.ID
JOIN Prod ON Ord.Prod_ID = Prod.ID
JOIN ProdType ON Prod.ProdType_ID = ProdType.ID 

GROUP BY MONTH(Ord.OrdDate)

score 0 · Answer 3 · edited Jun 23 '13 at 14:05

select a.mon,b.* from
(
select 1 as mon union select 2 as mon union select 3 as mon union select 4 as mon union 
select 5 as mon union select 6 as mon union select 7 as mon union select 8 as mon union 
select 9 as mon union select 10 as mon union select 11 as mon union  select 12 as mon 
) a
left outer join
(
   your existing query here
) b
on a.mon=b.MONTH(OrdDate)
go

Just copy and paste your query in the place holder and go. You will always get 12 rows only. And there is no need to create any tables too.

score 0 · Answer 4 · answered Mar 13 '12 at 21:23

0

You need a table containing months in order to make this work (or you can use a stored procedure or probably common table expression).

SELECT Months.Month, COUNT(Orders.OrderID)
FROM
 Months
LEFT OUTER JOIN
 Orders
ON
 MONTH(Orders.OrderDate) = Months.Month

Would ensure you got:

Month, Count
1, 1
2, 1
3, 2
4, NULL
etc

answered Mar 13 '12 at 21:23

Lee Tickett

5,847
8
31
55

I know that this would work. However we are not authorized to create new tables or stored procedures. Everything has to be within the query. – NebDaMin Mar 13 '12 at 21:29
So `JOIN (SELECT Month = 1 UNION ALL SELECT 2 UNION ALL SELECT 3...) AS Months`? – Aaron Bertrand Mar 13 '12 at 21:35
Correct. But that needs to be your "MASTER" table... So start with that then left join your other tables – Lee Tickett Mar 13 '12 at 21:39
I have tried using unions in this query but when i add all the needed funtionality the query is masssive. administration hates huge queries. I know, I don't have very many options do I? – NebDaMin Mar 14 '12 at 14:55

score 0 · Answer 5 · answered Mar 13 '12 at 21:51

;WITH m(m) AS ( SELECT TOP 12 ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.objects )
SELECT [MONTH] = m.m,
  Worker1 = COUNT(CASE WHEN COALESCE(@Worker_ID1, w.ID) = w.ID THEN 1 END),
  Worker2 = COUNT(CASE WHEN COALESCE(@Worker_ID2, w.ID) = w.ID THEN 1 END),
  Worker3 = COUNT(CASE WHEN COALESCE(@Worker_ID3, w.ID) = w.ID THEN 1 END),
  Worker4 = COUNT(CASE WHEN COALESCE(@Worker_ID4, w.ID) = w.ID THEN 1 END),
  Worker5 = COUNT(CASE WHEN COALESCE(@Worker_ID5, w.ID) = w.ID THEN 1 END)
FROM m
LEFT OUTER JOIN dbo.Ord AS o
ON o.OrdDate >= DATEADD(MONTH, m.m-1, RTRIM(@Year)+'0101')
AND o.OrdDate < DATEADD(MONTH, m.m, RTRIM(@Year+'0101')
INNER JOIN dbo.Prod     AS p  ON o.Prod_ID     = p.ID
INNER JOIN dbo.ProdType AS pt ON p.ProdType_ID = pt.ID
INNER JOIN dbo.Grouping AS g  ON o.Grouping_ID = g.ID
INNER JOIN dbo.Worker   AS w  ON g.Worker_ID   = w.ID
WHERE (@DrugType_ID IS NULL OR pt.ID = @ProdType_ID)
GROUP BY m.m
ORDER BY m.m;

score -1 · Answer 6 · edited Mar 13 '12 at 22:38

that behavior is well defined by SQL construct "Inner join". use left join (or right join, depending on which is correct side) to retrieve null values when the join condition is not satisfied as below (not tested)

SELECT
MONTH(Ord.OrdDate) AS 'MONTH',
COUNT(CASE WHEN @Worker_ID1 IS NULL OR @Worker_ID1 = Worker.ID THEN 1 END) AS 'Worker1',
COUNT(CASE WHEN @Worker_ID2 IS NULL OR @Worker_ID2 = Worker.ID THEN 1 END) AS 'Worker2',
COUNT(CASE WHEN @Worker_ID3 IS NULL OR @Worker_ID3 = Worker.ID THEN 1 END) AS 'Worker3',
COUNT(CASE WHEN @Worker_ID4 IS NULL OR @Worker_ID4 = Worker.ID THEN 1 END) AS 'Worker4',
COUNT(CASE WHEN @Worker_ID5 IS NULL OR @Worker_ID5 = Worker.ID THEN 1 END) AS 'Worker5'

FROM Prod 
LEFT JOIN ORD ON Ord.Prod_ID = Prod.ID
JOIN ProdType ON Prod.ProdType_ID = ProdType.ID
JOIN Grouping ON Ord.Grouping_ID = Groupord.ID
JOIN Worker ON Grouping.Worker_ID = Worker.ID

WHERE ((Ord.OrdDate is not null and @Year = YEAR(Ord.OrdDate)) or ORD.prod_id is null)
AND (@ProdType_ID IS NULL OR @ProdType_ID = ProdType.ID)

GROUP BY MONTH(Ord.OrdDate)

Note - i added additional where clause conditions to check on the year function on orddate as this can be null

google for joins SQL and am sure you will find much more quality information than this

Hope this helps

Handling non existent values in sql query expression for ssrs chart

6 Answers6

Linked