如何在不使用SQL CURSOR的情况下根据日期序列中的事件翻转位开关

Question

我有一个日期列表，偶尔会发生与开始和停止日期相对应的事件，并且我想为发生在开始和停止之间的所有日期切换一个位开关。

示例：

DateList = (
'2001-01-01', 
'2001-01-02', 
'2001-01-03', 
'2001-01-04', 
'2001-01-05', 
'2001-01-06', 
'2001-01-07', 
'2001-01-08', 
'2001-01-09', 
'2001-01-10', 
'2001-01-011')
Events = (
('2001-01-05', 'on')
('2001-01-08', 'off'))

(显然只是一个伪代码示例(。

有没有一种方法可以在不使用光标的情况下连接这两个表，并得到以下结果：

Result (Date, Event, Bit) = (
('2001-01-01', NULL, 0)
('2001-01-02', NULL, 0)
('2001-01-03', NULL, 0)
('2001-01-04', NULL, 0)
('2001-01-05', 'on', 1)
('2001-01-06', NULL, 1)
('2001-01-07', NULL, 1)
('2001-01-08', 'off', 0)
('2001-01-09', NULL, 0)
('2001-01-10', NULL, 0)
('2001-01-11', NULL, 0)

我希望这是有道理的。显然，使用游标会很容易，但还有其他只使用联接的方法吗？

感谢大家抽出时间。

Answer 1

我认为这将帮助您

CREATE TABLE DateList(Period date) ;

CREATE TABLE EVENTS (Period date, Event varchar(5));

INSERT INTO Events(Period, Event)
VALUES ('2001-01-05', 'on'),
('2001-01-08', 'off');

INSERT INTO DateList(Period)
VALUES ('2001-01-01'),
('2001-01-02'),
('2001-01-03'),
('2001-01-04'),
('2001-01-05'),
('2001-01-06'),
('2001-01-07'),
('2001-01-08'),
('2001-01-09'),
('2001-01-10'),
('2001-01-11');

SELECT d.Period AS
START , j.Event,
CASE
WHEN e.Event='on' THEN 1
ELSE 0
END [Bit]
FROM DateList d
LEFT JOIN EVENTS j ON j.Period=d.Period
LEFT JOIN
(SELECT Period AS PeriodStart,
isnull(dateadd(DAY, -1, lead(Period, 1) over(
ORDER BY Period)), Period) PeriodEnd,
Event
FROM EVENTS) E ON d.Period BETWEEN e.PeriodStart AND e.PeriodEnd;

Answer 2

您也可以使用cross apply来简单地执行此操作：

create table DateList
(
Date date
);
create table Events
(
Date  date,
Event varchar(5)
);
insert into Events(Date, Event)
values ('2001-01-05', 'on'),
('2001-01-08', 'off');

insert into DateList(Date)
values ('2001-01-01'),
('2001-01-02'),
('2001-01-03'),
('2001-01-04'),
('2001-01-05'),
('2001-01-06'),
('2001-01-07'),
('2001-01-08'),
('2001-01-09'),
('2001-01-10'),
('2001-01-11');
select 
DateList.[Date], 
Event, 
[Bit]
from DateList 
left join Events
on Events.[Date] = DateList.[Date]
cross apply (
select 
sum(iif(Event = 'on', 1, -1)) 
from Events 
where Events.[Date] <= DateList.[Date]
) as t([Bit])
;

这在概念上类似于

select
DateList.[Date],
Event,
sum(
case Event 
when 'on' then 1 
when 'off' then -1 
end
) over (
order by DateList.[Date] 
rows between unbounded preceding and current row
) [Bit]
from DateList
left join Events
on Events.[Date] = DateList.[Date]

结果：

|       Date |  Event |    Bit |
|------------|--------|--------|
| 2001-01-01 | (null) | (null) |
| 2001-01-02 | (null) | (null) |
| 2001-01-03 | (null) | (null) |
| 2001-01-04 | (null) | (null) |
| 2001-01-05 |     on |      1 |
| 2001-01-06 | (null) |      1 |
| 2001-01-07 | (null) |      1 |
| 2001-01-08 |    off |      0 |
| 2001-01-09 | (null) |      0 |
| 2001-01-10 | (null) |      0 |
| 2001-01-11 | (null) |      0 |