Help etending this query

[chrisio]

Ok, I have a range of dates that my query searches through and returns the number of days that a given date range crosses, This works very well, however I now need to modify the query so that if it crosses through one range in particular it only returns that range.

Set Date Ranges
HIGH SEASON DATES
1 JANUARY - 4 JANUARY 2006
6 JULY - 6 SEPTEMBER 2006
21 DEC - 31 DEC 2006
1 JAN - 3 JAN 2007

SHOULDER SEASON DATES
9 FEBRUARY - 22 FEBRUARY 2006
30 MARCH - 26 APRIL 2006
25 MAY - 7 JUNE 2006
29 JUNE - 5 JULY 2006
19 OCTOBER - 1 NOVEMBER 2006

LOW SEASON DATES
5 JANUARY- 8 FEBRUARY 2006
23 FEBRUARY - 29 MARCH 2006
27 APRIL- 24 - MAY 2006
8 JUNE - 28 JUNE 2006
7 SEPTEMBER - 18 OCTOBER 2006
2 NOVEMBER - 20 DECEMBER 2006

SPECIAL OFFER RATES
24 FEBRUARY - 01 MARCH 2006

$SD = 2006-02-24
$ED = 2006-02-26

if i were to feed the above set of dates to the below sql it would spit out both the row from the special offers (24 FEBRUARY - 01 MARCH 2006) and also the low season row (23 FEBRUARY - 29 MARCH 2006). What I need is where my variables are BETWEEN the special offers dates to only return those and where they are OUTSIDE those dates to return the other dates. I hope this is making sense.

[code=php:0]

$SD = $date_from (date(yyyy-mm-dd));
$ED = $date;(date(yyyy-mm-dd));
$VID = $_GET['vid']; (integer)
$rid = $_GET['rid']; (integer)

$seasons ="SELECT sdate
    , edate
    , band
    , datediff(edate,'".$SD."')+1 as days_overlap
  from season
where '".$SD."' between sdate and edate
  and '".$ED."' > edate
and rid = " . $rid . "
and vid = " . $VID . "
union
select sdate
    , edate
    , band
    , datediff('".$ED."','".$SD."')+1 as days_overlap
  from season
where '".$SD."' >= sdate
  and '".$ED."' <= edate
  and rid = " . $rid . "
  and vid = " . $VID . "
union
select sdate,
        edate,
        band,
        datediff(edate,sdate)+1 as days_overlap
from season
where sdate >= '".$SD."'
and edate  <= '".$ED."'
and rid = " . $rid . "
and vid = " . $VID . "
union
SELECT sdate, edate, band, datediff( '".$ED."' , sdate )+1 AS days_overlap
FROM season
WHERE '".$ED."'
BETWEEN sdate
AND edate
AND '".$SD."' <= sdate
and rid = " . $rid ."
and vid = " . $VID;

[/code]


Many Thanks in advance
Chris

[fenway]

[quote author=chrisio link=topic=107998.msg434033#msg434033 date=1158224795]
if i were to feed the above set of dates to the below sql it would spit out both the row from the special offers (24 FEBRUARY - 01 MARCH 2006) and also the low season row (23 FEBRUARY - 29 MARCH 2006). What I need is where my variables are BETWEEN the special offers dates to only return those and where they are OUTSIDE those dates to return the other dates. I hope this is making sense.
[/quote]
I don't know what you mean by this.