SQL query to combine several tables to find price of item every day

Question

I am trying to build a table to show me the price of an item each day over a specified time period.
I begin with three tables:

one containing the sale price with a start and end date, one containing the regular price of an item and when it started at that price, and one date table that just has dates for filling in blanks.

Tables:

Sale price table    :  itemid, price, sale_date_start, sale_date_end

Regular price table :  itemid, regularp, regularp_date

missing date table  :  missingdate (date table, with 5 years of dates by day)

Items can have multiple regular price entries as well as multiple sale price entries. I am new to SQL, I have written some queries to try to combine these, but they do not work correctly -- nor do i think I am on the right track.

Can someone help?

Answer 1

First thing you need to do is simplify your schema.

You only need 1 table for the price information:

itemid, price, price_date_start, price_date_end, is_sale_price

From an application perspective, there doesn't need to be a distinction between sale prices and regular prices. All prices have a start date and an end date, although the end date for the current price might be unknown. Whether or not a price is a sale price, is only important from a business perspective, I added a column to keep the management happy.

At this point I have to point out that it isn't very efficient or helpful to get the database to produce a list of the price on every date. The data base only needs to give you information on when the price changes, because the price remains constant between each price change.

If you absolutely have to get the database to output a value for every day, you can accomplish that by joining the table I described with a sequence or view of dates, as you originally planned. It is relatively simple, but I not going to post a solution, out of fear that someone might adopt it.

Answer 2

This is made much more complicated by the fact that the regular price table has a start date, but not an end date, even though (I am assuming) the regular price can change over time.

I suggest something like this:

SELECT missing_date price_date,
       r.itemid,
       CASE WHEN s.itemid IS NULL
            THEN r.regularp
            ELSE s.price
       END item_price
FROM missing_date m
JOIN (SELECT itemid, regularp, regularp_date start_date,
             (SELECT MIN(r2.regularp_date)
              FROM   regular_price r2
              WHERE  r2.item_id = r1.item_id AND 
                     r2.regularp_date > r1.regularp_date) end_date
      FROM   regular_price r1) r
ON   m.missing_date >= r.start_date and 
     (m.missing_date <  r.end_date or r.end_date IS NULL)
LEFT JOIN sale_price s
ON   m.missing_date >= s.sale_date_start and 
     m.missing_date <= s.sale_date_end and
     r.itemid = s.itemid