SQL Query ??

gerryjuice

I need a bit of help trying to create a sql query to do the following

I gave a table with the following columns & rec's

ID ProdID Date_1 Date_2
1 prod12 01/01/1950 31/12/9999
2 prod12 31/05/2005 21/06/2006

What i want to do is to get are recs which hold for the following conditions
1. Their ProdID occurs more than once.
2. If 1 holds, retreive the recs if the other occurence's time frame occurs within its own.
eg rec ID 2 from the tables time frame occures within 1, therefore i need these two recs.

Is there a way to hold variables in sql (ie PodID)?
I know i can do by developing a program, but i wanted to just see if it could be done using just sql.:)

Beano

I'm a bit confused by what you are trying to achieve. Given the data you have there what result would you expect to see?

stipey

if i get the gist of what you are looking for

select *
from   Products p1,
         Products p2
where p1.ProdID  = p2.ProdID
and    p2.Date_1 > p1.Date_1  -- you might want >=
and    p2.Date_2 < p1.Date_2  -- you might want >=
and    p1.ID <> p2.ID             -- if using >= or <= make sure you don't match the row to itself 

[Edit:] Only thing is, this will be bring back one row and not two.

gerryjuice

Sorry i didnt explain it properly,

If i have a table
eg

Id prodID date1 date2
1 prod12 01/01/1950 31/12/1999
2 prod65 01/01/1950 31/12/1999
3 prod78 01/01/1950 31/12/1999
4 prod34 01/01/1950 31/12/1999
5 prod12 01/01/2005 31/12/2006
6 prod99 01/01/1950 31/12/1999
7 prod51 01/01/1950 31/12/1999
8 prod34 01/01/2004 31/12/2006
9 prod11 01/01/1950 31/12/1999
10 prod09 01/01/1950 31/12/1999

The query should give me back
1 prod12 01/01/1950 31/12/1999
5 prod12 01/01/2005 31/12/2006
4 prod34 01/01/1950 31/12/1999
8 prod34 01/01/2004 31/12/2006

Thanks for the help

liamo

Your second rule does not appear to have been applied based on the example results. Is that deliberate or an error?

I don't think it's possible (well, certainly not practical) to return what you want with SQL alone.

Here's a scenario : You have 3 records A, B, C where B's date range lies within A's, and where C's date range lies within B's. In that case, C's date range also lies with A's. Do you need 1 set of three records returned, or 1 set of three and 1 set of two? Or do you only want a maximum of two records per set returned?

What about a situation where two records have overlapping date range (as opposed to an enclosed date range) ? Do you want 1 or 2 sets of records?

Without more information on your data and a more defined set of requirements, it's difficult to take this any further. Regardless, I think you're going to end up processing this outside of SQL.

Regards,

Liam