在SQL Server中给定日期之间按周生成报告

Question

我需要将报告按工作周拆分，工作周是从周一到周日。假设我将start_date设置为01-12-2012，将end_date设置为29-12-2011。现在，我想将1-4作为week1，5-11作为week2，12-18作为week3，19-25作为week4，26-29作为week5。

我已经尝试了下面的代码，但没有得到准确的结果。任何修改或建议都将不胜感激。提前谢谢。

SELECT 
    ta.account, ta.customer, 

    isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1) = '1' 
THEN amount END),0) AS "1",

    isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1)= '2' 
    AND (datepart(dw,ta.dt)= 1)
    THEN amount END),0) AS "1",

    isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1)= '2' 
    AND (datepart(dw,ta.dt)<> 1)
    THEN amount END),0) AS "2",

    isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1)= '3' 
    AND (datepart(dw,ta.dt) = 1)    
    THEN amount END),0) AS "2",

    isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1)= '3' 
    AND (datepart(dw,ta.dt)<> 1)    
    THEN amount END),0) AS "3",

    isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1) = '4' 
    AND (datepart(dw,ta.dt) = 1)        
    THEN amount END),0) AS "3",

    isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1) = '4' 
AND (datepart(dw,ta.dt) <> 1)   
    THEN amount END),0) AS "4",

    isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1) = '5' 
    AND (datepart(dw,ta.dt) = 1)    
    THEN amount END),0) AS "4",

    isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1) = '5' 
    AND (datepart(dw,ta.dt) <> 1)   
    THEN amount END),0) AS "5"

FROM 
    (SELECT * FROM tablename
     WHERE dt >= '12/01/2011 00:00:00' AND dt < '12/26/2011 00:00:00'
    ) ta
GROUP BY 
    ta.account, ta.customer

所需的o/p为：

Account Customer Week1 Week2 Week3 Week4 Week5

对于上述给定日期，week1应为1-4 week2应为5-11 week2应为12-18 week2应为19-25 week2应为26-01(截至2012年1月)

因为我在每周一做报告，所以我想要从周一到周日的总金额为一周。

可能有这样一种情况，客户可能没有任何用法。因此，在这种情况下，我希望它与上面示例中的week5一样为0.00。

希望你能理解我的问题。谢谢

Answer 1

尝尝这个

SET DATEFIRST 1    
SELECT tn.account, tn.customer, SUM(amount), DATEPART(ww,tn.dt) WeekNumber
FROM tablename tn
WHERE dt >= '12/01/2011 00:00:00' 
      and dt < '12/26/2011 00:00:00'
GROUP BY ta.account, ta.customer, DATEPART(ww,tn.dt)

DATEPART返回一个整数，它表示指定日期的指定日期部分。在这种情况下，一年中的星期数。

SET DATEFIRST将一周的第一天设置为从1到7的数字，其中1表示星期一，2-星期二，依此类推。