Date Gap Problem

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  October 18, 2010 at 5:10 am

  #1238322

  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

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  October 18, 2010 at 7:42 am

  #1238401

  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