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

x
Solved

# Query that search for time overLap

Posted on 2011-10-24
Medium Priority
251 Views
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..
0
Question by:ZURINET
• 4
• 3

LVL 50

Expert Comment

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

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
``````

0

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
0

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
0

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

0

LVL 50

Expert Comment

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

what is the position regarding the nulls?
0

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

Thanks
0

## Featured Post

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
Course of the Month20 days, 7 hours left to enroll

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