如何在sql server中获取一个月的每周(从每天到每天)

Question

我有如下数据：

Docdate                Qty   Amount
-----------------------------------
2014/08/01(Friday)     5     100
2014/08/03(Sunday)     5     100
2014/08/04(Monday)     8     100
2014/08/10(Sunday)     8     100
2014/08/11(Monday)     8     100
2014/08/17(Sunday)     8     100
2014/08/18(Monday)     8     100
2014/08/24(Sunday)     8     100
2014/08/25(Monday)     8     100
2014/08/31(Sunday)     8     100

我想用下面的模板来概括一个存储过程：

Item QtyWeek1(1-3) QtyWeek2(4-10) QtyWeek3(11-17) QtyWeek4(18-24) QtyWeek5(25-31)
A    10             16            16              16              16
B    -              -             -                -              -

在存储过程中可以做到这一点吗？

在Crystal Report中可以做到这一点吗？如果是，则应显示哪些存储过程列？

谢谢

Answer 1

如果您将Docdate DATETIME类型列为

SELECT item, SUM(CASE WHEN DATEPART(wk, Docdate) BETWEEN 1 AND 3 THEN Qty ELSE 0 END) AS qtyWeek1, 
SUM(CASE WHEN DATEPART(wk, Docdate) BETWEEN 4 AND 10 THEN Qty ELSE 0 END) AS qtyWeek2, 
SUM(CASE WHEN DATEPART(wk, Docdate) BETWEEN 11 AND 17 THEN Qty ELSE 0 END) AS qtyWeek3,
SUM(CASE WHEN DATEPART(wk, Docdate) BETWEEN 18 AND 24 THEN Qty ELSE 0 END) AS qtyWeek4,
SUM(CASE WHEN DATEPART(wk, Docdate) BETWEEN 25 AND 31 THEN Qty ELSE 0 END) AS qtyWeek5

FROM YouTable 
GROUP BY item

Answer 2

这一点都不容易。我假设您还有一个列名项目，并且希望始终为五周，因此我不得不考虑使用额外的列YYYYMM来包含所属周的月份。

则查询将为：

select ITEM, MONTH_YEAR, [1] WEEK1, [2] WEEK2, [3] WEEK3, [4] WEEK4, [5] WEEK5
from (
   select 
      ITEM, 
      YEAR(DATE1)*100+MONTH(DATE1) MONTH_YEAR,
      1 + WEEK - WEEK0 WEEK,
      sum(Qty) Qty
   from Table1 t1
   cross apply (SELECT cast(Replace(LEFT(DocDate,10),'/','-') as date)) N(DATE1)
   cross apply (SELECT DATEPART(WEEK, DATEADD(DAY, -1, DATE1))) T(WEEK)
   cross apply (SELECT DATEPART(WEEK, DateAdd(day, -1, DateAdd(month, MONTH(DATE1) - 1, DateAdd(Year, YEAR(DATE1)-1900, 0))))) U(WEEK0)
   group by WEEK, YEAR(DATE1)*100+MONTH(DATE1), WEEK0, ITEM
) A
pivot (sum(QTY) FOR WEEK in ([1],[2],[3],[4],[5])) B

术语表：

• DATE1表示日期类型的DocDate列的转换
• week表示周的编号
• WEEK0表示月份的第一周的编号

SQL Fiddle

Answer 3

因为你有水晶报表，所以不要在数据库中做交叉表。在crystal report中这样做。

如果你看过任何人关于这方面的常规建议，那就是:在报告工具中做透视

一般的解决方案是：

向现有记录集中添加一个名为weeknumber的列，

将您现有的DocDate映射到周数字(您需要解释规则，例如，什么时候开始一周，什么是第一周等等，然后我可以建议一些代码)

在crystal报表中，创建一个以新列weeknumber为中心的交叉表报表。

这样，如果您有2列或20列，crystal将处理它，而不是在数据库对象中进行硬编码。