SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Returning first date and last date for duplicate record

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy, Thanks for the quick reply. Is it complicated in my ver. of MySQL or complicated full stop?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy if you say it's complicated I know it must be, so off down the php road I go.............

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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.

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi longneck,

    I amended the code for the table above to:

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

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you messed up the where caluse by putting another condition in there without regard for operator precedence:
    Code:
     where w1.property_id = 45
       and (   w2.price is null
            or w1.price != w2.price)

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did indeed, instant now, thanks.

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

  10. #10
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    run this query:
    Code:
    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.

  11. #11
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  13. #13
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    replace
    Code:
    on w1.ddate - w2.ddate = 1
    with
    Code:
    on datediff(w1.ddate,w2.ddate) = 1


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •