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

Groups > comp.databases.ms-sqlserver > #1888 > unrolled thread

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

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1888 — How to keep a history of price for different ice cream in a dataware house

Hello!

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.

//Tony

[toc] | [next] | [standalone]

#1889

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

[toc] | [prev] | [next] | [standalone]

#1890

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 

[toc] | [prev] | [next] | [standalone]

#1891

Tony Johansson (johansson.andersson@telia.com) writes:
> 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.
> 

Not sure what you mean here. Since you have month as a key, you would store
the price for January that year. If the price changes during the month, 
things get a little hairy, but the price of ice cream is fairly static.

-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#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" <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
> 

[toc] | [prev] | [next] | [standalone]

#1893

On Sunday, 15 February 2015 17:02:34 UTC, Tony Johansson  wrote:
> Hello!
> 
> 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.
> 
> //Tony

How did the horse meat go?

[toc] | [prev] | [next] | [standalone]

#1894

To track the history of, say, Foobars we need to see time as a continuum and model it as (begin_date, end_date) pairs that define when a foobar had a particular value. Here is the skeleton. 

CREATE TABLE Foobar_History 
(foo_id CHAR(9) NOT NULL, 
 start_date DATE NOT NULL, 
 end_date DATE, --null means current 
 CHECK (start_date <= end_date),
 foo_status INTEGER NOT NULL, 
 PRIMARY KEY (foo_id, start_date)); 

When the end_date is NULL, that state of being is still current. You use a simple query for the status on any particular date;

SELECT * 
  FROM Foobar
 WHERE @in_cal_date
     BETWEEN start_date
      AND COALESCE (end_date, CURRENT_TIMESTAMP);

There are more tricks in the DDL to prevent gaps, etc

CREATE TABLE Events
(event_id CHAR(10) NOT NULL,
 previous_event_end_date DATE NOT NULL  
 CONSTRAINT Chained_Dates  
  REFERENCES Events (event_end_date), 
 event_start_date DATE NOT NULL, 
 event_end_date DATE UNIQUE, -- null means event in progress
  PRIMARY KEY (event_id, event_start_date), 
 CONSTRAINT Event_Order_Valid 
  CHECK (event_start_date <= event_end_date), 
 CONSTRAINT Chained_Dates 
  CHECK (DATEADD(DAY, 1, previous_event_end_date) = event_start_date)
-- CHECK (previous_event_end_date + INTERVAL '01' DAYS) = event_start_date)
);

-- disable the Chained_Dates constraint
ALTER TABLE Events NOCHECK CONSTRAINT Chained_Dates;
GO

-- insert a starter row
INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05');
GO

-- enable the constraint in the table
ALTER TABLE Events CHECK CONSTRAINT Chained_Dates;
GO

-- this works
INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
VALUES ('Glob Week', '2010-01-05', '2010-01-06', '2010-01-10');

-- this fails
INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
VALUES ('Snoob', '2010-01-09', '2010-01-11', '2010-01-15'); 

[toc] | [prev] | [next] | [standalone]

#1903

Dana nedjelja, 15. veljače 2015. u 18:02:34 UTC+1, korisnik Tony Johansson napisao je:
> Hello!
> 
> 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

On my websites http://www.dbdesign10.com and http://www.dbdesign11.com you can find a solution that keeps "history".
This is the first and only correct general solution that solves the "History".

Vladimir Odrljin

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web