Difficult query help

[Michael Bahm]

I have a table that stores billing rates for our employees by client.
Each employee can have a different billing rate for each client for a
specified period. Here are the columns in the table.

eid - Employee ID#
cid - Client ID#
startdt - start date of billing rate
enddt - end date of billing rate
brate - billing rate

I need to create a script that will verify that for a given eid, and cid
that either the startdt or enddt for one billing rate, the periods do
not overlap.

For example, I need to be able to detect overlaps such as this:


eid cid startdt enddt brate
001 001 1/1/2003 12/31/2003 $50
001 001 11/01/2003 04/01/2004 $75



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

[David Portas]

Re: Difficult query help

I assume you've already got a unique constraint on (eid,cid,startd t) and a
check constraint to ensure that (startdt<enddt) .

CREATE TABLE BillingRates (eid INTEGER, cid INTEGER, startdt DATETIME, enddt
DATETIME NOT NULL, CHECK (startdt<enddt) , brate NUMERIC(10,2) NOT NULL,
PRIMARY KEY (cid,eid,startd t))

INSERT INTO BillingRates VALUES (001, 001, '20030101', '20031231', 50)
INSERT INTO BillingRates VALUES (001, 001, '20031101', '20040401', 75)

This query will find any overlaps:

SELECT B1.*
FROM BillingRates AS B1
JOIN BillingRates AS B2
ON B1.eid = B2.eid
AND B1.cid = B2.cid
AND B1.startdt <= B2.enddt
AND B1.enddt >= B2.startdt
AND B1.startdt <> B2.startdt

--
David Portas
SQL Server MVP
--

[Michael Bahm]

Re: Difficult query help

Thanks, that's exactly what I needed.


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