locked
Compare Rows Using CTE and finding out required output RRS feed

  • 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.

    Thursday, December 6, 2018 10:30 AM

All replies

  • 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

    Friday, December 7, 2018 3:27 PM
  • 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

    Monday, December 10, 2018 7:28 AM