I am using the following query in an ssrs report.
Declare @Year int = 2012
Declare @ProdType_ID int = 1
SELECT
MONTH(Ord.OrdDate) AS 'MONTH',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) THEN 1 END) as Order1,
COUNT(CASE WHEN @Year-1 = YEAR(Ord.OrdDate) THEN 1 END) as 'Order2'
FROM
(
select 1 as number
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
union
select 10
union
select 11
union
select 12
) as months
JOIN Ord on Number = Month(Ord.OrdDate)
JOIN Prod ON Ord.Prod_ID = Prod.ID
JOIN ProdType ON Prod.ProdType_ID = ProdType.ID
WHERE @ProdType_ID = ProdType.ID
GROUP BY months.number, Month(Ord.OrdDate)
This returns a table with the correct grouping except for the fact that months that haven't happened yet or were not recorded don’t appear. I would like every month to display even if it hasn't come to pass yet and I would like their order values to be zero.
Is there some way I could put an array or something similar in the FROM clause? Thanks.
It worked after removing the WHERE clause and doing all the filtering in the count aggregate.
SELECT
MONTH(Ord.OrdDate) AS 'Month',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) and @ProdType_ID = ProdType.ID THEN 1 END) AS Order1,
COUNT(CASE WHEN @Year-1 = YEAR(Ord.OrdDate) and @ProdType_ID = ProdType.ID THEN 1 END) AS Order2
FROM
(
SELECT 1 AS Number
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6
UNION
SELECT 7
UNION
SELECT 8
UNION
SELECT 9
UNION
SELECT 10
UNION
SELECT 11
UNION
SELECT 12
) AS Months
LEFT OUTER JOIN Ord ON Number = MONTH(Ord.OrdDate)
LEFT OUTER JOIN Prod ON Ord.Prod_ID = Prod.ID
LEFT OUTER JOIN ProdType ON Prod.ProdType_ID = ProdType.ID
GROUP BY Months.Number, MONTH(Ord.OrdDate)
I think you may want to use a OUTER JOIN. With a left outer join the query will return all rows from the "left table" in the FROM clause that has rows that meet any WHERE conditions. The column values from the other tables will be NULL. So you may need to add ISNULL functions around the column values used in the select statement if special handling of NULL is needed.
This MSDN article Using Outer Joins has more complete info.
I've re-written you're query using left outer join. You may need to do some additional work on the case statement, but perhaps not -- The YEAR function will return NULL if the parameter value is NULL, and I think the logic can stand.
Declare @Year int = 2012
Declare @ProdType_ID int = 1
SELECT
MONTH(Ord.OrdDate) AS 'MONTH',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) THEN 1 END) as Order1,
COUNT(CASE WHEN @Year-1 = YEAR(Ord.OrdDate) THEN 1 END) as 'Order2'
FROM
(
select 1 as number
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
union
select 10
union
select 11
union
select 12
) as months
LEFT OUTER JOIN Ord on Number = Month(Ord.OrdDate)
LEFT OUTER JOIN Prod ON Ord.Prod_ID = Prod.ID
LEFT OUTER JOIN ProdType ON Prod.ProdType_ID = ProdType.ID
WHERE @ProdType_ID = ProdType.ID
GROUP BY months.number, Month(Ord.OrdDate)
If would also use a LEFT JOIN. But I would suggest using a Tally table for getting the Months. This will work on sql server 2005+:
Declare @Year int = 2012
Declare @ProdType_ID int = 1
;WITH Months ( Number ) AS
(
SELECT 1 UNION ALL
SELECT 1 + Number FROM Months WHERE Number < 12
)
SELECT
MONTH(Ord.OrdDate) AS 'MONTH',
COUNT(CASE WHEN @Year = YEAR(Ord.OrdDate) THEN 1 END) as Order1,
COUNT(CASE WHEN @Year-1 = YEAR(Ord.OrdDate) THEN 1 END) as 'Order2'
FROM
Months
LEFT JOIN Ord on Number = Month(Ord.OrdDate)
LEFT JOIN Prod ON Ord.Prod_ID = Prod.ID
LEFT JOIN ProdType ON Prod.ProdType_ID = ProdType.ID
WHERE @ProdType_ID = ProdType.ID
GROUP BY months.number, Month(Ord.OrdDate)