Find n consecutive date

Question

Find n consecutive date

Zaran 21

Hi,

Could you please help me how to find 8 calendar days in a row(Consecutive date) that have absenceDate(taking into consideration Saturday and Sunday)?
Here is the sample data of large table.

Create table test (Id int,caseid varchar(20),S_AbsenceDate DateTime,E_AbsenceDate DateTime)
Insert into test (caseid,S_AbsenceDate,E_AbsenceDate)
values('F12','2022-03-7','2022-03-8')
,values('F12','2022-03-11','2022-03-11')
,values('F12','2022-03-14','2022-03-14')
,values('F12','2022-03-15','2022-03-15')
,values('F12','2022-03-18','2022-03-18')
,values('F13','2022-03-18','2022-03-21')
,values('F13','2022-03-23','2022-03-23')
,values('F13','2022-03-24','2022-03-25')
,values('F13','2022-03-28','2022-03-31')
,values('F14','2022-03-22','2022-03-22')
,values('F15','2022-03-4','2022-03-4')
,values('F15','2022-03-7','2022-03-7')
,values('F15','2022-03-8','2022-03-8')
,values('F15','2022-03-9','2022-03-9')
,values('F15','2022-03-10','2022-03-10)
,values('F15','2022-03-11','2022-03-11')
,values('F15','2022-03-14','2022-03-14')
,values('F15','2022-03-15','2022-03-15')
,values('F15','2022-03-16','2022-03-16')

Result shoulb be:
Caseid, CNT,S_AbsenceDate,E_AbsenceDate

F13,9,2022-03-23,2022-03-31
F15,10,2022-03-7,2022-3-16

Thank you

0 comments

Answer accepted by question author

LiHong-MSFT 10,061

Hi @Zaran
Please check this query:

;WITH CTE1 AS  
(  
 SELECT *,CASE WHEN DATEPART(weekday,E_AbsenceDate) = 6 THEN DATEADD(DAY,2,E_AbsenceDate) ELSE E_AbsenceDate END AS E_AbsenceDate_R  
 FROM test  
),CTE2 AS  
(  
 SELECT Id,caseid,S_AbsenceDate,E_AbsenceDate_R ,LEAD(S_AbsenceDate,1,DATEADD(DAY,2,E_AbsenceDate_R))OVER(PARTITION BY caseid ORDER BY S_AbsenceDate) AS LEAD_S_AbsenceDate  
 FROM CTE1  
),CTE3 AS  
(  
 SELECT *,CASE WHEN DATEDIFF(DAY,E_AbsenceDate_R,LEAD_S_AbsenceDate)>1 THEN 1 ELSE 0 END AS PART  
 FROM CTE2  
),CTE4 AS  
(  
 SELECT Id,caseid,S_AbsenceDate,E_AbsenceDate_R ,SUM(PART)OVER(ORDER BY caseid ASC,S_AbsenceDate DESC) AS PART  
 FROM CTE3  
)  
SELECT caseid,DATEDIFF(DAY,MIN(S_AbsenceDate),MAX(E_AbsenceDate_R))+1 AS CNT,MIN(S_AbsenceDate)AS S_AbsenceDate,MAX(E_AbsenceDate_R)AS E_AbsenceDate  
FROM CTE4  
GROUP BY caseid,PART  
HAVING DATEDIFF(DAY,MIN(S_AbsenceDate),MAX(E_AbsenceDate_R))+1>=8  
ORDER BY caseid,S_AbsenceDate

Output:

The final output is somewhat different from your desired result. Considering that March 5th and 6th are weekends, I think the absence date of F15 should be calculated from March 4th.

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Zaran 21 Reputation points

2022-04-20T17:31:05.393+00:00

Thank you, LiHong,

It works for me :)
Zaran 21 Reputation points

2022-04-20T17:45:19.47+00:00

Hi LiHong,

The only thing from you query didn't consider is:
If the last day of the E_AbsenceDate is Firday and there is no more date for the caseid, it will calculate weekend while for this situation we don't need to consider the weekend, because there is no more records for it.
Can you please fix it in your code?

Thank you so much.

LiHong-MSFT 10,061

Hi @Zaran
For example, for caseid F12 ,E_AbsenceDate '2022-03-18' is Friday and it is the last day for caseid F12. When calculating this ,wo don't need to consider weekend, so the consecutive AbsenceDate should be 1day, not 3 days.
If I am right,then check this:

 ;WITH CTE AS  
 (  
  SELECT *,ROW_NUMBER()OVER(PARTITION BY caseid ORDER BY E_AbsenceDate DESC)AS RNum  
  FROM #test  
 ),CTE1 AS  
 (  
  SELECT *,CASE WHEN DATEPART(weekday,E_AbsenceDate) = 6 AND RNum > 1 THEN DATEADD(DAY,2,E_AbsenceDate) ELSE E_AbsenceDate END AS E_AbsenceDate_R  
  FROM CTE  
 ),CTE2 AS(  
  SELECT Id,caseid,S_AbsenceDate,E_AbsenceDate_R ,LEAD(S_AbsenceDate)OVER(PARTITION BY caseid ORDER BY S_AbsenceDate) AS LEAD_S_AbsenceDate  
  FROM CTE1  
 ),CTE3 AS(  
  SELECT *,CASE WHEN DATEDIFF(DAY,E_AbsenceDate_R,LEAD_S_AbsenceDate)=1 THEN 0 ELSE 1 END AS PART  
  FROM CTE2  
 ),CTE4 AS  
 (  
  SELECT Id,caseid,S_AbsenceDate,E_AbsenceDate_R ,SUM(PART)OVER(ORDER BY caseid ASC,S_AbsenceDate DESC) AS PART  
  FROM CTE3  
 )  
 SELECT caseid,DATEDIFF(DAY,MIN(S_AbsenceDate),MAX(E_AbsenceDate_R))+1 AS CNT,MIN(S_AbsenceDate)AS S_AbsenceDate,MAX(E_AbsenceDate_R)AS E_AbsenceDate  
 FROM CTE4  
 GROUP BY caseid,PART  
 HAVING DATEDIFF(DAY,MIN(S_AbsenceDate),MAX(E_AbsenceDate_R))+1>=8  
 ORDER BY caseid,S_AbsenceDate

Best regards,
LiHong

Zaran 21 Reputation points

2022-04-21T15:29:43.987+00:00

Thank you, LiHong,

Yes, You are right.

Query doesn't work correctly and gave me wrong result, and also many rows for one Caseid.

I appreciated it if you can review it again.

6 additional answers

Your answer

Zaran 21 Reputation points

2022-04-20T17:31:05.393+00:00

Thank you, LiHong,

It works for me :)
Zaran 21 Reputation points

2022-04-20T17:45:19.47+00:00

Hi LiHong,

The only thing from you query didn't consider is:
If the last day of the E_AbsenceDate is Firday and there is no more date for the caseid, it will calculate weekend while for this situation we don't need to consider the weekend, because there is no more records for it.
Can you please fix it in your code?

Thank you so much.
LiHong-MSFT 10,061 Reputation points

2022-04-21T01:47:56.233+00:00

Hi @Zaran
For example, for caseid F12 ,E_AbsenceDate '2022-03-18' is Friday and it is the last day for caseid F12. When calculating this ,wo don't need to consider weekend, so the consecutive AbsenceDate should be 1day, not 3 days.
If I am right,then check this:

;WITH CTE AS ( SELECT *,ROW_NUMBER()OVER(PARTITION BY caseid ORDER BY E_AbsenceDate DESC)AS RNum FROM #test ),CTE1 AS ( SELECT *,CASE WHEN DATEPART(weekday,E_AbsenceDate) = 6 AND RNum > 1 THEN DATEADD(DAY,2,E_AbsenceDate) ELSE E_AbsenceDate END AS E_AbsenceDate_R FROM CTE ),CTE2 AS( SELECT Id,caseid,S_AbsenceDate,E_AbsenceDate_R ,LEAD(S_AbsenceDate)OVER(PARTITION BY caseid ORDER BY S_AbsenceDate) AS LEAD_S_AbsenceDate FROM CTE1 ),CTE3 AS( SELECT *,CASE WHEN DATEDIFF(DAY,E_AbsenceDate_R,LEAD_S_AbsenceDate)=1 THEN 0 ELSE 1 END AS PART FROM CTE2 ),CTE4 AS ( SELECT Id,caseid,S_AbsenceDate,E_AbsenceDate_R ,SUM(PART)OVER(ORDER BY caseid ASC,S_AbsenceDate DESC) AS PART FROM CTE3 ) SELECT caseid,DATEDIFF(DAY,MIN(S_AbsenceDate),MAX(E_AbsenceDate_R))+1 AS CNT,MIN(S_AbsenceDate)AS S_AbsenceDate,MAX(E_AbsenceDate_R)AS E_AbsenceDate FROM CTE4 GROUP BY caseid,PART HAVING DATEDIFF(DAY,MIN(S_AbsenceDate),MAX(E_AbsenceDate_R))+1>=8 ORDER BY caseid,S_AbsenceDate

Best regards,
LiHong
Zaran 21 Reputation points

2022-04-21T15:29:43.987+00:00

Thank you, LiHong,

Yes, You are right.

Query doesn't work correctly and gave me wrong result, and also many rows for one Caseid.

I appreciated it if you can review it again.

Answer 1

Zaran 21

Hi Lihang,

Thank you very much for your help.

Please consider the values below, and review the code with the values below.

Create table test (Id int identity(1,1)
,caseid varchar(20)
,S_AbsenceDate DateTime
,E_AbsenceDate DateTime)
Insert into test (caseid,S_AbsenceDate,E_AbsenceDate)
values ('F12','2022-03-7','2022-03-8')
, ('F12','2022-03-11','2022-03-11')
, ('F12','2022-03-14','2022-03-14')
, ('F12','2022-03-15','2022-03-15')
, ('F12','2022-03-18','2022-03-18')
, ('F13','2022-03-18','2022-03-21')
, ('F13','2022-03-23','2022-03-23')
, ('F13','2022-03-24','2022-03-25')
, ('F13','2022-03-28','2022-03-31')
, ('F13','2022-04-01','2022-04-1')
, ('F14','2022-03-22','2022-03-22')
, ('F15','2022-03-4','2022-03-4')
, ('F15','2022-03-7','2022-03-7')
, ('F15','2022-03-8','2022-03-8')
, ('F15','2022-03-9','2022-03-9')
, ('F15','2022-03-10','2022-03-10')
, ('F15','2022-03-11','2022-03-11')
, ('F15','2022-03-14','2022-03-14')
, ('F15','2022-03-15','2022-03-15')
, ('F15','2022-03-16','2022-03-16')
, ('F15','2022-03-16','2022-03-17')
, ('F15','2022-03-16','2022-03-18')
, ('F15','2022-03-16','2022-03-19')

I have added records for the F13 and F15. I have checked your code with the above values and the result is not correct for the F15. the result form your codes is :
caseid CNT S_AbsenceDate E_AbsenceDate
F13 10 2022-03-23 00:00:00.000 2022-04-01 00:00:00.000
F15 17 2022-03-04 00:00:00.000 2022-03-20 00:00:00.000

and the correct result should be:
caseid CNT S_AbsenceDate E_AbsenceDate
F13 10 2022-03-23 00:00:00.000 2022-04-01 00:00:00.000
F15 17 2022-03-04 00:00:00.000 2022-03-19 00:00:00.000

LiHong-MSFT 10,061

Hi Zaran
Please check this:

;WITH CTE AS
(
 SELECT *,ROW_NUMBER()OVER(PARTITION BY caseid ORDER BY E_AbsenceDate DESC)AS RNum
 FROM test
),CTE1 AS
(
 SELECT *,CASE WHEN DATEPART(weekday,E_AbsenceDate) IN(6,7) AND RNum > 1 THEN DATEADD(DAY,2,E_AbsenceDate) ELSE E_AbsenceDate END AS E_AbsenceDate_R
 FROM CTE
),CTE2 AS
(
 SELECT Id,caseid,S_AbsenceDate,E_AbsenceDate,E_AbsenceDate_R ,LEAD(S_AbsenceDate,1,DATEADD(DAY,2,E_AbsenceDate_R))OVER(PARTITION BY caseid ORDER BY S_AbsenceDate,E_AbsenceDate_R) AS LEAD_S_AbsenceDate
 FROM CTE1
),CTE3 AS
(
 SELECT *,CASE WHEN DATEDIFF(DAY,E_AbsenceDate_R,LEAD_S_AbsenceDate)> 1 THEN 1 ELSE 0 END AS PART
 FROM CTE2
),CTE4 AS
(
 SELECT Id,caseid,S_AbsenceDate,E_AbsenceDate,E_AbsenceDate_R ,SUM(PART)OVER(ORDER BY caseid ASC,S_AbsenceDate DESC) AS PART
 FROM CTE3
)
SELECT caseid,DATEDIFF(DAY,MIN(S_AbsenceDate),MAX(E_AbsenceDate_R))+1 AS CNT,MIN(S_AbsenceDate)AS S_AbsenceDate,MAX(E_AbsenceDate)AS E_AbsenceDate
FROM CTE4
GROUP BY caseid,PART
HAVING DATEDIFF(DAY,MIN(S_AbsenceDate),MAX(E_AbsenceDate_R))+1>=8
ORDER BY caseid,S_AbsenceDate

Best regards,
LiHong

Zaran 21 Reputation points

2022-04-24T15:03:02.853+00:00

Thank you LiHong,

The result shows wrong result:

caseid CNT S_AbsenceDate E_AbsenceDate
F12 12 2022-03-07 00:00:00.000 2022-03-18 00:00:00.000
F13 15 2022-03-18 00:00:00.000 2022-04-01 00:00:00.000
F13 9 2022-03-23 00:00:00.000 2022-03-31 00:00:00.000
F15 16 2022-03-04 00:00:00.000 2022-03-19 00:00:00.000

and correct result should be:
caseid CNT S_AbsenceDate E_AbsenceDate
F13 10 2022-03-23 00:00:00.000 2022-04-01 00:00:00.000
F15 16 2022-03-04 00:00:00.000 2022-03-18 00:00:00.000:

Answer 2

LiHong-MSFT 10,061

How about this:

 ;WITH CTE AS
 (
  SELECT *,ROW_NUMBER()OVER(PARTITION BY caseid ORDER BY E_AbsenceDate DESC)AS RNum
  FROM test
 ),CTE1 AS
 (
  SELECT *,CASE WHEN DATEPART(weekday,E_AbsenceDate) = 6 AND RNum > 1 THEN DATEADD(DAY,2,E_AbsenceDate) ELSE E_AbsenceDate END AS E_AbsenceDate_R
  FROM CTE
 ),CTE2 AS(
  SELECT Id,caseid,S_AbsenceDate,E_AbsenceDate_R ,LEAD(S_AbsenceDate,1,DATEADD(DAY,2,E_AbsenceDate_R))OVER(PARTITION BY caseid ORDER BY S_AbsenceDate) AS LEAD_S_AbsenceDate
  FROM CTE1
 ),CTE3 AS(
  SELECT *,CASE WHEN DATEDIFF(DAY,E_AbsenceDate_R,LEAD_S_AbsenceDate)> 1 THEN 1 ELSE 0 END AS PART
  FROM CTE2
 ),CTE4 AS
 (
  SELECT Id,caseid,S_AbsenceDate,E_AbsenceDate_R ,SUM(PART)OVER(ORDER BY caseid ASC,S_AbsenceDate DESC) AS PART
  FROM CTE3
 )
 SELECT caseid,DATEDIFF(DAY,MIN(S_AbsenceDate),MAX(E_AbsenceDate_R))+1 AS CNT,MIN(S_AbsenceDate)AS S_AbsenceDate,MAX(E_AbsenceDate_R)AS E_AbsenceDate
 FROM CTE4
 GROUP BY caseid,PART
 HAVING DATEDIFF(DAY,MIN(S_AbsenceDate),MAX(E_AbsenceDate_R))+1>=8
 ORDER BY caseid,S_AbsenceDate

I test this code using the sample datas you gave,and it runs well. If it didn't work ,then you need to update your sample datas so that we can provide a tested, verifiable solution to your issue.

Best regards,
LiHong

Zaran 21 Reputation points

2022-04-22T02:35:41.647+00:00

Hi LiHang,

I couldn't put my comments here, so I had to put my comments as of Answer box.
Please check it
Thank you so much
Zaran 21 Reputation points

2022-04-22T02:49:41.75+00:00

Hi Lihang,

Thank you very much for your help.
I have added records for the F13 and F15. and run your query, the result is not correct for the F15.

, ('F13','2022-04-01','2022-04-1')
, ('F15','2022-03-16','2022-03-17')
, ('F15','2022-03-18','2022-03-18')
, ('F15','2022-03-19','2022-03-19')

the result form your codes is :
caseid CNT S_AbsenceDate E_AbsenceDate
F13 10 2022-03-23 00:00:00.000 2022-04-01 00:00:00.000
F15 17 2022-03-04 00:00:00.000 2022-03-20 00:00:00.000

and the correct result should be:
caseid CNT S_AbsenceDate E_AbsenceDate
F13 10 2022-03-23 00:00:00.000 2022-04-01 00:00:00.000
F15 17 2022-03-04 00:00:00.000 2022-03-19 00:00:00.000
Zaran 21 Reputation points

2022-04-22T02:57:13.483+00:00

Hi Lihang,

Thank you very much for your help.
I have added records for the F13 and F15. and run your query, the result is not correct for the F15.

, ('F13','2022-04-01','2022-04-1')
, ('F15','2022-03-16','2022-03-17')
, ('F15','2022-03-18','2022-03-18')
, ('F15','2022-03-19','2022-03-19')

the result form your codes is :
caseid CNT S_AbsenceDate E_AbsenceDate
F13 10 2022-03-23 00:00:00.000 2022-04-01 00:00:00.000
F15 17 2022-03-04 00:00:00.000 2022-03-20 00:00:00.000

and the correct result should be:
caseid CNT S_AbsenceDate E_AbsenceDate
F13 10 2022-03-23 00:00:00.000 2022-04-01 00:00:00.000
F15 17 2022-03-04 00:00:00.000 2022-03-19 00:00:00.000

Answer 3

Hi @Zaran
I did a local test , my out put is a little different from yours.I wonder if your desire output is right ,because
'F15','2022-03-4','2022-03-4')
,('F15','2022-03-7','2022-03-7')
the two dates are consecutive，isn't it ？
Below is my test code：

CREATE TABLE TABLETEMP  
 (Id int,caseid varchar(20),S_AbsenceDate DateTime,E_AbsenceDate DateTime)  
  
  
declare @STOcaseid varchar(20)='F12'  
declare @STOSTARTDATE DateTime='2022-03-7'  
declare @STOENDDATE DateTime ='2022-03-8'  
declare @caseid varchar(20)  
declare @STARTDATE DateTime  
declare @ENDDATE DateTime  
declare @currentrow int = 0  
DECLARE DB_CURSOR CURSOR FOR  
SELECT caseid,S_AbsenceDate,E_AbsenceDate FROM test  
  
OPEN DB_CURSOR  
FETCH NEXT FROM DB_CURSOR INTO @caseid,@STARTDATE,@ENDDATE  
 set @currentrow =@currentrow+1  
FETCH NEXT FROM DB_CURSOR INTO @caseid,@STARTDATE,@ENDDATE  
 set @currentrow =@currentrow+1  
PRINT @STARTDATE  
WHILE @@FETCH_STATUS = 0  
BEGIN    
   set @currentrow =@currentrow+1  
  
   IF (@STOcaseid = @caseid)  
   BEGIN  
      DECLARE @diffdays INT  = (SELECT DATEDIFF(DAY,@STOENDDATE,@STARTDATE))  
  
   declare @lagweekday int = (select DATEPART(WEEKDAY,@STOENDDATE))  
   declare @currentweekday int = (select DATEPART(WEEKDAY,@STARTDATE))  
   print @currentrow  
   print @diffdays   
   print @lagweekday  
   print @currentweekday  
  
      IF (@diffdays =0 OR @diffdays = 1 OR (@lagweekday = 6 AND @currentweekday = 2))  
   BEGIN  
       print 'COMBINE'  
       set @STOENDDATE = @ENDDATE  
     
   END   
   ELSE  
   BEGIN  
     print 'APART DATE INSERT'  
     INSERT INTO TABLETEMP(Id, caseid,S_AbsenceDate,E_AbsenceDate)  
        VALUES(1,@STOcaseid,@STOSTARTDATE,@STOENDDATE)  
   set @STOcaseid = @caseid  
   set @STOSTARTDATE = @STARTDATE  
      set @STOENDDATE = @ENDDATE  
   END  
   END  
   ELSE   
   BEGIN  
     print 'APART CASE INSERT'  
    INSERT INTO TABLETEMP(Id, caseid,S_AbsenceDate,E_AbsenceDate)  
        VALUES(1,@STOcaseid,@STOSTARTDATE,@STOENDDATE)  
      set @STOcaseid = @caseid  
   set @STOSTARTDATE = @STARTDATE  
      set @STOENDDATE = @ENDDATE  
   END    
  
   FETCH NEXT FROM DB_CURSOR INTO @caseid,@STARTDATE,@ENDDATE  
END  
print 'LAST ROW INSERT'  
 INSERT INTO TABLETEMP(Id, caseid,S_AbsenceDate,E_AbsenceDate)  
        VALUES(1,@STOcaseid,@STOSTARTDATE,@STOENDDATE)  
CLOSE DB_CURSOR  
DEALLOCATE DB_CURSOR

Result preview:

WITH CTE AS(SELECT *,CONSECUTIVEDAYS = DATEDIFF(DAY,S_AbsenceDate,E_AbsenceDate) FROM TABLETEMP)  
SELECT * FROM CTE WHERE CONSECUTIVEDAYS >=8

Result preview:

Best Regards,
Isabella

Zaran 21 Reputation points

2022-04-20T17:32:45.233+00:00

Hi Isabella,

I didn't check your query, I see the result is not correct.
Thank you to participate.

Answer 4

Create table test (Id int identity(1,1)
,caseid varchar(20)
,S_AbsenceDate DateTime
,E_AbsenceDate DateTime)
Insert into test (caseid,S_AbsenceDate,E_AbsenceDate)
values ('F12','2022-03-7','2022-03-8')
, ('F12','2022-03-11','2022-03-11')
, ('F12','2022-03-14','2022-03-14')
, ('F12','2022-03-15','2022-03-15')
, ('F12','2022-03-18','2022-03-18')
, ('F13','2022-03-18','2022-03-21')
, ('F13','2022-03-23','2022-03-23')
, ('F13','2022-03-24','2022-03-25')
, ('F13','2022-03-28','2022-03-31')
, ('F14','2022-03-22','2022-03-22')
, ('F15','2022-03-4','2022-03-4')
, ('F15','2022-03-7','2022-03-7')
, ('F15','2022-03-8','2022-03-8')
, ('F15','2022-03-9','2022-03-9')
, ('F15','2022-03-10','2022-03-10')
, ('F15','2022-03-11','2022-03-11')
, ('F15','2022-03-14','2022-03-14')
, ('F15','2022-03-15','2022-03-15')
, ('F15','2022-03-16','2022-03-16')

declare @minDate date=(select min(S_AbsenceDate) from test)
declare @maxDate date=(select max(E_AbsenceDate) from test)
--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
Union all
Select n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,myDateCTE as
(

Select dateadd(day,n-1, @minDate ) dt from Nums
Where dateadd(day,n-1, @minDate )<= @maxDate

)

, mycte as (
select dt, caseid, row_number() Over(Partition by caseid order by dt) rn1 from
(select distinct caseid from test) t1, myDateCTE
)

,mycte1 as (
SELECT caseid, dt, rn1, row_number() Over(Partition by caseid order by dt) rn2
FROM mycte t1
WHERE EXISTS
(SELECT * FROM test t2
WHERE (t1.dt BETWEEN t2.S_AbsenceDate AND t2.E_AbsenceDate and t1.caseid=t2.caseid)
or (datepart(weekday,t1.dt) in (1,7))) )

Select caseid, 1+datediff(day,Min(m.dt),Max(m.dt)) as CNT, Min(m.dt) As S_AbsenceDate, Max(m.dt) As E_AbsenceDate
From mycte1 m
Group By caseid, m.rn1 - m.rn2
having(datediff(day,Min(m.dt),Max(m.dt))>7)
Order by caseid

drop table test

/*
caseid CNT S_AbsenceDate E_AbsenceDate
F13 9 2022-03-23 2022-03-31
F15 13 2022-03-04 2022-03-16

*/

Zaran 21 Reputation points

2022-04-20T17:32:02.18+00:00

Hi Jing,

I didn't check your query, i will chek and let you know.
Thank you to participate.

Share via

Find n consecutive date

6 additional answers

Your answer