Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770

    Unanswered: minimum price on earliest date

    Ok my first posted question :
    (This is related to a travel website)

    I have the following table layout :
    Code:
    CREATE TABLE "public"."package" (
      "id" BIGINT NOT NULL, 
      "accom_code" VARCHAR(4) NOT NULL, 
      "start_date" DATE NOT NULL, 
      "end_date" DATE NOT NULL, 
      "pricing_type" VARCHAR(2), 
      "indic_price" NUMERIC(7,2), 
      "unit_price" NUMERIC(7,2), 
      "adult_age_max_cnt" INTEGER, 
      CONSTRAINT "package_pkey" PRIMARY KEY("id")
    ) WITH OIDS;
    The package table contains a list (a very large one) for holiday accomodation packages.

    What i'm trying to get is the following :

    The MINIMUM price using the following for "price" :

    Code:
    CAST (CASE p.pricing_type
                 WHEN 'UN' THEN p.unit_price
                 WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
                 ELSE p.unit_price
             END AS NUMERIC(7,2))
    AND

    The minimum date (i.e. nearest start date) greater than today
    WHERE the start_date equals the minimum start_date and the price equals the minimum price.

    Any thoughts?

    With any luck I will be able to give you an test data insert for this.

    This should probably be posted in the POSTGRESQL section however I feel that it is a more general SQL question than anything else.

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    What I have thus far :

    Code:
    SELECT p.accom_code
            ,MIN(CAST (CASE p.pricing_type
                 WHEN 'UN' THEN p.unit_price
                 WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
                 ELSE p.indic_price
             END AS NUMERIC(7,2))) as min_price
            ,MIN(p.start_date) as min_start
      FROM package p
          WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF','BGDE','YRB5','BJAM')
          AND p.duration = 7
          AND start_date > current_date
          AND start_date < current_date + interval '28 day'
          GROUP BY p.accom_code
    At the present time i'm getting 10 records (as expected). However I believe these records are WRONG as the minimum price doesn't necessarily match up with the minimum date for a particular (correct me i'm wrong here). How do I go about correcting this?

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Ooh think I nearly got it, can someone verify this :

    Code:
    SELECT p.accom_code
            ,x.min_start
            ,MIN(CAST (CASE p.pricing_type
                 WHEN 'UN' THEN p.unit_price
                 WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
                 ELSE p.indic_price
             END AS NUMERIC(7,2))) as min_price
      FROM package p
      JOIN (
              SELECT p.accom_code,MIN(p.start_date) as min_start
              FROM package p
              WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF','BGDE','YRB5','BJAM')
              AND p.duration = 7
              AND start_date > current_date
              AND start_date < current_date + interval '28 day'
              GROUP BY p.accom_code
          ) x
      ON p.accom_code = x.accom_code AND x.min_start = p.start_date
          WHERE p.accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF','BGDE','YRB5','BJAM')
          AND p.duration = 7
          AND start_date > current_date
          AND start_date < current_date + interval '28 day'
      GROUP BY p.accom_code,x.min_start
    In theory it should give the cheapest price on the earliest start date for each of the 10 accomodation types.

    Edit : Just to finish it off I needed the start_date out of it as well (added x.min_start to select and group clause)
    Last edited by aschk; 05-17-07 at 07:42.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looks okay to me

    i would not use the same alias "p" in more than one place in the query

    and i would probably remove one of the following:
    Code:
    AND p.duration = 7
    AND p.duration = 7
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    LOL oh yeah. I've cleaned it up in the above post now.
    However I do have one question to finish it off (and this annoys me about of lot of SQL i've done in the past) :
    Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections? I hope that question makes sense.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by aschk
    Is there any way to remove the duplicate WHERE clauses in a query like this
    define a view
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Btw, your queries won't work at all in SQL because you used delimited schema/table/column names in the CREATE TABLE statement (i.e. double-quotes) but not in your query.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    That DDL was written in PMS for Postgres so it's a pure copy. I did mention that I was using Postgresql in my first post.

    What I DID want to be standard was the SQL query I was doing.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Well, then you have to use delimited schema/table/column names in your query - as I said.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I have come across a problem with the query above. If two records calculate the same minimum price for one accom_code I can get two results when joined to itself.
    i.e. min price and min date is &#163;189 and 25/05/07
    when self-joined total price = &#163;567 / 3 adults = &#163;189
    and total price = &#163;378 / 2 adults = &#163;189

    They both match the minimum price and date and thus both records are output. DOH!

    As a quick hack (and I don't like this method) I did a distinct and ordered by total price minimum first.

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by aschk
    Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections?
    For this purpose, SQL99 defines "common table expressions", i.e., a "WITH" subclause of a select statement. Not yet available in the current version of PostgreSQL but coming soon (8.4 probably)
    (DB2, Oracle and SQLServer already have them in place.)
    Your query, with CTEs, would become:
    Code:
    WITH p AS
    ( SELECT accom_code, start_date,
               MIN(CAST (CASE pricing_type
                   WHEN 'UN' THEN unit_price
                   WHEN 'PA' THEN indic_price*adult_age_max_cnt
                   ELSE indic_price
               END AS NUMERIC(7,2))) as min_price
      FROM   package
      WHERE  accom_code IN ('DDNA','ADE9','CGHH','ASEC',
                            'BDB9','HGMD','CMEF','BGDE','YRB5','BJAM')
        AND  duration = 7
        AND  start_date > current_date
        AND  start_date < current_date + interval '28 day'
    ),
    x AS
    ( SELECT p.accom_code,MIN(p.start_date) as min_start
      FROM p
      GROUP BY p.accom_code
    )
    SELECT p.accom_code,x.min_start,p.min_price
      FROM p INNER JOIN x
         ON p.accom_code = x.accom_code AND x.min_start = p.start_date
     GROUP BY p.accom_code,x.min_start
    These are effectively "local view definitions", cf suggestion by r937.
    (Note that in the above query, the SELECT .. p.min_price is not compatible with the GROUP BY -- maybe add p.min_price to the GROUP BY? Or replace it by "MIN(p.min_price)"?)
    Last edited by Peter.Vanroose; 07-09-07 at 08:53.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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