Query that search for time overLap

Posted on 2011-10-24
Last Modified: 2012-05-12
Hallo all

Given the attached table below
How can I sort the table to detect the row with DateTime overlap
I.e. Given that the Table have validFrom and ValidUntil
and given that where PriseStatus is 0 is not allowed to Overlap with another row with PriseStatus 0
and there is possibility that valid From and ValidUntil can contain Null values

In this case the overlaped row are Row 1 with PriseID = 123456gk and Row 5 with PriseID = 123453gz

I need a query that can retive both rows..  
Question by:ZURINET
    LVL 50

    Expert Comment

    what do you want to infer/imply when validfrom/to are null?
    LVL 50

    Accepted Solution

    why doesn't row 5 also overlap with row 2 and 4?

    they both appear to be totally within the period of the fifth row...

    like this?

    ;with cte as (
      select * from yourtable
      where prisestatus=0
    select a.*,b.priseid as overlaps
     from cte as a
     inner join cte as b
      on a.priseid<b.priseid
     where a.validfrom between b.validfrom and b.validuntil
        or a.validuntil between b.validfrom and b.validuntil
        or b.validfrom between a.validfrom and a.validuntil
        or b.validto between a.validfrom and a.validuntil
        or (a.validfrom <= b.validfrom and a.validuntil >= b.validfrom)
     order by a.priseid,b.priseid

    Open in new window


    Author Comment

    Hi Low

    because Row 4 has PriseStatus of 1
    Hence the select statment will ignor it .. the other way round..
    Row 4 is used by another process
    LVL 50

    Expert Comment

    data row 3 has a status of 1 and is ignored...

    the other rows overlap with the last one which contains all there periods....

    please explain how you want the data displayed...

    and explain exactly what you mean by overlap and how you need nulls treated

    Author Comment

    Hi Actually

    Row with ID = 123453gz Suppose to  be 2009--03--30... instead of 2009--11--26

    Hence 2 matches will be identified..

    I need to be able to output on the IDs of Row that overlap with each other..

    The process is for Price of goods.. where by select statment will return only 1 valid price at a time..
    Hence we need a re-org system that scan the table periodically and return .. rows with two valid Price .. within a time frame.

    E.G..  Row is valid for January
             Row 2 is for Febuary
            Row 5 is valid for January also... bingo..
    LVL 50

    Expert Comment

    so ... does my solution work?

    what is the position regarding the nulls?

    Author Comment

    Hi Thanks

    Your solution gave me the hint on what  I need to know and do

    I will do the rest..


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now