Re: writing a query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 10/21/04 

Date: Thu, 21 Oct 2004 16:53:26 -0700

>> What I need to do is total up some hours worked by a persons bill
rate. But, I need to be able to select the rate at the time the work is
performed. <<

Read the note at the cottomof this posting. In general the bst way to
handle these problems is with a history table that has durations:

CREATE TABLE BillRates
(ssn CHAR(9) NOT NULL,
 start_date DATETIME NOT NULL,
 end_date DATETIME, -- null is current
 rate DECIMAL (12,4) NOT NULL,
 PRIMARY KEY (ssn, start_date));

use a Calendar table and a BETWEEN predicate to fnd the rate for a date.
If you try to do this with just one date in the history, you get these
god-awful self-joins that are expensive to execute and bitch to
maintain.

And please learn the differences between fields and columns, or you'll
never understand SQL.

--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. Sample data is also a good idea, along with clear
specifications.

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

Relevant Pages

  • Re: Which database design is better
    ... It would really help if you would post DDL. ... (store_nbr INTEGER NOT NULL PRIMARY KEY, ... ssn CHARNOT NULL PRIMARY KEY ... start_date DATETIME NOT NULL, ...
    (microsoft.public.sqlserver.programming)
  • Re: Auto-generate recently visited record-list
    ... Form Company with CompanyID as primary key and autonummered / ... Table History with LogID and FkID ... Allen Browne - Microsoft MVP. ... On the company form I added a combo list CompanyName ORDER BY Inserting ...
    (comp.databases.ms-access)
  • Re: Data Primary key vs. Artificial (Autonumber) primary key
    ... sorry to distract you with minor issues, like the SSN and name fields. ... primary key based on data fields versus using a primary key based on an ... the autonumber field would be used for defining relations between tables. ... Are there any negative aspects associated with the BlueClaw approach? ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Date Calculation
    ... HireDate - DateTime ... CourseID - Autonumber - primary key ... Grace - Number - long integer – number of days grace period allowed ...
    (microsoft.public.access.tablesdbdesign)
  • datasets - working with alot of data
    ... But this is more of a general question. ... the oldest million records into a history table, ... I could have made a primary key out ... of this post will not scare people off from reading it. ...
    (microsoft.public.dotnet.general)