Re: converting single record with date range into multiple records

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

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 06/30/04 

Date: Wed, 30 Jun 2004 12:52:25 -0700

Build a table for the buckets, or use an existing Calendar table:

CREATE TABLE MonthBuckets
(bucket_start DATETIME NOT NULL PRIMARY KEY,
 bucket_end DATETIME NOT NULL);

Assuming that the start and end dates are always the first and last days
of a month:

CREATE VIEW MonthParts (part_nbr, startdate, enddate)
AS
SELECT P1.part_nbr, B1.bucket_start, B1.bucket_end
  FROM Parts AS P1, MonthBuckets AS B1
 WHERE P1.startdate BETWEEN B1.bucket_start AND B1.bucket_end
   AND P1.enddate BETWEEN B1.bucket_start AND B1.bucket_end;

I would stick with a VIEW, which will update itself rather than carry
redundant information in the Database. If you are not familiar with
using a Calendar table, just Google the newsgroup and look at some old
postings. Unlike temporal functions, this is portable.

--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: 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: Help joining several tables
    ... CREATE TABLE Calendar ... (cal_date DATETIME NOT NULL PRIMARY KEY, ... This will give you the status of each employee for each day in the ...
    (microsoft.public.sqlserver.programming)
  • 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)
  • RE: Date Calculation
    ... HireDate - DateTime ... CourseID - Autonumber - primary key ... Grace - Number - long integer – number of days grace period allowed ...
    (microsoft.public.access.tablesdbdesign)
  • Re: UTC conversion from different time zones
    ... but in my idea is the time in Net not real calendar based. ... But if you take something which is already UTC (because you've called ... I find the dateTime pretty good. ... DateTime had no idea whether it was a local time or a universal time - ...
    (microsoft.public.dotnet.general)