Asp Forum - Date range check - microsoft.public.sqlserver.programming

cpnet

3/25/2007 7:40:00 PM

I have a table that associates zip codes to towns. A zip code associated
with a town may change over time, so I'm also associating a date range with
the pair. I want to make sure that a given zip code is only associated with
a single town for any period of time. For example, 90210 may be a zip code
for Beverly Hills from 2000-2006, but in 2007 maybe it becomes a zip code
for Compton.

My table has 5 columns:

ZipTownId
TownId
ZipCode
StartDT
EndDT

I'm fairly new to SQL Server, and so far I'm thinking of writing a UDF that
will take a Zip Code and start and end date values. It will check my table
to see if there's any records with overlapping date ranges for that Zip
Code. I would use the UDF in a CHECK contstraint on the table to validate
any date inserts/updates on the table.

My question is whether or not this is the best way to handle this?

Thanks

4 Answers

--CELKO--

3/25/2007 10:39:00 PM

This is the general skeleton of a history table

CREATE TABLE ZipCodeHistory
(low_zip CHAR (5) NOT NULL
CHECK(low_zip LIKE '[0-9][0-9][0-9][0-9][0-9]'),
high_zip CHAR (5) NOT NULL
CHECK(low_zip LIKE '[0-9][0-9][0-9][0-9][0-9]'),
CHECK (low_zip <= high_zip),
town_name CHAR(25) NOT NULL,
start_date DATETIME NOT NULL.
end_date DATETIME, -- null is current
CHECK (start_date < end_date));

Since SQL Server does not have full SQL-92 constraints, the rule about
no overlapping dates is a bit hard. One answer is to build a Calendar
table and put this in a TRIGGER:

IF EXISTS (SELECT *
FROM Calendar AS C, ZipCodeHistory AS Z
WHERE C.cal_date BETWEEN Z.start_date AND Z.end_date
GROUP BY C.cal_date
HAVING COUNT(*) > 1)
ROLLBACK;

Or you can use a VIEW

CREATE VIEW ZipStuff (..)
AS
SELECT ..
FROM ZipCodeHistory AS Z
WHERE NOT EXISTS (SELECT *
FROM Calendar AS C, ZipCodeHistory AS Z
WHERE C.cal_date BETWEEN Z.start_date AND Z.end_date
GROUP BY C.cal_date
HAVING COUNT(*) > 1)
WITH CHECK OPTION;

The WITH CHECK OPTION is not well known.

Hugo Kornelis

3/27/2007 10:18:00 PM

On 25 Mar 2007 15:39:23 -0700, --CELKO-- wrote:

(snip)
>Or you can use a VIEW
>
>CREATE VIEW ZipStuff (..)
>AS
>SELECT ..
> FROM ZipCodeHistory AS Z
> WHERE NOT EXISTS (SELECT *
> FROM Calendar AS C, ZipCodeHistory AS Z
> WHERE C.cal_date BETWEEN Z.start_date AND Z.end_date
> GROUP BY C.cal_date
> HAVING COUNT(*) > 1)
>WITH CHECK OPTION;
>
>The WITH CHECK OPTION is not well known.

Hi Joe,

Unfortunately, the WITH CHECK OPTION won't prevent updates to the table
from violating the "constraint".

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Hugo Kornelis

3/27/2007 10:34:00 PM

On Sun, 25 Mar 2007 15:40:08 -0400, cpnet wrote:

>I have a table that associates zip codes to towns. A zip code associated
>with a town may change over time, so I'm also associating a date range with
>the pair. I want to make sure that a given zip code is only associated with
>a single town for any period of time. For example, 90210 may be a zip code
>for Beverly Hills from 2000-2006, but in 2007 maybe it becomes a zip code
>for Compton.
>
>My table has 5 columns:
>
>ZipTownId
>TownId
>ZipCode
>StartDT
>EndDT
>
>I'm fairly new to SQL Server, and so far I'm thinking of writing a UDF that
>will take a Zip Code and start and end date values. It will check my table
>to see if there's any records with overlapping date ranges for that Zip
>Code. I would use the UDF in a CHECK contstraint on the table to validate
>any date inserts/updates on the table.

Hi cpnet,

Here's a simplified version to show how the UDF would work:

CREATE FUNCTION dbo.Overlaps
(@TestID int,
@StartDT datetime,
@EndDT datetime)
RETURNS int
AS
BEGIN
DECLARE @Overlaps int
SELECT @Overlaps = COUNT(*)
FROM dbo.Test
WHERE TestID = @TestID
AND StartDT <= @EndDT
AND EndDT >= @StartDT
AND NOT (StartDT = @StartDT AND EndDT = @EndDT)
RETURN @Overlaps
END
go
CREATE TABLE dbo.Test
(TestID int NOT NULL,
StartDT datetime NOT NULL,
EndDT datetime NOT NULL DEFAULT ('99991231'),
Value int NOT NULL
CONSTRAINT PK_Test PRIMARY KEY (TestID, StartDT),
CONSTRAINT UQ_Test UNIQUE (TestID, EndDT),
CONSTRAINT CK_EndDT_StartDT CHECK (EndDT > StartDT),
CONSTRAINT CK_NoOverlaps CHECK (dbo.Overlaps(TestID, StartDT, EndDT)
= 0)
);
go
-- Three correct inserts
INSERT INTO Test (TestID, StartDT, EndDT, Value)
VALUES (1, '20060101', '20061231', 1)
INSERT INTO Test (TestID, StartDT, EndDT, Value)
VALUES (1, '20070401', '20071231', 2)
INSERT INTO Test (TestID, StartDT, EndDT, Value)
VALUES (2, '20060101', '20071231', 3)
-- Below should fail
INSERT INTO Test (TestID, StartDT, EndDT, Value)
VALUES (2, '20060401', '20061231', 4)
-- This one is fine again
INSERT INTO Test (TestID, StartDT, EndDT, Value)
VALUES (1, '20070101', '20070228', 5)
-- This one isn't
INSERT INTO Test (TestID, StartDT, EndDT, Value)
VALUES (1, '20040101', '20090228', 6)
-- Check results
SELECT * FROM Test

>My question is whether or not this is the best way to handle this?

For preventing overlaps: yes. But it's much harder if you also want to
prevent gaps (such as the month of March 2007 for TestID 1 in the sample
data above). Since those can also be cause by deletion of rows, and a
CHECK constraint is only called on inserts and updates to check the new
data, preventing gaps would require triggers.

Or, if you want no gaps and no overlaps, you can decide to omit the
ending date from the table. For any given date, the "current" row is the
one with the latest starting date on or before the current date:

SELECT TOP 1 Value
FROM dbo.Test
WHERE TestID = 1
AND StartDT <= CURRENT_TIMESTAMP
ORDER BY StartDT DESC;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

cpnet

3/28/2007 12:58:00 AM

Thank you! I'm only concerned with overlaps - gaps are fine in my case.

microsoft.public.sqlserver.programming

Date range check

cpnet

--CELKO--

Hugo Kornelis

Hugo Kornelis

cpnet

x Login to ForumsZone