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

user_333047 0
Viewed 41

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.

bit database-cursor date sql
2 Answers

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;
user_117360040

You can also use cross apply to do this fairly simply:

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])
;

This is conceptually similar to

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]

Result:

|       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 |
user_93114800
Related