question

Yassir-9592 avatar image
0 Votes"
Yassir-9592 asked MelissaMa-msft commented

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

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 !

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Yassir-9592

Could you please validate all the updated answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
Viorel-1 avatar image
1 Vote"
Viorel-1 answered Yassir-9592 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Viorel-1 it is a historical data , the date 2019-06-01 for 'AC-84 is correct i can have older events

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

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;
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@GuoxiongYuan-7218 Thanks for your help !!

Your query doesn't work in that case for example if i have Events_end<=2020.

As bellow an example :

i have that :

  insert into Events values(6,'AC_88','Event 5','2020-09-15','2020-11-15')

the output need to be

 6,'AC_88','Event 5','2020-01-01','2020-09-15'
 6,'AC_88','Event 5','2020-09-15','2020-11-15'
 6,'AC_88','Event 5','2020-11-15','2021-01-01'

All year need to be covered

0 Votes 0 ·

Not clear to your requirement for Events_end. Based on your output, it seems you want to add the record if Events_end is less than 2021-01-01. So I added the CTE_3.

0 Votes 0 ·
Yassir-9592 avatar image Yassir-9592 GuoxiongYuan-7218 ·

@GuoxiongYuan-7218 yes it works but not perfect it cause duplicate value .
For example if i have that rows :

 insert into Events values(7,'AC_89','Event 6','2020-01-01','2020-11-15')
 insert into Events values(8,'AC_89','Event 6','2020-11-15','2020-01-01')

the result need to be

 AC_89    Event 6    2020-01-01 00:00:00.000    2020-11-15 00:00:00.000    0
 AC_89    Event 6    2020-11-15 00:00:00.000    2020-01-01 00:00:00.000    0

.
Or with you query i have that

 AC_89    Event 6    2020-01-01 00:00:00.000    2020-11-15 00:00:00.000    0
 AC_89    Event 6    2020-11-15 00:00:00.000    2020-01-01 00:00:00.000    0
 AC_89    Event 6    2020-11-15 00:00:00.000    2021-01-01 00:00:00.000    1

I think the Cte 3 cause that issue .

Thanks for help


0 Votes 0 ·
Yassir-9592 avatar image Yassir-9592 GuoxiongYuan-7218 ·

@GuoxiongYuan-7218 it works but not perfectly because i have duplicate rows .

For example if i have a data like that :

 insert into Events values(7,'AC_89','Event 6','2020-01-01','2020-11-15')
 insert into Events values(8,'AC_89','Event 6','2020-11-15','2021-01-01')

the output with your query is

AC_89 Event 6 2020-01-01 00:00:00.000 2020-11-15 00:00:00.000 0
AC_89 Event 6 2020-11-15 00:00:00.000 2021-01-01 00:00:00.000 0
AC_89 Event 6 2021-01-01 00:00:00.000 2021-01-01 00:00:00.000 1

But the right output is that :

AC_89 Event 6 2020-01-01 00:00:00.000 2020-11-15 00:00:00.000 0
AC_89 Event 6 2020-11-15 00:00:00.000 2021-01-01 00:00:00.000 0

I think the Cte_3 cause that issue .

Thanks for help !

0 Votes 0 ·
Yassir-9592 avatar image Yassir-9592 GuoxiongYuan-7218 ·

@GuoxiongYuan-7218 it works but not perfectly because i have duplicate rows .

For example if i have a data like that :

 insert into Events values(7,'AC_89','Event 6','2020-01-01','2020-11-15')
 insert into Events values(8,'AC_89','Event 6','2020-11-15','2021-01-01')

the output with your query is

 AC_89    Event 6    2020-01-01 00:00:00.000    2020-11-15 00:00:00.000    0
 AC_89    Event 6    2020-11-15 00:00:00.000    2021-01-01 00:00:00.000    0
 AC_89    Event 6    2021-01-01 00:00:00.000    2021-01-01 00:00:00.000    1

But the right output is that :

 AC_89    Event 6    2020-01-01 00:00:00.000    2020-11-15 00:00:00.000    0
 AC_89    Event 6    2020-11-15 00:00:00.000    2021-01-01 00:00:00.000    0

I think the Cte_3 cause that issue .

Thanks for help !


0 Votes 0 ·
Show more comments

Did you try my updated query?

136395-image.png


0 Votes 0 ·
image.png (25.9 KiB)
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Yassir-9592 commented

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Viorel-1 Thanks for your help !!

Your query doesn't work in that case for example if i have Events_end<=2020.

As bellow an example :

i have that :


  insert into Events values(6,'AC_88','Event 5','2020-09-15','2020-11-15')

the output need to be

 6,'AC_88','Event 5','2020-01-01','2020-09-15'
 6,'AC_88','Event 5','2020-09-15','2020-11-15'
 6,'AC_88','Event 5','2020-11-15','2021-01-01'

All year need to be covered

Thanks

0 Votes 0 ·

The query seems to include these rows as expected:

 6    AC_88    Event 5    2020-01-01 00:00:00.000    2020-09-15 00:00:00.000    1
 6    AC_88    Event 5    2020-09-15 00:00:00.000    2020-11-15 00:00:00.000    0

It is not clear what rules to apply in order to include your third row too:

 6,'AC_88','Event 5','2020-11-15','2021-01-01'

Do you have some new requirements?

0 Votes 0 ·

@Viorel-1 the idea is covering all the year 2020 :

with your query i will get that result :

 6    AC_88    Event 5    2020-01-01 00:00:00.000    2020-09-15 00:00:00.000    1
  6    AC_88    Event 5    2020-09-15 00:00:00.000    2020-11-15 00:00:00.000    0

if you see i didn't covered between 2020-11-15 00:00:00.000 and the end of year o the first day of the next day . this the explanation about that line :
6,'AC_88','Event 5','2020-11-15','2021-01-01'

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered Yassir-9592 commented

Hi @Yassir-9592

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MelissaMa-msft Thanks for your help !!

Your query doesn't work in that case for example if i have Events_end<=2020.

As bellow an example :

i have that :


  insert into Events values(6,'AC_88','Event 5','2020-09-15','2020-11-15')

the output need to be

 6,'AC_88','Event 5','2020-01-01','2020-09-15'
 6,'AC_88','Event 5','2020-09-15','2020-11-15'
 6,'AC_88','Event 5','2020-11-15','2021-01-01'

All year need to be covered

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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



image.png (27.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

Check if the additional clarifications regarding the end of interval are solved by this query:

 declare @start_date date = '2020-01-01'
 declare @end_date date = '2021-01-01'
 ;
 with Q as
 (
     select *,
         lag(Events_end) over (partition by code_events order by Events_start) as pe,
         lead(Events_start) over (partition by code_events order by Events_start) as ns
     from Events
 )
 select id, code_events, Events, Events_start, Events_end, 0 as IsMissing
 from Q where Events_start between @start_date and @end_date
           or @start_date between Events_start and Events_end
 union all
 select id, code_events, Events, isnull(pe, @start_date), iif(Events_start < @end_date, Events_start, @end_date), 1 as IsMissing
 from Q where Events_start > @start_date
 union all
 select id, code_events, Events, iif(Events_end > @start_date, Events_end, @start_date), @end_date, 1 as IsMissing
 from Q where Events_end < @end_date and ns is null
 order by code_events, Events_start

The @start_date and @end_date defines the watched interval.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @Yassir-9592

I inserted below rows into your table.

 insert into Events values(6,'AC_88','Event 5','2020-09-15','2020-11-15')
 insert into Events values(7,'AC_88','Event 5','2020-12-15','2020-12-31')

Please refer below updated query and check whether it is working.

 declare @startdate date = '2020-01-01'
 declare @enddate date = '2021-01-01'
    
 ;with cte as (select ROW_NUMBER() over (partition by code_events,Events order by Events_start) rn
 , code_events,events,Events_start,Events_end from Events)
 ,cte1 as (
 select min(Events_start) over (partition by code_events,Events) mindate,
 max(Events_end) over (partition by code_events,Events ) maxdate,code_events,events
 from Events)
 select code_events,Events,Events_start,Events_end,0 as IsMissing from cte 
 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 
 union
 select  a.code_events,a.Events,@startdate,Events_start,1
 from cte a
 left join cte1 b 
 on a.code_events=b.code_events and a.Events=b.Events
 where rn=1 and mindate>@startdate
 union 
 select  a.code_events,a.Events,a.Events_end,@enddate,1
 from cte a
 left join cte1 b 
 on a.code_events=b.code_events and a.Events=b.Events
 inner join (select max(rn) max,code_events,Events from cte group by code_events,Events) c
 on a.rn=c.max and a.code_events=c.code_events and a.Events=c.Events
 where maxdate<@enddate 

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
 AC_88    Event 5    2020-01-01 00:00:00.000    2020-09-15 00:00:00.000    1
 AC_88    Event 5    2020-09-15 00:00:00.000    2020-11-15 00:00:00.000    0
 AC_88    Event 5    2020-11-15 00:00:00.000    2020-12-15 00:00:00.000    1
 AC_88    Event 5    2020-12-15 00:00:00.000    2020-12-31 00:00:00.000    0
 AC_88    Event 5    2020-12-31 00:00:00.000    2021-01-01 00:00:00.000    1

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 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.