How to flip a bit switch based on events in date sequence without using SQL CURSOR?

Question

I have a list of dates, with occasional events that correspond to start and stop dates for periods, and I want to flip a bit switch for all dates which occur between start and stop.

Example:

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'))

(Obviously just a pseudocode example).

Is there a way to join these two tables without using a cursor and get the following result:

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)

I hope this makes sense. Obviously would be easy with a cursor, but is there another way using only joins?

Thanks everyone for your time.

Answer 1

I think this will help you

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;