February 26, 2008 at 10:23 am
I have a point-in-time table in a SQL2000 database that has somehow gotten some overlaps inserted into it. These are causing problems. I need to figure out a way to fix these overlaps, there are too many rows (close to 4000) to fix manually.
Here is the issue. The table contains values for products that are of differing types, which are valid for a specified period of time. So the primary key of the table is as follows:
-Product number
-value type code
-effective date
We have about 3800 rows where the primary key is fine, but the combination of product number, value type code and expiration date is occurring more than once. For example:
product number value type code effective date expiration date
1 01 1/1/2006 3/31/2006
1 01 2/1/2006 3/31/2006
While not a PK violation (because of the differing effective dates), this results in overlaps which are causing issues elsewhere in select statements that try to pull data from this table based on the construct "where product number = x and value type code = y and @datevalue between effective date and expiration date". If we had no overlaps this would always return a unique row.
So the question is, what's the easiest way to fix these overlaps? I would like to be able to iterate through the set of overlapping values, and fix each row by updating its expiration date to be 1 second prior to the effective date of the "next" row in the sequence. In the example above I would need to update the first row with a value of 1/31/2006 23:59:59 for the expiration date, since the next row in the sequence is effective on 2/1/2006. I'm thinking I'll have to cursor through these rows somehow to do this, but I'm having trouble getting my head around the correct logic to complete these updates. Any help on getting me going in the right direction would be appreciated.
February 26, 2008 at 10:48 am
How about this...?
DECLARE @dates TABLE (pNo INT, val VARCHAR(10), effDate DATETIME, expDate DATETIME)
INSERT @dates
SELECT 1,'01','1/1/2006','3/31/2006' UNION
SELECT 1,'01','2/1/2006','3/31/2006' UNION
SELECT 1,'01','2/15/2006','3/31/2006' UNION
SELECT 1,'02','4/1/2006','5/15/2006' UNION
SELECT 1,'02','5/1/2006','6/1/2006' UNION
SELECT 1,'04','1/1/2006','3/31/2006' UNION
SELECT 1,'05','1/1/2006','3/31/2006'
SELECT * FROM @dates
UPDATE @dates
SET expDate = ISNULL((SELECT MIN(b.effDate)
FROM @dates AS b
WHERE b.pNo = a.pNo
AND b.effDate < a.expDate
AND b.expDate > a.effDate
AND b.effDate > a.effDate), expDate)
FROM @dates AS a
SELECT * FROM @dates
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 26, 2008 at 1:03 pm
Awesome. Made a few tweaks and it worked like a charm. Thanks Jason!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply