Jump to content


Photo

Help etending this query


  • Please log in to reply
1 reply to this topic

#1 chrisio

chrisio
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 14 September 2006 - 09:06 AM

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.


$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;



Many Thanks in advance
Chris

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 14 September 2006 - 02:54 PM

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.

I don't know what you mean by this.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users