locked
Compare multiple row in a table RRS feed

  • Question

  • User1126057398 posted
    There is one table blackout

    BlackoutId Startdate Enddate CarclassId
    1 2/3/2016 6/3/2016 1
    2 1/3/2016 5/3/2016 1
    3 3/3/2016 4/3/2016 1
    4 4/5/2016 5/5/2016 1
    5 4/3/2016 6/3/2016 1

    Now, Output will be like
    Blackout for CarclassId 1 will be 2 dates ie 1/3/2016 to 6/3/2016 and 4/5/2016 to 5/5/2016
    Friday, April 29, 2016 12:29 PM

All replies

  • User475983607 posted

    Blackout for CarclassId 1 will be 2 dates ie 1/3/2016 to 6/3/2016 and 4/5/2016 to 5/5/2016

    Confused... The date range of 4/5/2016 to 5/5/2016 is within 1/3/2016 to 6/3/2016.  Are you missing an identifier?  Is there a flaw in the example?

    If you are trying to determine if a date is within a blackout date,it would be much easier to use a WHERE clause and filter the result set where the date is between the Start and End dates.  Also it seems like a design flaw to allow overlapping dates.

    What are you trying to do?

    Friday, April 29, 2016 1:27 PM
  • User77042963 posted
      
      create table blackout(BlackoutId int identity(1,1), Startdate date,Enddate date, CarclassId int)
      Insert into blackout values('03/02/2016','03/06/2016', 1) ---dateformat MM/DD/YYYY
    ,('03/01/2016','03/05/2016', 1)
    ,('03/03/2016','03/04/2016', 1)
    ,('05/04/2016','05/05/2016', 1)
    ,('03/04/2016','03/06/2016', 1)
     
    ---Option 1 
      ;with mycte as (
     Select distinct dt,CarclassId from blackout
     Cross apply (values(StartDate),(EndDate)) d(dt)
     )
    
    
    ,mycte1 as  (
    SELECT
        *,  DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY CarclassId ORDER BY dt), dt) AS grp FROM mycte
    )
    select CarclassId, min(dt) as [From],max(dt) as [To] from mycte1
    group by CarclassId, grp
    Order by CarclassId, grp
    
     
    
    
     ----Option 2
     --Define your date range here. I use min from startdate and max from enddate from your sample table
     declare @minDate date=(select min(Startdate) from blackout)
     declare @maxDate date=(select max(Enddate) from blackout)
     --===== 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, CarclassId, row_number() Over(Partition by CarclassId order by dt) rn1 from
    (select distinct CarclassId from blackout) t1, myDateCTE  
    )
      
    ,mycte1 as (
    SELECT CarclassId, dt, rn1, row_number() Over(Partition by CarclassId order by dt) rn2
       FROM mycte t1
       WHERE  EXISTS 
         (SELECT * FROM blackout t2 
           WHERE t1.dt BETWEEN t2.Startdate AND t2.Enddate and t1.CarclassId=t2.CarclassId)
      
           )
      
      
    Select CarclassId, Min(m.dt) As Startdate, Max(m.dt) As end_dt
    From mycte1 m
    Group By CarclassId, m.rn1 - m.rn2
    Order by CarclassId
    
    
    
    
    drop table blackout
     
    
     
    
      

    Friday, April 29, 2016 2:30 PM
  • User1126057398 posted
    Thanks Limno. In my case below is the table structure
    Blackout Table:
    BlackoutId, LocationId, AgencyId, Begindate,Enddate,Rategroup,Description, IpAddress,CreatedBy, Createdon

    BlackoutCars Table:
    BlackoutId, CarclassId

    Carclass Table:
    CarclassId, CarClassName, Description, IsActive


    So, in my case Blackout Id won't be unique ie w.r.t. one Blackout there will be multiple carclassId. Moreover, grouping will be w.r.t. AgencyId,. LocationId, RategroupId, CarclassId.
    RategroupId can have value 0=> Applied for all Or can have individual Id.

    Monday, May 2, 2016 5:50 AM
  • User77042963 posted

    Can you provide your table DDL and sample data with your expected result? (like I did in my post )  Thanks.

    Monday, May 2, 2016 1:26 PM