Re: Help joining several tables

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

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 05/24/04 

Date: Mon, 24 May 2004 08:55:58 -0700

I think your design is wrong. Being absense from work is not an entity;
it is an attribute of a employee on a work day.

CREATE TABLE Attendence
(emp_id INTEGER NOT NULL
        REFERENCES Personnel(emp_id)
 work_date DATETIME NOT NULL,
 date_type CHAR(1) DEFAULT 'W' NOT NULL --w=work
        CHECK (date_type IN ('W', 'A', 'H', ..)),
 ..);

Another thing you might be missing is a Calendar table that holds all of
the temporal data for your company, such as work days and holidays; this
is a standard SQL programming trick.

CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY,
 date_type CHAR(1) DEFAULT 'W' NOT NULL --w= workday
        CHECK (date_type IN ('W', 'H', ..)),
 ..);

This will give you the status of each employee for each day in the
reporting range (@start_date, @end_date):

SELECT C1.cal_date, A1.emp_id,
       COALESCE(A1.date_type, C1.date_type)
  FROM Calendar AS C1
       LEFT OUTER JOIN
       Attendence AS A1
       ON C1.cal_date = A1.work_date
 WHERE C1.cal_date BETWEEN @start_date AND @end_date;

--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 Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • RE: Employee Training Help
    ... HireDate - DateTime ... CourseID - Autonumber - primary key ... I am creating an employee training database where I can run reports and ...
    (microsoft.public.access.tablesdbdesign)
  • Re: converting single record with date range into multiple records
    ... Build a table for the buckets, or use an existing Calendar table: ... (bucket_start DATETIME NOT NULL PRIMARY KEY, ... FROM Parts AS P1, MonthBuckets AS B1 ...
    (microsoft.public.sqlserver.programming)
  • Re: Distinct Members In Every Month
    ... schema are. ... PRIMARY KEY ); ... membership_date DATETIME NOT NULL, ... Creating a calendar table is a standard programming trick in SQL. ...
    (microsoft.public.sqlserver.programming)
  • RE: This is driving me crazy
    ... EmpID - Autonumber - Primary key ... StartDate - DateTime ... employee to Emp_Job and left join from Jobsite to Emp_Job. ...
    (microsoft.public.access.queries)
  • Re: Next specific day compare
    ... (cal_date DATETIME NOT NULL PRIMARY KEY, ... Do the calendar table with the delivery dates on each of the ten ... schema are. ...
    (microsoft.public.sqlserver.programming)