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)
|
Mindt |
Maxdt |
|
2015-10-01 |
2015-10-04 |
|
2015-10-13 |
2015-10-15 |
|
2015-10-28 |
2015-10-31 |
No comments:
Post a Comment