如何在一个表中显示每周总计数

Question

我正在努力实现一个报告，它将显示所有的每日计数以及每周计数在同一表中。我尝试过不同的技术，但我似乎没能得到我想要的东西。

我正试着展示下一张类似的表格。

+-----------+-----+-------+--------+--+--+--+
|  August   |     | Count |        |  |  |  |
+-----------+-----+-------+--------+--+--+--+
| 8/1/2013  | Thu | 1,967 |        |  |  |  |
| 8/2/2013  | Fri | 1,871 |        |  |  |  |
| 8/3/2013  | Sat | 1,950 |        |  |  |  |
| 8/4/2013  | Sun | 2,013 | 7801   |  |  |  |
| 8/5/2013  | Mon | 2,039 |        |  |  |  |
| 8/6/2013  | Tue | 1,871 |        |  |  |  |
| 8/7/2013  | Wed | 1,611 |        |  |  |  |
| 8/8/2013  | Thu | 1,680 |        |  |  |  |
| 8/9/2013  | Fri | 1,687 |        |  |  |  |
| 8/10/2013 | Sat | 1,649 |        |  |  |  |
| 8/11/2013 | Sun | 1,561 | 12,098 |  |  |  |
+-----------+-----+-------+--------+--+--+--+

如果有一个现有的代码或技术，我可以实现这样的东西，请让我。谢谢。

舍温

Answer 1

尝试这样的方法，但是要确保检查服务器上的哪个工作日是周日，因为这是可以修改的。

select T1.August, T1.[Count], 
case DATEPART(WEEKDAY, O.Order_Date)
    WHEN 1 THEN (SELECT CONVERT(varchar(10), SUM(T2.[Count]) FROM TableName T2 WHERE T2.August BETWEEN DATEADD(d,-7,T1.August) and T1.August))
    ELSE ''
end as Weekly_Count
FROM TablleName T1
ORDER BY T.August

Answer 2

如果您不介意将这些小计放在新行上而不是在新列上，那么GROUP BY WITH ROLLUP可能是您的解决方案：

SET LANGUAGE GERMAN用于将星期一设为一周的第一天，并允许我们总结到星期日。

SET LANGUAGE GERMAN;

WITH first AS
(
  SELECT
    date,
    day,
    DATEPART(dw, date) AS dayweek,
    DATEPART(wk, date) AS week,
    count
  FROM example
)

SELECT
  CASE WHEN (GROUPING(dayweek) = 1) THEN 'TOT' ELSE CAST(MAX(date) AS VARCHAR(20)) END AS date,
  CASE WHEN (GROUPING(dayweek) = 1) THEN 'TOT' ELSE MAX(day) END AS day,
  SUM(count) AS count
FROM first
GROUP BY week,dayweek WITH ROLLUP

请参阅木琴上的完整示例

Answer 3

如果使用存储过程，则创建一个临时表并使用游标循环它，然后进行和。你也可以这样做：

SELECT CreatedDate, Amount, CASE WHEN DATENAME(dw , CreatedDate) = 'Sunday' THEN (SELECT SUM(Amount) FROM AmountTable at2 WHERE CreatedDate <= at1.CreatedDate AND CreatedDate > DATEADD(Day, -7, at1.CreatedDate)) ELSE 0 END AS 'WeekTotal'
from AmountTable at1