How to return a range value based on the first day of year and the value of row

Yassir 201 Reputation points
2021-09-28T19:22:19.07+00:00

Hello,

i have a table called Events, the value of that table like as bellow :

create table Events (
    id int,
    code_events varchar(10),
    Events varchar(10),
    Events_start datetime,
    Events_end datetime
 )

 insert into Events values(1,'AC_83','Event 1','2020-07-15','2020-07-30')
 insert into Events values(2,'AC_84','Event 2','2019-06-01','2030-07-30')
 insert into Events values(3,'AC_86','Event 3','2020-07-15','2022-12-15')
 insert into Events values(4,'AC_83','Event 1','2020-09-15','2025-04-30') 
 insert into Events values(5,'AC_87','Event 4','2021-07-15','2022-12-15')

I want to return a range of missing dates from the first date of 2020 and only if Events_start>=2020

As bellow what i want to get :

code_events    Events    Events_start    Events_end                       IS_Messing
 AC_83    Event 1    2020-01-01 00:00:00.000    2020-07-15 00:00:00.000   1
 AC_83    Event 1    2020-07-15 00:00:00.000    2020-07-30 00:00:00.000   0
 AC_83    Event 1    2020-07-30 00:00:00.000    2020-09-15 00:00:00.000   1  
 AC_83    Event 1    2020-09-15 00:00:00.000    2025-04-30 00:00:00.000   0
 AC_84    Event 2    2019-06-01 00:00:00.000    2030-07-30 00:00:00.000   0
 AC_86    Event 3    2020-01-01 00:00:00.000    2020-07-15 00:00:00.000   1
 AC_86    Event 3    2020-07-15 00:00:00.000    2022-12-15 00:00:00.000   0
 AC_87    Event 4    2020-01-01 00:00:00.000    2021-07-15 00:00:00.000   1
 AC_87    Event 4    2021-07-15 00:00:00.000    2022-12-15 00:00:00.000   0

How can i do that ?

Thanks for help !

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

7 answers

Sort by: Most helpful
  1. Viorel 114.4K Reputation points
    2021-09-28T20:49:01.013+00:00

    Try this query too:

    declare @start_date date = '2020-01-01'
    
    ;
    with Q1 as
    (
        select id, code_events, Events, 
            case when Events_start < @start_date then @start_date else Events_start end as Events_start,
            Events_end
        from Events
        where Events_end > @start_date
    ),
    Q2 as
    (
        select *,
            isnull(lag(Events_end) over (partition by code_events order by Events_start), @start_date) pe
        from Q1
    )
    select id, code_events, Events, Events_start, Events_end, IsMissing=0
    from Q2
    union all
    select id, code_events, Events, pe, Events_start, IsMissing=1
    from Q2 where pe < Events_start
    order by code_events, Events_start
    

    It is not clear why your sample results have 2019-06-01 for 'AC-84'.

    This approach assumes that there are no intersections between corresponding intervals.

    1 person found this answer helpful.

  2. Guoxiong 8,201 Reputation points
    2021-09-28T22:45:26.813+00:00

    How about this solution:

    ;WITH CTE_1 AS (
        SELECT code_events, Events, '2020-01-01' AS Events_start, MIN(Events_start) AS Events_end, 1 AS IS_Messing
        FROM Events
        GROUP BY code_events, Events
        HAVING MIN(Events_start) > '2020-01-01'
    ),
    CTE_2 AS (
        SELECT e1.code_events, e1.Events, e1.Events_end AS Events_start, e2.Events_start AS Events_end, 1 AS IS_Messing
        FROM Events AS e1
        LEFT JOIN Events AS e2 ON e1.Events = e2.Events AND e1.Events_end <> e2.Events_start AND e1.Events_start <> e2.Events_start
        WHERE e2.Events_start > e1.Events_start
    ),
    CTE_3 AS (
        SELECT code_events, Events, MAX(Events_end) AS Events_start, '2021-01-01' AS Events_end, 1 AS IS_Messing
        FROM Events
        GROUP BY code_events, Events
        HAVING MIN(Events_end) < '2021-01-01'
    )
    
    SELECT code_events, Events, Events_start, Events_end, 0 AS IS_Messing
    FROM Events
    UNION ALL
    SELECT code_events, Events, Events_start, Events_end, IS_Messing
    FROM CTE_1
    UNION ALL
    SELECT code_events, Events, Events_start, Events_end, IS_Messing
    FROM CTE_2
    UNION ALL
    SELECT code_events, Events, Events_start, Events_end, IS_Messing
    FROM CTE_3
    ORDER BY code_events, Events, Events_start;
    

  3. Viorel 114.4K Reputation points
    2021-09-29T00:16:04.76+00:00

    If the intervals do not have to be cut as 2020-01-01, then check this variant too:

    declare @start_date date = '2020-01-01'
    ;
    with Q as
    (
        select *, isnull(lag(Events_end) over (partition by code_events order by Events_start), @start_date) as pe
        from Events
        where Events_end > @start_date
    )
    select id, code_events, Events, Events_start, Events_end, 0 as IsMissing
    from Q
    union all
    select id, code_events, Events, pe, Events_start, 1
    from Q where pe < Events_start
    order by code_events, Events_start
    

  4. MelissaMa-MSFT 24,181 Reputation points
    2021-09-29T02:17:41.397+00:00

    Hi @Yassir

    If you have a very old version of SQL Server, please also refer below:

    declare @date date = '2020-01-01'  
    ;with cte as (  
    select ROW_NUMBER() over (partition by code_events order by Events_start) rn,*  
    from Events)  
    select code_events,Events,Events_start,Events_end,0 as IsMissing from cte   
    union  
    select code_events,Events,@date,Events_start,1 from cte   
    where rn=1 and Events_start>@date  
    union  
    select a.code_events,a.Events,a.Events_end,b.Events_start,1 from cte a  
    left join cte b on a.code_events=b.code_events and a.Events=b.Events  
    where a.rn=b.rn-1   
    

    Output:

    code_events	Events	Events_start	Events_end	IsMissing  
    AC_83	Event 1	2020-01-01 00:00:00.000	2020-07-15 00:00:00.000	1  
    AC_83	Event 1	2020-07-15 00:00:00.000	2020-07-30 00:00:00.000	0  
    AC_83	Event 1	2020-07-30 00:00:00.000	2020-09-15 00:00:00.000	1  
    AC_83	Event 1	2020-09-15 00:00:00.000	2025-04-30 00:00:00.000	0  
    AC_84	Event 2	2019-06-01 00:00:00.000	2030-07-30 00:00:00.000	0  
    AC_86	Event 3	2020-01-01 00:00:00.000	2020-07-15 00:00:00.000	1  
    AC_86	Event 3	2020-07-15 00:00:00.000	2022-12-15 00:00:00.000	0  
    AC_87	Event 4	2020-01-01 00:00:00.000	2021-07-15 00:00:00.000	1  
    AC_87	Event 4	2021-07-15 00:00:00.000	2022-12-15 00:00:00.000	0  
    

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  5. Guoxiong 8,201 Reputation points
    2021-09-29T21:34:17.313+00:00

    In order to be clear, here is the updated query:

    DECLARE @Events TABLE (  
    	id int,  
        code_events varchar(10),  
        Events varchar(10),  
        Events_start datetime,  
        Events_end datetime  
    );  
      
    INSERT INTO @Events VALUES   
    (1,'AC_83','Event 1','2020-07-15','2020-07-30'),  
    (2,'AC_84','Event 2','2019-06-01','2030-07-30'),  
    (3,'AC_86','Event 3','2020-07-15','2022-12-15'),  
    (4,'AC_83','Event 1','2020-09-15','2025-04-30'),  
    (3,'AC_87','Event 4','2021-07-15','2022-12-15'),  
    (6,'AC_88','Event 5','2020-09-15','2020-11-15'),  
    (7,'AC_89','Event 6','2020-01-01','2020-11-15'),  
    (8,'AC_89','Event 6','2020-11-15','2021-01-01');  
      
    ;WITH CTE_1 AS (  
    	SELECT code_events, Events, '2020-01-01' AS Events_start, MIN(Events_start) AS Events_end, 1 AS IS_Messing  
    	FROM @Events  
    	--WHERE Events_start >= '2020-01-01'  
    	GROUP BY code_events, Events  
    	HAVING MIN(Events_start) > '2020-01-01'  
    ),  
    CTE_2 AS (  
    	SELECT e1.code_events, e1.Events, e1.Events_end AS Events_start, e2.Events_start AS Events_end, 1 AS IS_Messing  
    	FROM @Events AS e1  
    	LEFT JOIN @Events AS e2 ON e1.Events = e2.Events AND e1.Events_end <> e2.Events_start AND e1.Events_start <> e2.Events_start  
    	WHERE e2.Events_start > e1.Events_start  
    --ORDER BY e1.code_events  
    ),  
      
    CTE_3 AS (  
    	SELECT code_events, Events, MAX(Events_end) AS Events_start, '2021-01-01' AS Events_end, 1 AS IS_Messing  
    	FROM @Events  
    	--WHERE Events_start >= '2020-01-01'  
    	GROUP BY code_events, Events  
    	HAVING MAX(Events_end) < '2021-01-01'  
    )  
      
    SELECT code_events, Events, Events_start, Events_end, 0 AS IS_Messing  
    FROM @Events  
    UNION ALL  
    SELECT code_events, Events, Events_start, Events_end, IS_Messing  
    FROM CTE_1  
    UNION ALL  
    SELECT code_events, Events, Events_start, Events_end, IS_Messing  
    FROM CTE_2  
    UNION ALL  
    SELECT code_events, Events, Events_start, Events_end, IS_Messing  
    FROM CTE_3  
    ORDER BY code_events, Events, Events_start;  
    

    Output:

    136396-image.png

    0 comments No comments