October 12, 2009 at 3:30 am
i have a cursor to compare the previous row dates to the current row dates to ensure they do not overlap, for each customer, e.g
GOOD DATA: (the start date of row 2 is greater than the end date of row 1)
RowId CustId StartDate EndDate
1 1234 01-jan-99 01-feb-99
2 1234 02-feb-99 02-mar-99
BAD DATA: (the start date of row 2 is less than the end date of row 1)
RowId CustId StartDate EndDate
1 1234 01-jan-99 01-feb-99
2 1234 10-jan-99 02-mar-99
i know this is uneconomic and is hugely resource hungry, but is there any alternative to compare each row?
(There are about 20k rows within this table, with 3k distinct CustId's)
October 12, 2009 at 4:44 am
Something like this ?...
select * from yourtable tab1 join yourtable tab2
on tab1.rowid <> tab2.rowid
and tab1.custid = tab2.custid
and tab1.StartDate between tab2.Startdate and tab2.EndDate
October 12, 2009 at 6:47 am
yes dave almost identical to something i had just before lunch!
SELECT
rowid
FROM
customer as main
INNER JOIN
customer as sub
ON
main.rowid <> sub.rowid
AND
main.startdate between sub.startdate and sub.enddate
October 12, 2009 at 8:26 pm
Just keep in mind that Between is inclusive and that sometimes causes problems with datetime columns in SQL Server since SQL Server stored everything as Date AND time so 1/1/2009 is stored as 1/1/2009 00:00:00.000.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply