[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Query that search for time overLap

Posted on 2011-10-24
Medium Priority
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
  • 4
  • 3
LVL 50

Expert Comment

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

Accepted Solution

Lowfatspread earned 2000 total points
ID: 37017393
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

ID: 37017529
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

LVL 50

Expert Comment

ID: 37017680
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

ID: 37017887
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

ID: 37017976
so ... does my solution work?

what is the position regarding the nulls?

Author Comment

ID: 37018128
Hi Thanks

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

I will do the rest..


Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

867 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