Compare Rows Using CTE and finding out required output

Question

User-1705074310 posted

This is an Input Table

Domain_Id	Start_Date	End_Date
1	2011-01-01	2011-01-05
1	2011-01-05	2011-01-07
1	2011-01-07	2011-01-15
2	2011-05-11	2011-05-12
2	2011-05-13	2011-05-14

This is an Output Table which is required

Domain_Id	Start_Date	End_Date
1	2011-01-01	2011-01-15
2	2011-05-11	2011-05-12
2	2011-05-13	2011-05-14

Can any one suggest me how to solve this problem using SQL Server. Please do provide me a right way to solve this problem.

Thank You.

Answer 1

User283571144 posted

Hi Rohan Kshirsagar,

According to your description, I still couldn't understand your requirement clearly.

Why the  start date should be 2011 but the end date will be 2001?

Does these date has the relationship? We just combine the same month's date?

If you could post more details information, it will be more easily for us to understand the issue and find out the solution.

Best Regards

Brando

Answer 2

User-1705074310 posted

Hey @Brando ZWZ I have updated my question would you help me with this.

I have tried and wrote this code but the problem with this code is i am getting an output as given below. But in this output i don't require 2nd Row. So help me to solve this problem.

Domain_Id	Starting_Date	End_Date
1	2011-01-01	2011-01-15
1	2011-01-05	2011-01-07
2	2011-05-11	2011-05-12
2	2011-05-13	2011-05-14

;with cte as (
    select Domain_Id, Starting_Date, End_Date
    from Que_Date
    union all
    select t.Domain_Id, cte.Starting_Date, t.End_Date
    from cte
    join Que_Date t on cte.Domain_Id = t.Domain_Id and cte.End_Date = t.Starting_Date
), cte2 as (
    select *, rn = row_number() over (partition by Domain_Id, End_Date order by Domain_Id)
    from cte
)
select DISTINCT Domain_Id, Starting_Date, max(End_Date) enddate
from cte2
where rn=1
group by Domain_Id, Starting_Date
order by Domain_Id, Starting_Date;

select * from Que_Date