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

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

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

Show all headers | View raw

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'); 

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