34,590
社区成员
发帖
与我相关
我的任务
分享
DECLARE @TMP TABLE(SN INT ,EndDate DATETIME,Num DECIMAL(18,2) )
INSERT INTO @TMP
SELECT 1,CONVERT(CHAR(10),GETDATE(),120),22 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-1,GETDATE()),120),1 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-2,GETDATE()),120),0.1 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-6,GETDATE()),120),20 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-14,GETDATE()),120),50 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-15,GETDATE()),120),25 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-26,GETDATE()),120),48 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-37,GETDATE()),120),12 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(MONTH,-3,GETDATE()),120),36 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-1,GETDATE()),120),36 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-2,GETDATE()),120),12 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-3,GETDATE()),120),22.23 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-14,GETDATE()),120),45 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-25,GETDATE()),120),22.65 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-36,GETDATE()),120),12 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(MONTH,-7,GETDATE()),120),23 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(YEAR,-1,GETDATE()),120),65
SELECT * , case when (select sum(num) from @tmp where sn = t.sn and enddate >= t.enddate) <= 100
then (select sum(num) from @tmp where sn = t.sn and enddate >= t.enddate)
end sum_num
FROM @TMP t
/*
SN EndDate Num sum_num
----------- ------------------------------------------------------ -------------------- ----------------------------------------
1 2009-03-19 00:00:00.000 22.00 22.00
1 2009-03-18 00:00:00.000 1.00 23.00
1 2009-03-17 00:00:00.000 .10 23.10
1 2009-03-13 00:00:00.000 20.00 43.10
1 2009-03-05 00:00:00.000 50.00 93.10
1 2009-03-04 00:00:00.000 25.00 NULL
1 2009-02-21 00:00:00.000 48.00 NULL
1 2009-02-10 00:00:00.000 12.00 NULL
1 2008-12-19 00:00:00.000 36.00 NULL
2 2009-03-18 00:00:00.000 36.00 36.00
2 2009-03-17 00:00:00.000 12.00 48.00
2 2009-03-16 00:00:00.000 22.23 70.23
2 2009-03-05 00:00:00.000 45.00 NULL
2 2009-02-22 00:00:00.000 22.65 NULL
2 2009-02-11 00:00:00.000 12.00 NULL
2 2008-08-19 00:00:00.000 23.00 NULL
2 2008-03-19 00:00:00.000 65.00 NULL
(所影响的行数为 17 行)
*/
DECLARE @TMP TABLE(SN INT ,EndDate DATETIME,Num DECIMAL(18,2) )
INSERT INTO @TMP
SELECT 1,CONVERT(CHAR(10),GETDATE(),120),22 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-1,GETDATE()),120),1 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-2,GETDATE()),120),0.1 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-6,GETDATE()),120),20 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-14,GETDATE()),120),50 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-15,GETDATE()),120),25 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-26,GETDATE()),120),48 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(DAY,-37,GETDATE()),120),12 UNION ALL
SELECT 1,CONVERT(CHAR(10),DATEADD(MONTH,-3,GETDATE()),120),36 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-1,GETDATE()),120),36 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-2,GETDATE()),120),12 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-3,GETDATE()),120),22.23 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-14,GETDATE()),120),45 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-25,GETDATE()),120),22.65 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(DAY,-36,GETDATE()),120),12 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(MONTH,-7,GETDATE()),120),23 UNION ALL
SELECT 2,CONVERT(CHAR(10),DATEADD(YEAR,-1,GETDATE()),120),65
select
a.*,b.EndDate
from
@TMP a
left join
@TMP b
on
a.SN=b.SN
and
a.EndDate>=b.EndDate
and
(select sum(Num) from @TMP where EndDate between b.EndDate and a.EndDate and SN=a.SN)>=100
and
(select sum(c.Num) from @TMP c where EndDate between (select top 1 EndDate from @TMP where SN=b.SN and EndDate>b.EndDate order by EndDate asc) and a.EndDate and SN=a.SN)<100
/*
SN EndDate Num EndDate
----------- ------------------------------------------------------ -------------------- ------------------------------------------------------
1 2009-03-19 00:00:00.000 22.00 2009-03-04 00:00:00.000
1 2009-03-18 00:00:00.000 1.00 2009-02-21 00:00:00.000
1 2009-03-17 00:00:00.000 .10 2009-02-21 00:00:00.000
1 2009-03-13 00:00:00.000 20.00 2009-02-21 00:00:00.000
1 2009-03-05 00:00:00.000 50.00 2009-02-21 00:00:00.000
1 2009-03-04 00:00:00.000 25.00 2008-12-19 00:00:00.000
1 2009-02-21 00:00:00.000 48.00 NULL
1 2009-02-10 00:00:00.000 12.00 NULL
1 2008-12-19 00:00:00.000 36.00 NULL
2 2009-03-18 00:00:00.000 36.00 2009-03-05 00:00:00.000
2 2009-03-17 00:00:00.000 12.00 2009-02-22 00:00:00.000
2 2009-03-16 00:00:00.000 22.23 2009-02-11 00:00:00.000
2 2009-03-05 00:00:00.000 45.00 2008-08-19 00:00:00.000
2 2009-02-22 00:00:00.000 22.65 2008-03-19 00:00:00.000
2 2009-02-11 00:00:00.000 12.00 2008-03-19 00:00:00.000
2 2008-08-19 00:00:00.000 23.00 NULL
2 2008-03-19 00:00:00.000 65.00 NULL
*/