Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.ms-sqlserver > #1892
| From | "Tony Johansson" <johansson.andersson@telia.com> |
|---|---|
| Newsgroups | comp.databases.ms-sqlserver |
| Subject | Re: How to keep a history of price for different ice cream in a dataware house |
| Date | 2015-02-15 22:30 +0100 |
| Organization | A noiseless patient Spider |
| Message-ID | <mbr35q$4di$1@dont-email.me> (permalink) |
| References | <mbqjdu$32o$1@dont-email.me> <XnsA442CB2B72BBEYazorman@127.0.0.1> <mbr037$noc$1@dont-email.me> |
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
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