Re: Tsql - Pulling the Beginning and Ending Dates in a Table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 07/02/04 

Date: Fri, 02 Jul 2004 08:16:52 -0700

If you are willing to make a slight change to the schema design, your
report queries will become MUCH easier and faster than nested correlated
subqueries. Just remember that time is a continuum and has to be
expressed as durations and not points.

CREATE TABLE InventoryHistory
(line_name CHAR(15) NOT NULL,
 product_type CHAR(10) NOT NULL,
 begin_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
 end_date DATETIME,
 qty_on_hand INTEGER NOT NULL,
  ...
 PRIMARY KEY (line_name, product_type, begin_date));

A NULL end_date means that the quantity shown is still valid today; you
use COALESCE(end_date, CURRENT_TIMESTAMP)in your queries.

The problem is that you do not have a complete fact in the rows of your
table. The complete fact is that you had a quantity of something for a
certain period of time.

The query you were asking for has only "half a fact" per row, and it is
full of redundancy on the line_name, product_type and quantity columns.
The fact that you have a date column that can change meaning depending
on another column is a violation of First Normal Form (1NF).

You need a simple procedure to set the row in each (line_name,
product_type) with a NULL end_date to the CURRENT_TIMESTAMP and to
insert a row with the new quantity and CURRENT_TIMESTAMP.

Date range reports are done with a table of reporting period begin-enbd
date pairs and a simple BETWEEN predicate.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: query/parameter strategy..
    ... Thanks Marsh..the dynamic Where clause was the ticket.. ... > about how to run a report with a dynamic Where clause. ... >>The table related to the selection contains field Grantyear, ... >>Wondering if need to build for each set of existing 10 report queries, ...
    (microsoft.public.access.reports)
  • Re:Printing in Access 97
    ... The report queries the database ... > changing the paper size does not modify the report layout ... >>I have an Access database that I am trying to print off. ...
    (microsoft.public.access.reports)
  • Grouping
    ... I have a report and subreport that are not grouping the way that I like. ... report and subreport pull from the report queries. ... Is there a way to right a code for the Service field on the report to group ...
    (microsoft.public.access.reports)