Total PageViews

Monday, September 7, 2020

How to find Minimum and Maximum of Consecutive Dates in SQL server

When we have a condition to find the min and max of date from two different consecutive date columns from the same table with different id as shown below.

Query:- Select * from Project

O/P:-     

Id

StartDate

EndDate

8

2015-10-01

2015-10-02

9

2015-10-02

2015-10-03

10

2015-10-03

2015-10-04

11

2015-10-13

2015-10-14

12

2015-10-14

2015-10-15

13

2015-10-28

2015-10-29

14

2015-10-30

2015-10-31

Now we have the condition is to find Min and Max from these consecutive dates

Query:- WITH set1 AS (

                                                SELECT dd dt,ROW_NUMBER() OVER(ORDER BY dd) Rnum

                                                FROM

                                                (select startdate as dd from Project

                                                union select enddate  as dd from Project

                                                ) fnl

                                                GROUP BY dd

                                        )

SELECT MIN(dt) mindt,MAX(dt) maxdt

FROM set1

GROUP BY DATEDIFF(day,Rnum,dt)

 O/P:-

Mindt

Maxdt

2015-10-01

2015-10-04

2015-10-13

2015-10-15

2015-10-28

2015-10-31

 

What other ideas can you add to this post that I may have not mentioned?

No comments:

Post a Comment