Path: csiph.com!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail From: "Tony Johansson" Newsgroups: comp.databases.ms-sqlserver Subject: Re: How to keep a history of price for different ice cream in a dataware house Date: Sun, 15 Feb 2015 22:30:54 +0100 Organization: A noiseless patient Spider Lines: 84 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset="Windows-1252"; reply-type=response Content-Transfer-Encoding: 8bit Injection-Date: Sun, 15 Feb 2015 21:30:35 +0000 (UTC) Injection-Info: mx02.eternal-september.org; posting-host="8fdc2b5af81c338deb7045fc6c39b0e2"; logging-data="4530"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+Z6IP76+3gVg9Z+FpOsFTe" X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18463 In-Reply-To: X-Newsreader: Microsoft Windows Mail 6.0.6002.18197 Cancel-Lock: sha1:LHUc/nQBu/JqoeZjfsDBCp+XeZA= X-Priority: 3 X-MSMail-Priority: Normal Xref: csiph.com comp.databases.ms-sqlserver:1892 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" 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" 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 >