Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1892

Re: How to keep a history of price for different ice cream in a dataware house

Show key headers only | View raw

One idea.it might be possible to have a date for each row about the sales.
So in this example the icecream Päronsplit have changed price 2015-05-23 
from 7 to 10
So here you could drill down to see the sales for week. You could also add 
one more column
where you indicate that date X we had a big advertisement and you might want 
to see the effect on sales
for this drive. You would hope that when we have a big advertisement we want 
to sale more ice cream.

Sales table
 Date                        Ice cream            City               Price 
Quantity
 2015-02-01             Päronsplitt        Stockhomn       7              3
 2015-02-02             Top hat            Göteborg          5 
7
 2015-02-03             Ice pig             Malmö             11 
0
 2015-02-04             Piggelin           Sundsvall          7 
3
 2015-02-05             Sandwith           Luleå              9 
1
. . .
 2015-05-23             Päronsplitt        Stockholm       10            3


//Tony


"Tony Johansson" <johansson.andersson@telia.com> skrev i meddelandet 
news:mbr037$noc$1@dont-email.me...
>I mean to put the price as a column in the sales tables doesn't solve the 
>problem because you would overwrite the previous price.
>
> //Tony
>
> "Erland Sommarskog" <esquel@sommarskog.se> skrev i meddelandet 
> news:XnsA442CB2B72BBEYazorman@127.0.0.1...
>> Tony Johansson (johansson.andersson@telia.com) writes:
>>> Assume I have a table called Sales in the dataware house where we have 
>>> the
>>> four columns Month, Ice cream, City and quantity.
>>> We have three dimensions where we have Month, Ice cream and City.
>>> It could look like this
>>> Sales table
>>> Month        Ice cream        City                Quantity
>>> January      Päronsplitt        Stockhomn       3
>>> January      Top hat            Göteborg           7
>>> January       Smugler           Malmö              13
>>>
>>>
>>> Now to my question. It is important to keep historic data in the 
>>> dataware
>>> house.
>>> So assume that I want to keep history of the price that have been 
>>> existing
>>> on different ice cream how
>>> would this be structured in the sales table.
>>
>> I don't do data warehouses, but my gut reaction would be to have a table
>> with three columns ProductID, Date and Price.
>>
>> Then there is a choice whether to have a row only for the dates when the
>> price changes or have a price for every day. The former saves space, but 
>> it
>> makes the table more difficult to query. The latter takes up space, and
>> you need a mechanism to copy prices from day to day.
>>
>> In the system I work with, we use the model with one row per day, no
>> matter whether the price change or not, but there are two remarks:
>>
>> 1) We made this design with SQL 6.5 where writing a query to get the
>> price per a certain date was difficult and not always performant.
>> With SQL 2005 and CROSS APPLY, this is a lot easier.
>>
>> 2) We hold prices for financial instruments and FX rates, which typically
>> change from day to day anyway, whereas the price for many retail
>> products like ice cream is fairly static.
>>
>>
>> -- 
>> Erland Sommarskog, Stockholm, esquel@sommarskog.se
> 

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

How to keep a history of price for different ice cream in a dataware house "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-15 18:02 +0100
  Re: How to keep a history of price for different ice cream in a dataware house Erland Sommarskog <esquel@sommarskog.se> - 2015-02-15 19:58 +0100
    Re: How to keep a history of price for different ice cream in a dataware house "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-15 21:38 +0100
      Re: How to keep a history of price for different ice cream in a dataware house Erland Sommarskog <esquel@sommarskog.se> - 2015-02-15 22:14 +0100
      Re: How to keep a history of price for different ice cream in a dataware house "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-15 22:30 +0100
  Re: How to keep a history of price for different ice cream in a dataware house rja.carnegie@gmail.com - 2015-02-16 03:51 -0800
  Re: How to keep a history of price for different ice cream in a dataware house --CELKO-- <jcelko212@earthlink.net> - 2015-02-16 08:37 -0800
  Re: How to keep a history of price for different ice cream in a dataware house gposta10@gmail.com - 2015-03-23 11:44 -0700

csiph-web