Post reply

How to realize exclusion on date periods?

  • January 13, 2006 at 2:38 am

    #93165

    I have two tables (SQL results) of date-periods, PeriodsPossible and PeriodsUsed, each with a StartDate and EndDate. I want from PeriodsPossible only those records with no dates in PeriodsUsed.

    IE (dates as yyyy-mm-dd):

    PeriodsUsed

    ID  StartDate     EndDate

    A   2006-05-05  2006-05-17

    B   2006-05-24  2006-05-24

    C   2006-06-01  2006-06-22

    PeriodsPossible

    ID  StartDate     EndDate

    1   2006-05-01  2006-05-05

    2   2006-05-07  2006-05-10

    3   2006-05-10  2006-05-20

    4   2006-05-20  2006-05-23

    5   2006-05-20  2006-05-30

    6  2006-07-01  2006-07-29

    I want

    Not record 1 (2006-05-05 is used)

    Not record 2 (2006-05-07  2006-05-10 is used)

    Not record 3 (2006-05-10  2006-05-17 is used)

    Not record 5 (2006-05-24 is used)

    So the only records free are record 4 and 6, these records should be my sql result:

    ID  StartDate     EndDate

    4   2006-05-20  2006-05-23

    6   2006-07-01  2006-07-29

    I don't think that MS SQL Server has a exclusion function. But how could I write a sql clause to realize this?

    Or does anyone know a function for this?

  • January 13, 2006 at 3:11 am

    #614559

    Hi ,

    Try this logic..

     Select ID, Startdate, EndDate

     From PerodPossible PP inner join  PeriodUsed PU

     on  pp.ID != PU.ID  and

     PP.Startdate  not between PU.startdate and PU.Enddate  and

     PP.EndDate  not between PU.startdate and PU.Enddate 

     

     

    Thanks

    Shrikant

     

     

    Regards
    Shrikant Kulkarni

  • January 13, 2006 at 3:28 am

    #614563

    Shrikant,

    Why this PP.ID != PU.ID?

    I used A,B,C versus 1,2,3 for reason of explanation (for an easy reference). In the real world the table PU (PeriodsUsed) has no ID, it exists of only 2 columns: StartDate and EndDate.

  • January 13, 2006 at 4:07 am

    #614569

    Try this:

     

    declare @PeriodsUsed table (StartDate datetime, EndDate datetime)

    insert @PeriodsUsed select '2006-05-05', '2006-05-17'

    insert @PeriodsUsed select '2006-05-24', '2006-05-24'

    insert @PeriodsUsed select '2006-06-01', '2006-06-22'

    declare @PeriodsPossible table (StartDate datetime, EndDate datetime)

    insert @PeriodsPossible select '2006-05-01', '2006-05-05'

    insert @PeriodsPossible select '2006-05-07', '2006-05-10'

    insert @PeriodsPossible select '2006-05-10', '2006-05-20'

    insert @PeriodsPossible select '2006-05-20', '2006-05-23'

    insert @PeriodsPossible select '2006-05-20', '2006-05-30'

    insert @PeriodsPossible select '2006-07-01', '2006-07-29'

    select p.* from @PeriodsPossible p left join @PeriodsUsed u

    on

    (p.StartDate <= u.StartDate and u.StartDate <= p.EndDate)

    or

    (u.StartDate <= p.StartDate and p.StartDate <= u.EndDate)

    where u.StartDate is null

     

  • January 13, 2006 at 4:30 am

    #614573

    Hi

    Ohh..Sorry..I haven't seen that part..Anyway Logic Jesper suggested is also good.   

    Regards
    Shrikant Kulkarni

  • January 13, 2006 at 5:18 am

    #614581

    Jesper,

    I had to write it out in a graphical way, to understand the logic of your (superb) solution. But the logic is clear when you see it in front of you. Thank you very much.

    What confused me at first, is the IS NULL comparision at the end. But using SELECT * in stead of SELECT p.* made it obvious. Should it be possible without this NULL comparision? By using a WHERE EXISTS clause? I think so.

  • January 13, 2006 at 5:52 am

    #614586

    You are probably thinking of something like this:

    select p.* from @PeriodsPossible p

    where not exists

    (select * from @PeriodsUsed u

    where

    (p.StartDate <= u.StartDate and u.StartDate <= p.EndDate)

    or

    (u.StartDate <= p.StartDate and p.StartDate <= u.EndDate)

    )

    It is probably only a matter of taste, but I tend to avoid correlated subqueries as I think they are a bit harder to read. There might also be a performance issue, although I am not sure about this...

     

    Added: The logic I use is the following: Two date intervals have common dates if and only if the start date of one of the date intervals is contained in the other date interval.

  • January 13, 2006 at 6:06 am

    #614591

    I just figured it out myself:

    SELECT * FROM @PeriodsPossible P

    WHERE NOT EXISTS (SELECT NULL FROM @PeriodsUsed U

     WHERE (P.StartDate <= U.Startdate AND U.StartDate <= P.EndDate)

        OR (U.StartDate <= P.StartDate AND P.StartDate <= U.EndDate))

    But you were faster

    Both solutions (JOIN versus EXISTS) give the same Execution plan, so I think it is only a matter of taste.

    I'am used to the Exists, so I'll use this one. But the real solution came from you, so again:  THANKS!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply