统计每月数据，补齐缺少月份，并计算每月小于600秒比率

SELECT
--a.dt AS '时间',
ISNULL(b.Rate, '0')*100 AS '比例'
FROM
(
SELECT
CONVERT(varchar(7) ,dateadd(m, number, '2019-01-01'), 120) dt
FROM
master..spt_values
WHERE
type = 'p'
AND dateadd(m, number, '2019-01-01') <= '2019-07-27'
) a
LEFT JOIN (
SELECT
CAST(
(SUM(CASE WHEN S2FMC<=600 THEN 1 ELSE 0 END)
/
cast( COUNT(*) as numeric(5,2))
) as numeric(5,2)
) AS Rate
,CONVERT(VARCHAR(7) ,[StatisticalTime], 120) AS [StatisticalTime]
FROM EDSS_CPCenter.dbo.CPCenter_Statistical
WHERE [StatisticalTime]>='2019-01-01' and [StatisticalTime]<='2019-07-27'
GROUP BY CONVERT(VARCHAR(7) ,[StatisticalTime], 120)
) as b
ON a.dt = b.StatisticalTime

转载于:https://www.cnblogs.com/xiaoan-one/p/11106554.html