asked on

Query that search for time overLap

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

Lowfatspread

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

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ZURINET

ASKER

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

Lowfatspread

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

ZURINET

ASKER

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

Lowfatspread

so ... does my solution work?

what is the position regarding the nulls?

ZURINET

ASKER

Hi Thanks

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

I will do the rest..

Thanks