34,588
社区成员
发帖
与我相关
我的任务
分享
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)
GO
EXEC 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
*/
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
GO
DECLARE @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
GO
DROP TABLE #
GO
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 行受影响)
*/
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 行受影响)
*/
set datefirst 7
--> 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)
GO
EXEC p '2008/06/22','2008/07/12'
EXEC p '2008/06/04','2008/06/25'
EXEC p '2008/06/28','2008/06/29'
GO
DROP 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 行受影响)
*/
--> 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)
GO
EXEC p '2008/06/22','2008/07/12'
EXEC p '2008/06/04','2008/06/25'
EXEC p '2008/06/28','2008/06/29'
GO
DROP 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 行受影响)
*/
--> 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 行受影响)
*/