34,838
社区成员




DECLARE @Dt DATETIME
SET @Dt = CONVERT(VARCHAR(10), GETDATE() + 1, 120)
SELECT a.khh
,SUM(CASE WHEN DATEDIFF(dd, a.[DATE], @Dt) < 5
THEN ISNULL(a.SKJE, 0) - ISNULL(a.FHJE, 0)
ELSE 0
END) AS [5天内]
,SUM(CASE WHEN DATEDIFF(dd, a.[DATE], @Dt) < 10
THEN ISNULL(a.SKJE, 0) - ISNULL(a.FHJE, 0)
ELSE 0
END) AS [10天内]
,SUM(CASE WHEN DATEDIFF(dd, a.[DATE], @Dt) < 15
THEN ISNULL(a.SKJE, 0) - ISNULL(a.FHJE, 0)
ELSE 0
END) AS [15天内]
,SUM(CASE WHEN DATEDIFF(dd, a.[DATE], @Dt) >= 15
THEN ISNULL(a.SKJE, 0) - ISNULL(a.FHJE, 0)
ELSE 0
END) AS [15天之前]
FROM View_XSHTTZH AS a
WHERE ISNULL(a.SKJE, 0) < ISNULL(a.FHJE, 0)
AND a.khh IN (
SELECT TOP 20
khh
FROM View_XSHTTZH
GROUP BY khh
ORDER BY SUM(CASE WHEN ISNULL(SKJE, 0) < ISNULL(FHJE, 0)
THEN ISNULL(FHJE, 0) - ISNULL(SKJE, 0)
ELSE 0
END) DESC )
GROUP BY a.khh
if object_id('Tempdb..#t') is not null drop table #t
create table #t(
[KHH] int identity(1,1) not null,
[DATE] datetime null,
[FHJE] int null,
[SKJE] int null
)
Insert Into #t
select '2014-04-01',1,2 union all
select '2014-04-05',3,2 union all
select '2014-04-09',3,1 union all
select '2014-04-12',4,2 union all
select '2014-04-15',6,2 union all
select '2014-04-20',7,2
;with cte as(
select *,SKJE-FHJE as result from #t
where FHJE>SKJE
)
select [KHH],[DATE],[FHJE],[SKJE],[result],
(case when datediff(dd,[DATE],getdate())<5 then result else null end ) as '小于5天',
(case when datediff(dd,[DATE],getdate()) between 5 and 15 then result else null end ) as '5到15天',
(case when datediff(dd,[DATE],getdate())>15 then result else null end ) as '15天以前'
from cte
-------------------------
--结果
(6 行受影响)
KHH DATE FHJE SKJE result 小于5天 5到15天 15天以前
----------- ----------------------- ----------- ----------- ----------- ----------- ----------- -----------
2 2014-04-05 00:00:00.000 3 2 -1 NULL NULL -1
3 2014-04-09 00:00:00.000 3 1 -2 NULL -2 NULL
4 2014-04-12 00:00:00.000 4 2 -2 NULL -2 NULL
5 2014-04-15 00:00:00.000 6 2 -4 NULL -4 NULL
6 2014-04-20 00:00:00.000 7 2 -5 -5 NULL NULL
(5 行受影响)
--@days 你需要的天数
declare @days int
set @days=5
;with cte as(
select top 20 *, (SKJE-FHJE) as result from 你的表
where FHJE>SKJE
)
select * from cte where datediff(dd,[DATE],getdate())<@days