ChrisM@Work (6/25/2014)
Your query has four output columns:SELECT
ot.OperationalTypeAlternateKey AS [OperationalTypeAlternateKey]
,ot.OperationalType AS [Entry Type]
,Convert(Varchar, DateDayOfMonth)+' '+DateMonthName + ' '+ Convert(Varchar,DateYear) AS [Date]
,COUNT(*) AS [Total]
FROM
If you're returning a single row with a count of 0 when there are no matching rows for the given set of parameters, what would you like to see returned in the first two columns of the output?
+1,
your base table should be #tmpOperationalTypes as per the 1st two columns are suggesting in SELECT clause. Date dimension is Inner join with fctOperationalEntry table and following is your where clause.
WHERE d.DateYear IN (SELECT Value FROM dbo.fnSplitString(@Year, ','))
AND d.DateDayOfMonth = @Day
AND d.DateMonthName = @Month
this will eliminate any possible combination.