一个非常变态的日期统计需求SQL中如何实现？

Question

表order_head
order_head_id
order_date
order_qty表中记录
order_head_id       order_date        order_qty
1                   2008/06/01        10
2                   2008/06/04        20
3                   2008/06/07        10
4                   2008/06/10        20
5                   2008/06/15        10
6                   2008/06/25        10
7                   2008/06/29        10
8                   2008/07/02        10
9                   2008/07/12        10
...
1， 如果统计日期范围是 2008/06/22 - 2008/07/12 需要表现为
对象日                                数量
2008/06/22 - 2008/06/28          10
2008/06/29 - 2008/07/05          20
2008/07/06 - 2008/07/12          102， 如果统计日期范围是 2008/06/04 - 2008/06/25 需要表现为
对象日                                数量
2008/06/04 - 2008/06/07          30
2008/06/08 - 2008/06/14          20
2008/06/15 - 2008/06/21          10
2008/06/22 - 2008/06/25          103， 如果统计日期范围是 2008/06/28 - 2008/06/29 需要表现为
对象日                                数量
2008/06/28 - 2008/06/28          0
2008/06/29 - 2008/06/29          10PS： 对象日期范围其实就是周（周日-周6）

Answer 1

--> liangCK小梁 于2008-10-17
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (order_head_id INT,order_date DATETIME,order_qty INT)
INSERT INTO #T
SELECT 1,'2008/06/01',10 UNION ALL
SELECT 2,'2008/06/04',20 UNION ALL
SELECT 3,'2008/06/07',10 UNION ALL
SELECT 4,'2008/06/10',20 UNION ALL
SELECT 5,'2008/06/15',10 UNION ALL
SELECT 6,'2008/06/25',10 UNION ALL
SELECT 7,'2008/06/29',10 UNION ALL
SELECT 8,'2008/07/02',10 UNION ALL
SELECT 9,'2008/07/12',10--SQL查询如下:SELECT CONVERT(VARCHAR(10),MIN(order_date),120)
       +'~'
       +CONVERT(VARCHAR(10),MAX(order_date),120) rder_date,
       SUM(order_qty) order_qty
FROM #T
WHERE order_date BETWEEN '2008/06/22' AND '2008/07/12' 
GROUP BY DATEPART(WEEK,order_date)/*
rder_date             order_qty
--------------------- -----------
2008-06-25~2008-06-25 10
2008-06-29~2008-07-02 20
2008-07-12~2008-07-12 10(3 行受影响)*/

Answer 2

--> liangCK小梁 于2008-10-17
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.T') IS NOT NULL DROP TABLE T
CREATE TABLE T (order_head_id INT,order_date DATETIME,order_qty INT)
INSERT INTO T
SELECT 1,'2008/06/01',10 UNION ALL
SELECT 2,'2008/06/04',20 UNION ALL
SELECT 3,'2008/06/07',10 UNION ALL
SELECT 4,'2008/06/10',20 UNION ALL
SELECT 5,'2008/06/15',10 UNION ALL
SELECT 6,'2008/06/25',10 UNION ALL
SELECT 7,'2008/06/29',10 UNION ALL
SELECT 8,'2008/07/02',10 UNION ALL
SELECT 9,'2008/07/12',10--SQL查询如下:GO
CREATE PROC p
  @start_date DATETIME,
  @end_date DATETIME
AS
  SELECT CONVERT(VARCHAR(10),MIN(order_date),120)
       +'~'
       +CONVERT(VARCHAR(10),MAX(order_date),120) rder_date,
       SUM(order_qty) order_qty
  FROM T
  WHERE order_date BETWEEN @start_date AND @end_date 
  GROUP BY DATEPART(WEEK,order_date)
GOEXEC p '2008/06/22','2008/07/12'EXEC p '2008/06/04','2008/06/25'EXEC p '2008/06/28','2008/06/29'GODROP PROC p
DROP TABLE T/*
rder_date             order_qty
--------------------- -----------
2008-06-25~2008-06-25 10
2008-06-29~2008-07-02 20
2008-07-12~2008-07-12 10(3 行受影响)rder_date             order_qty
--------------------- -----------
2008-06-04~2008-06-07 30
2008-06-10~2008-06-10 20
2008-06-15~2008-06-15 10
2008-06-25~2008-06-25 10(4 行受影响)rder_date             order_qty
--------------------- -----------
2008-06-29~2008-06-29 10(1 行受影响)
*/

Answer 3

--> liangCK小梁 于2008-10-17
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.T') IS NOT NULL DROP TABLE T
CREATE TABLE T (order_head_id INT,order_date DATETIME,order_qty INT)
INSERT INTO T
SELECT 1,'2008/06/01',10 UNION ALL
SELECT 2,'2008/06/04',20 UNION ALL
SELECT 3,'2008/06/07',10 UNION ALL
SELECT 4,'2008/06/10',20 UNION ALL
SELECT 5,'2008/06/15',10 UNION ALL
SELECT 6,'2008/06/25',10 UNION ALL
SELECT 7,'2008/06/29',10 UNION ALL
SELECT 8,'2008/07/02',10 UNION ALL
SELECT 9,'2008/07/12',10--SQL查询如下:GO
CREATE PROC p
  @start_date DATETIME,
  @end_date DATETIME
AS
  SELECT CONVERT(VARCHAR(10),MIN(order_date),111)
       +'~~'
       +CONVERT(VARCHAR(10),MAX(order_date),111) rder_date,
       SUM(order_qty) order_qty
  FROM T
  WHERE order_date BETWEEN @start_date AND @end_date 
  GROUP BY DATEPART(WEEK,order_date)
GOEXEC p '2008/06/22','2008/07/12'EXEC p '2008/06/04','2008/06/25'EXEC p '2008/06/28','2008/06/29'GODROP PROC p
DROP TABLE T/*
rder_date              order_qty
---------------------- -----------
2008/06/25~~2008/06/25 10
2008/06/29~~2008/07/02 20
2008/07/12~~2008/07/12 10(3 行受影响)rder_date              order_qty
---------------------- -----------
2008/06/04~~2008/06/07 30
2008/06/10~~2008/06/10 20
2008/06/15~~2008/06/15 10
2008/06/25~~2008/06/25 10(4 行受影响)rder_date              order_qty
---------------------- -----------
2008/06/29~~2008/06/29 10(1 行受影响)
*/

Answer 4

先确定@@datefirst的值为7，再用datepart分组就行了

Answer 5

IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (order_head_id INT,order_date DATETIME,order_qty INT)
INSERT INTO #T
SELECT 1,'2008/06/01',10 UNION ALL
SELECT 2,'2008/06/04',20 UNION ALL
SELECT 3,'2008/06/07',10 UNION ALL
SELECT 4,'2008/06/10',20 UNION ALL
SELECT 5,'2008/06/15',10 UNION ALL
SELECT 6,'2008/06/25',10 UNION ALL
SELECT 7,'2008/06/29',10 UNION ALL
SELECT 8,'2008/07/02',10 UNION ALL
SELECT 9,'2008/07/12',10
SET DATEFIRST 7
go
SELECT DATEADD(wk,  DATEDIFF(wk,0,max(order_date)), 0)-1 ,
DATEADD(wk,  DATEDIFF(wk,0,max(order_date)),  0)+5
     ,
       SUM(order_qty) order_qty
FROM #T
WHERE order_date BETWEEN '2008/06/22' AND '2008/07/12' 
GROUP BY DATEPART(WEEK,order_date)/*
----------------------- ----------------------- -----------
2008-06-22 00:00:00.000 2008-06-28 00:00:00.000 10
2008-06-29 00:00:00.000 2008-07-05 00:00:00.000 20
2008-07-06 00:00:00.000 2008-07-12 00:00:00.000 10
(3 行受影响)*/

Answer 6

IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (order_head_id INT,order_date DATETIME,order_qty INT)
INSERT INTO #T
SELECT 1,'2008/06/01',10 UNION ALL
SELECT 2,'2008/06/04',20 UNION ALL
SELECT 3,'2008/06/07',10 UNION ALL
SELECT 4,'2008/06/10',20 UNION ALL
SELECT 5,'2008/06/15',10 UNION ALL
SELECT 6,'2008/06/25',10 UNION ALL
SELECT 7,'2008/06/29',10 UNION ALL
SELECT 8,'2008/07/02',10 UNION ALL
SELECT 9,'2008/07/12',10
SET DATEFIRST 7
go
SELECT CONVERT(VARCHAR(10),DATEADD(wk,  DATEDIFF(wk,0,max(order_date)), 0)-1 ,111)+'-'+
CONVERT(VARCHAR(10),DATEADD(wk,  DATEDIFF(wk,0,max(order_date)),  0)+5,111) rder_date,
       SUM(order_qty) order_qty
FROM #T
WHERE order_date BETWEEN '2008/06/22' AND '2008/07/12' 
GROUP BY DATEPART(WEEK,order_date)/*
rder_date             order_qty
--------------------- -----------
2008/06/22-2008/06/28 10
2008/06/29-2008/07/05 20
2008/07/06-2008/07/12 10(3 行受影响)
*/

Answer 7

传说fc已关注此贴..
IF OBJECT_ID('tb','u') IS NOT NULL DROP TABLE tb
GO
CREATE TABLE tb (order_head_id INT,order_date DATETIME,order_qty INT)
INSERT INTO tb SELECT 1,'2008/06/01',10 
UNION ALL SELECT 2,'2008/06/04',20 
UNION ALL SELECT 3,'2008/06/07',10 
UNION ALL SELECT 4,'2008/06/10',20 
UNION ALL SELECT 5,'2008/06/15',10 
UNION ALL SELECT 6,'2008/06/25',10 
UNION ALL SELECT 7,'2008/06/29',10 
UNION ALL SELECT 8,'2008/07/02',10 
UNION ALL SELECT 9,'2008/07/12',10
GODECLARE @d1 DATETIME,@d2 DATETIME--将以下任一组数据赋值注释取消即可看到效果--SELECT @d1='2008/06/22',@d2='2008/07/12'
/*
2008-06-22 00:00:00.000 2008-06-28 00:00:00.000 10
2008-06-29 00:00:00.000 2008-07-05 00:00:00.000 20
2008-07-06 00:00:00.000 2008-07-12 00:00:00.000 10
*/
--SELECT @d1='2008/06/04',@d2='2008/06/25'
/*
2008-06-04 00:00:00.000 2008-06-07 00:00:00.000 30
2008-06-08 00:00:00.000 2008-06-14 00:00:00.000 20
2008-06-15 00:00:00.000 2008-06-21 00:00:00.000 10
2008-06-22 00:00:00.000 2008-06-25 00:00:00.000 10
*/
--SELECT @d1='2008/06/28',@d2='2008/06/29'
/*
2008-06-28 00:00:00.000 2008-06-28 00:00:00.000 0
2008-06-29 00:00:00.000 2008-06-29 00:00:00.000 10
*/SELECT TOP 100 IDENTITY(INT) id INTO # FROM tb;WITH fc AS
(
SELECT * FROM
(SELECT DATEADD(dd,(id-1)*7+1-DATEPART(dw,@d1),@d1) d FROM #) x
WHERE d<=@d2 AND @d1<=d
)
SELECT x.d1,x.d2,ISNULL(SUM(a.order_qty) ,0)
FROM
(
SELECT a.d d1,DATEADD(dd,-1,ISNULL(b.d,DATEADD(dd,1,@d2))) d2 
FROM fc a
LEFT JOIN fc b
ON DATEDIFF(dd,a.d,b.d)=7
UNION SELECT @d1,MIN(d)-1 FROM fc
) x
LEFT JOIN tb a
ON order_date BETWEEN d1 AND d2
GROUP BY x.d1,x.d2
GODROP TABLE #
GO

Answer 8

CREATE TABLE T (order_head_id INT,order_date DATETIME,order_qty INT)
INSERT INTO T
SELECT 1,'2008/06/01',10 UNION ALL
SELECT 2,'2008/06/04',20 UNION ALL
SELECT 3,'2008/06/07',10 UNION ALL
SELECT 4,'2008/06/10',20 UNION ALL
SELECT 5,'2008/06/15',10 UNION ALL
SELECT 6,'2008/06/25',10 UNION ALL
SELECT 7,'2008/06/29',10 UNION ALL
SELECT 8,'2008/07/02',10 UNION ALL
SELECT 9,'2008/07/12',10
GO
alter PROC p
  @start_date DATETIME,
  @end_date DATETIME
AS
  SELECT [对象日]=CONVERT(VARCHAR(10),case when dateadd(d,1-datepart(dw,MIN(order_date)),MIN(order_date))<@start_date then @start_date else dateadd(d,1-datepart(dw,MIN(order_date)),MIN(order_date)) end,111)
       +'~~'
       +CONVERT(VARCHAR(10),case when dateadd(d,7-datepart(dw,MAX(order_date)),MAX(order_date))>@end_date then @end_date else dateadd(d,7-datepart(dw,MAX(order_date)),MAX(order_date)) end,111)
       ,[数量]=SUM(order_qty) order_qty
  FROM T
  WHERE order_date BETWEEN @start_date AND @end_date 
  GROUP BY DATEPART(WEEK,order_date)
GOEXEC p '2008/06/22','2008/07/12'EXEC p '2008/06/04','2008/06/25'EXEC p '2008/06/28','2008/06/29'/*
对象日                   数量
-----------------------------
2008/06/22~~2008/06/28 10
2008/06/29~~2008/07/05 20
2008/07/06~~2008/07/12 10
-----------------------------
2008/06/04~~2008/06/07 30
2008/06/08~~2008/06/14 20
2008/06/15~~2008/06/21 10
2008/06/22~~2008/06/25 10-----------------------------
2008/06/29~~2008/06/29 10
*/