計算每一天 - 距離臨近作業日的天數。可以將前瞻天數限制為 20 天。
| 日期 | IS_HOLIDAY | 理想的結果 |
|---|---|---|
| 05.01.2008 | 1 | 4 |
| 06.01.2008 | 1 | 3 |
| 07.01.2008 | 1 | 2 |
| 08.01.2008 | 1 | 1 |
| 09.01.2008 | 0 | 1 |
| 10.01.2008 | 0 | 1 |
| 11.01.2008 | 0 | 3 |
| 12.01.2008 | 1 | 2 |
| 13.01.2008 | 1 | 1 |
| 14.01.2008 | 0 | 1 |
| 15.01.2008 | 0 | 1 |
| 16.01.2008 | 0 | 1 |
| 17.01.2008 | 0 | 1 |
查詢資料：
create table #tmp ( [date] date, is_holiday int )
insert into #tmp ( date, is_holiday )
select '2008-01-05' date, 1 is_holiday union
select '2008-01-06' date, 1 is_holiday union
select '2008-01-07' date, 1 is_holiday union
select '2008-01-08' date, 1 is_holiday union
select '2008-01-09' date, 0 is_holiday union
select '2008-01-10' date, 0 is_holiday union
select '2008-01-11' date, 0 is_holiday union
select '2008-01-12' date, 1 is_holiday union
select '2008-01-13' date, 1 is_holiday union
select '2008-01-14' date, 0 is_holiday union
select '2008-01-15' date, 0 is_holiday union
select '2008-01-16' date, 0 is_holiday union
select '2008-01-17' date, 0 is_holiday
我嘗試使用以下結構：
select date, sum(convert(int, is_holiday)) over (
order by date
rows between 5 preceding and current row
) as rsum
from dic_calendar_production
但它看起來在后面。當我在 'following' 上更改 'preceding' 時，它會引發錯誤
'BETWEEN ... FOLLOWING AND CURRENT ROW' 不是有效的視窗框架，不能與 OVER 子句一起使用。
而且，它可以給我某個范圍內的總和，但是當它到達第一個零時它不會停止。
uj5u.com熱心網友回復：
似乎您只需要將資料分組，然后ROW_NUMBER：
create table #tmp ( [date] date, is_holiday int )
insert into #tmp ( date, is_holiday )
VALUES ('2008-01-05', 1),
('2008-01-06', 1),
('2008-01-07', 1),
('2008-01-08', 1),
('2008-01-09', 0),
('2008-01-10', 0),
('2008-01-11', 0),
('2008-01-12', 1),
('2008-01-13', 1),
('2008-01-14', 0),
('2008-01-15', 0),
('2008-01-16', 0),
('2008-01-17', 0);
GO
WITH CTE AS(
SELECT [date],
is_holiday,
COUNT(CASE is_holiday WHEN 0 THEN 1 END) OVER (ORDER BY [date] ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS Grp
FROM #tmp t)
SELECT [date],
is_holiday,
ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY [date] DESC) AS desirableresult
FROM CTE
ORDER BY [date];
GO
DROP TABLE #tmp;
uj5u.com熱心網友回復：
您可以將相關查詢與datediff一起使用：
select *,
IsNull(
DateDiff(
day, t.[date],
(select Min([date]) from #tmp t2 where t2.[date] > t.[date] and t2.is_holiday = 0)
),1) Result
from #tmp t
order by [date];
DB<>小提琴示例
uj5u.com熱心網友回復：
select t.date,t.is_holiday,isnull(app.date,t.date),isnull(DATEDIFF(day,t.date,app.date),1)diff
from #tmp t
cross apply
(
select min(x.date) as date
from #tmp as x
where t.date<x.date
and x.is_holiday=0
)app
您也可以嘗試交叉應用方法
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/466132.html