how to access historical pricing 1

[jpadie]

i have the following table structure for a booking system i am developing for a client
date
houseid
price
effectivefrom

effectively i hold a price for each house and for each day. it sounds crazy but it lent itself to the simplest coding solution at the time it was first developed.

i also hold all changes to the pricing so that a particular house on a particular day can have multiple prices depending on what day the booking was made.

I'd like some help with putting together a query that will return all the prices on all days (in a given year) for all houses for a booking made on a particular day (and that won't kill my server).

thanks
Justin

 

[rac2]

From what you say, it seems like there are rows everyday for every house. So how do you tell the date of a booking?

Which column tells the date that the booking was made?

 

[jpadie]

this table is just a lookup table. so it does not store a booking date.

but let's say i wanted to know the prices of all houses on all days in 2006 for a booking made on 2006-03-22 (this is genuine as the client wants to be able to view pricing grids for the whole year)

using pcode i'd be looking to retrieve
for each day
for each house
the price
with the latest effectivefrom date
provided that the effectivefrom date was not after 2006-03-22

 

[guelphdad]

It won't kill your server when you have correctly indexed your table.

other than that select the relevant columns from your table
then do an order by date, house or house, date depending on whether you want to show all houses from december 22nd or each house and then every date that it had a price

select
house,
price,
date
from yourtable
order by 
date, house

shows
house1 price dec 22
house2 price dec 22
house3 price dec 22
house1 price dec 23
house2 price dec 23
house3 price dec 23

select
house,
price,
date
from yourtable
order by 
house, date

shows
house1 price dec 22
house1 price dec 23
house2 price dec 22
house2 price dec 23
house3 price dec 22
house3 price dec 23

and of course to both of those you would add a relevant date clause so you don't get them after 2006-03-22

 

[jpadie]

i must have explained badly.

the solution that you propose appears to return all price entries for each house and date that are prior to a date for the effectivefrom that is provided in the where clause - this, I agree, is straightforward and will return

house1 price dec 22 effectivefromsomedate1
house1 price dec 22 effectivefromsomedate2
house1 price dec 22 effectivefromsomedate3
house1 price dec 22 effectivefromsomedate4
house1 price dec 22 effectivefromsomedate5

etc

i want only to return the latest effectivefrom date that is before or on the same day as the where clause.

thanks
justin

 

[rac2]

latest effectivefrom date that is before or on the same day as the where clause"

"for each house
the price
with the latest effectivefrom date
provided that the effectivefrom date was not after 2006-03-22"

The price on the latest effectivefrom date prior to 2006-03-22 for each house.

The latest efffectivefrom date for each house prior to 2006-03-22 is given by

SELECT houseid, MAX(efffectivefrom) AS LatestDate
FROM MyTable
WHERE efffectivefrom < '2006-03-22'
GROUP BY houseid

This can be JOINed with the base table to obtain the price on the latest date.

SELECT a.houseid, a.price, b.LatestDate
FROM MyTable a
JOIN (
      SELECT houseid, MAX(efffectivefrom) AS LatestDate
      FROM MyTable
      WHERE efffectivefrom < '2006-03-22'
      GROUP BY houseid
     ) b ON b.houseid = a.houseid
        AND b.LatestDate = a.efffectivefrom

 

[jpadie]

thank you. that looks spot on.

i will have to check on the server load as it looks that it may page the table twice for each row.

i guess i may be able to avoid this by establishing a primary key over houseid, booking date and effectivefrom (but as this is three out of four fields is there much point?)

thanks again
Justin