22,209
社区成员
发帖
与我相关
我的任务
分享
------------------------------------------------------------------------
-- Author : HappyFlyStone
-- Date : 2009-10-15
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
------------------------------------------------------------------------
-- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([date] SMALLDATETIME,[f1] NVARCHAR(4),[s1] INT)
Go
INSERT INTO ta
SELECT '2009-10-01','a001',10 UNION ALL
SELECT '2009-10-03','a001',20 UNION ALL
SELECT '2009-10-04','a002',-20 UNION ALL
SELECT '2009-10-07','a001',-5 UNION ALL
SELECT '2009-10-09','a001',6 UNION ALL
SELECT '2009-10-14','a002',9 UNION ALL
SELECT '2009-10-25','a002',30 UNION ALL
SELECT '2009-10-27','a001',-12 UNION ALL
SELECT '2009-10-28','a001',20 UNION ALL
SELECT '2009-11-02','a002',8 UNION ALL
SELECT '2009-11-05','a001',7
GO
--Start
select e.dt,e.t,e.[f1],isnull(sum([s1]),0) as sl
from(
SELECT
convert(char(7),[date],120) as dt,[s1] ,[f1],
case when datepart(d,[date]) between 1 and 7 then '01-07'
when datepart(d,[date]) between 8 and 14 then '08-14'
when datepart(d,[date]) between 15 and 22 then '15-22'
else '23-last' end as t
FROM
TA
) b
right join(
select dt,t,[f1]
from
(
select '01-07' as t union all select '08-15'
union all select '15-22' union all select '23-last'
) c
,
(
select convert(char(7),[date],120) as dt
from ta group by convert(char(7),[date],120)
) d
,
(
select [f1] from ta group by [f1]
) f
)e
on b.dt = e.dt and b.t = e.t and e.f1 = b.f1
group by e.dt,e.t,e.[f1]
order by 1,3
--Result:
/*
dt t f1 sl
------- ------- ---- -----------
2009-10 01-07 a001 25
2009-10 08-15 a001 0
2009-10 15-22 a001 0
2009-10 23-last a001 8
2009-10 01-07 a002 -20
2009-10 08-15 a002 0
2009-10 15-22 a002 0
2009-10 23-last a002 30
2009-11 01-07 a001 7
2009-11 08-15 a001 0
2009-11 15-22 a001 0
2009-11 23-last a001 0
2009-11 01-07 a002 8
2009-11 08-15 a002 0
2009-11 15-22 a002 0
2009-11 23-last a002 0
*/
--End