October 18, 2010 at 5:10 am
Paul White NZ (10/18/2010)
The original question doesn't give expected output or the number of rows to be processed, so this is a valid alternative:
SELECT *
FROM #membership M
WHERE M.enddate >= '2006-01-01'
AND M.enddate != '9999-12-31'
AND M.startdate < '2010-01-01'
AND NOT EXISTS
(
SELECT *
FROM #membership M2
WHERE M2.memberid = M.memberid
AND M2.startdate <= DATEADD(DAY, 30, M.enddate)
AND M2.enddate > M.enddate
);
Paul
Agreed. I didn't read the part about the "qualifying 30 day gap" and didn't actually solve for that problem. :blush:
--Jeff Moden
October 18, 2010 at 7:42 am
Thanks all!! Once I take care of some other stuff this morning (and let the coffee start working!) I hope to be able to get back to this issue. As a Mentor with Solid Quality I sent an email to the SQL techies there too, and Itzik sent back 2 solutions, one similar to Wayne's grouping method and the other similar to Paul's.
Jeff, I really like the 'out-of-sequence' error thrower you have added into this Quirky Update method!
Sorry that I didn't post a better definition of the problem. I really was brain-dead, and the family had just gotten home from a trip. The actual requirement (for now) is to simply EXCLUDE members that have a gap > 30 days in size. I am sure there will be other similar needs as far as date ranges (especially contiguous ones) and gaps go in the future.
Oh, the dataset is very small too, so performance isn't (at this point) a critical factor. Only 13.5M rows or so. This is just one state's data tho, so that number could be MUCH larger in the future.
Again, thanks to all for the assistance - very nice stuff here (assuming it actually works when I get to reviewing it)!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply