SQL查询按周获取总计数

Question

我需要获取一份报告以获取神经科医生的一些医疗数据。以下查询按月份计算总计和平均值。我怎样才能改变它以按周计算总计和平均值？

SELECT 
    [Month] = DATENAME(MONTH, DATEADD(MONTH, MONTH(HeadacheDate), -1)), 
    [Total] = COUNT(CASE 
    WHEN Severity > 0 THEN 1 
    END), 
    [Light] = COUNT(CASE 
    WHEN Severity > 0 AND 
     Severity < 4 THEN 1 
    END), 
    [Moderate] = COUNT(CASE 
    WHEN Severity > 3 AND 
     Severity < 7 THEN 1 
    END), 
    [Severe] = COUNT(CASE 
    WHEN Severity > 6 THEN 1 
    END), 
    [DHE or ER] = COUNT(CASE 
    WHEN Medication LIKE '%dhe%' THEN 1 
    END) 
FROM HeadacheData 
WHERE YEAR(HeadacheDate) = 2016 
GROUP BY MONTH(HeadacheDate); 

Answer 1

在DATENAME功能，并且还通过组它只需使用WEEK代替MONTH。

而且查询的更易读的格式会是这样的......

SELECT 
    [Week]  = DATENAME(WEEK, DATEADD(MONTH, MONTH(HeadacheDate), -1)) 
    ,[Total]  = COUNT(CASE WHEN Severity > 0 THEN 1 END) 
    ,[Light]  = COUNT(CASE WHEN Severity > 0 AND Severity < 4 THEN 1 END) 
    ,[Moderate] = COUNT(CASE WHEN Severity > 3 AND Severity < 7 THEN 1 END) 
    ,[Severe] = COUNT(CASE WHEN Severity > 6 THEN 1 END) 
    ,[DHE or ER] = COUNT(CASE WHEN Medication LIKE '%dhe%' THEN 1 END) 
FROM HeadacheData 
WHERE YEAR(HeadacheDate) = 2016 
GROUP BY DATENAME(WEEK, DATEADD(MONTH, MONTH(HeadacheDate), -1));