minimum price on earliest date

aschk · #1 (**permalink**) 05-17-07, 06:15

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.

aschk · #2 (**permalink**) 05-17-07, 06:20

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?

aschk · #3 (**permalink**) 05-17-07, 06:29

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)

r937 · #4 (**permalink**) 05-17-07, 06:36

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

aschk · #5 (**permalink**) 05-17-07, 06:41

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.

r937 · #6 (**permalink**) 05-17-07, 06:50

Quote:

Originally Posted by aschk

Is there any way to remove the duplicate WHERE clauses in a query like this

define a view

stolze · #7 (**permalink**) 05-17-07, 07:34

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.

aschk · #8 (**permalink**) 05-17-07, 09:06

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.

stolze · #9 (**permalink**) 05-20-07, 09:43

Well, then you have to use delimited schema/table/column names in your query - as I said.

aschk · #10 (**permalink**) 05-21-07, 06:23

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 £189 and 25/05/07
when self-joined total price = £567 / 3 adults = £189
and total price = £378 / 2 adults = £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.

Peter.Vanroose · #11 (**permalink**) 07-09-07, 06:22

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)"?)

Internet Services

Web Development

Digital Marketing

Consumer Tech