问题描述:

And show them as a total for each month. I use the following script

SELECT datepart(yy,j.CompletedAt) as 'YY',

str(datepart(MM, j.CompletedAt),2) as 'MM',

Datename(MM,j.CompletedAt) as 'Month',

count('MM') as 'PMInMonth'

FROM facts_reactive.dbo.jobs j

WHERE (

(J.Reactive = -1)

AND datepart(yy,j.CompletedAt) = ' 2013'

AND j.jobstatus = 6

)

GROUP BY datepart(yy,j.CompletedAt),

str(datepart(MM, j.CompletedAt),2),

Datename(MM,j.CompletedAt)

ORDER BY datepart(yy,j.CompletedAt) ASC,

str(datepart(MM, j.CompletedAt),2) ASC;

However, if there were no jobs cancelled in the month it does not return anything; whereas I would like the month and 0 returned as the number of cancelled jobs.

Is this possible?

Many thanks in advance.

Rob

网友答案:
;WITH cMONTHS AS
(
    SELECT
        CAST('20130101' AS date) AS TheMonthStart,
        CAST('20130201' AS date) AS TheMonthEnd
    UNION ALL
    SELECT
        DATEADD(mm, 1, TheMonthStart),
        DATEADD(mm, 1, TheMonthEnd)
    FROM cMONTHS
    WHERE TheMonthStart < '20140101'
)
SELECT
   datepart(yy,c.TheMonthStart) as 'YY',  
   str(datepart(MM, c.TheMonthStart),2) as 'MM', 
   Datename(MM,c.TheMonthStart) as 'Month', 
   count(j.CompletedAt) as 'PMInMonth'
FROM
     cMONTHS c
     LEFT JOIN
     facts_reactive.dbo.jobs j ON j.CompletedAt >= c.TheMonthStart AND j.CompletedAt < c.TheMonthEnd
                   AND
                   J.Reactive = -1   
                   AND
                   j.jobstatus = 6
GROUP BY
   datepart(yy,c.TheMonthStart),  
   str(datepart(MM, c.TheMonthStart),2), 
   Datename(MM,c.TheMonthStart)
ORDER BY
   datepart(yy,c.TheMonthStart),  
   str(datepart(MM, c.TheMonthStart),2);
相关阅读:
Top