Returning first date and last date for duplicate record

[Ward]

Hi again,

MySQL ver 4.0.25

Having sorted out a calendar search with the help of this forum, I am now working on using the same table to show a price list.

My table with a portion of data is this:
tbl_availability
----------------------------------------------
id(int11) | ddate(date) | price(int4)
----------------------------------------------
45 | 2006-01-01 | 250
45 | 2006-01-02 | 250
45 | 2006-01-03 | 250
45 | 2006-01-04 | 300
45 | 2006-01-05 | 300
45 | 2006-01-06 | 300
45 | 2006-01-07 | 300
45 | 2006-01-08 | 300
45 | 2006-01-09 | 500
45 | 2006-01-10 | 500
45 | 2006-01-11 | 500
45 | 2006-01-12 | 500
45 | 2006-01-13 | 500
45 | 2006-01-14 | 500
45 | 2006-01-15 | 300
45 | 2006-01-16 | 300
45 | 2006-01-17 | 300
45 | 2006-01-18 | 300

So far I have got stuck at:

SELECT min(ddate) as datefrom, max(ddate) as dateto, price FROM tbl_availability WHERE property_id=45 GROUP BY price ORDER BY datefrom

Returning the following:

date from 2006-01-01 - dateto 2006-01-03 - price 250
date from 2006-01-04 - dateto 2006-01-18 - price 300
date from 2006-01-09 - dateto 2006-01-14 - price 500

Whereas a correct price list would show:

date from 2006-01-01 - dateto 2006-01-03 - price 250
date from 2006-01-04 - dateto 2006-01-08 - price 300
date from 2006-01-09 - dateto 2006-01-14 - price 500
date from 2006-01-15 - dateto 2006-01-18 - price 300

Please could someone show me how to proceed from the above query to return the second resultset?

[r937]

what you're asking is exceedingly complex to do in sql

trust me

you'd be better reading (the relevant id portion of) the table into php, and detecting price breaks while looping over the resluts

[Ward]

Hi Rudy, Thanks for the quick reply. Is it complicated in my ver. of MySQL or complicated full stop?

[r937]

complicated

requires multiply-nested subqueries some of which are NOT EXISTS to detect consecutive rows with no intervening rows of a different price... or something ...

i think it might be covered in Joe Celko's SQL for Smarties

or maybe not

in any case i wouldn't try it with SQL myself

[Ward]

Rudy if you say it's complicated I know it must be, so off down the php road I go.............

[longneck]

select w1.ddate as startdate
     , w1.price as startprice
  from ward w1
left outer
  join ward w2
    on w1.ddate - w2.ddate = 1
   and w1.id = w2.id
 where w2.price is null
    or w1.price != w2.price

this will give you what date the price went in to effect, including the price from the first day. with this data, you can easily figure out the date ranges in your host language.

[Ward]

Hi longneck,

I amended the code for the table above to:

select w1.ddate as startdate
  	 , w1.price as startprice
    from tbl_availability w1
  left outer
    join tbl_availability w2
  	on w1.ddate - w2.ddate = 1
     and w1.property_id = w2.property_id
   where w1.property_id = 45 and w2.price is null
  	or w1.price != w2.price

and it returns the following:
2006-01-22 500
2006-01-24 300
2006-02-01 400
2006-02-08 500
2006-02-16 300
2006-02-22 100
2006-03-01 100
2006-03-08 600
2006-03-09 100

Do you know why it returns 2006-02-22 100 followed by 2006-03-01 100 even though there is no break in price during that period? Also the query took about 2 minutes to run on my local host, I tried a couple of times thinking it might be the pc, yet other queries are almost instantaneous.

[longneck]

you messed up the where caluse by putting another condition in there without regard for operator precedence:

 where w1.property_id = 45
   and (   w2.price is null
        or w1.price != w2.price)

[Ward]

I did indeed, instant now, thanks.

Have you an idea why it returns 2 consecutive results with the same price as above?

[longneck]

run this query:

select w1.ddate
  from tbl_availability w1
left outer
  join tbl_availability w2
    on w1.ddate - w2.ddate = 1
   and w2.id = w1.id
 where w1.id = 45
   and w2.id is null

if you get more than one row from this query, then you have a break in your date sequence. in the sample data you provided, there were no breaks.

[Ward]

that query above returns no rows, it seems that the first query returns the 1st day of the month even if the price is the same as the preceeding row and I have checked the data for breaks.

[Ward]

longneck, I lied, the second query returns:

2006-01-22
2006-02-01
2006-03-01
2006-04-01

yet there are no breaks in the data

[longneck]

replace

on w1.ddate - w2.ddate = 1

with

on datediff(w1.ddate,w2.ddate) = 1