Dunno if this is the fanciest way to do this, but here's a quick answer:

DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATETIME)

INSERT INTO @Employee

SELECT 101, 'James Bond',...

Luis, that is a sexy solution! Coolness.

Apologies that I misunderstood what you needed. Hopefully Lynn will be able to see you to the finish line. 🙂

If you are wanting to ADD all the gaps together per customer per year, then my code WILL NOT work. I thought you were looking for ANY SINGLE GAP of...

All these examples you're posting now aren't in 2013.

I thought that went without saying, but yes I should have specified! 🙂 Table-Valued Functions will be many times faster than Scalar Functions!

Sorry, I just corrected my last post. Can you try that code? It should work.

If you want to do it the way you are suggesting, it will get a...

Maybe something like this?

;WITH

CTE1 AS

(

SELECT

RN = ROW_NUMBER() OVER

(PARTITION BY universalmemberid ORDER BY enrollmentdate),

*

FROM #nj_members

WHERE

(enrollmentdate >= '1/1/2013' AND enrollmentdate < '2014-1-1')

OR

(terminationdate >= '1/1/2013'...

Tammy, the code that I posted should work by itself for the DDL you provided (below). The rest of the code you provided (20 steps) I unfortunately don't have time...

I think it was in one of my Louis Davidson books, it was almost definitely T-SQL. But I'll have to go try to dig it up.

If you can post some DDL to create/insert the tables/data--the underlying data, not the concatenated string version--I'm sure someone will be able to help you.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Actually, first of all, I should correct what I wrote, because you only need one CTE:

;WITH

CTE1 AS

(

SELECT

RN = ROW_NUMBER() OVER

(PARTITION BY universalmemberid ORDER BY enrollmentdate),

*...

Glad if I could help. I'd really rather not solve the entire puzzle for you, or you won't really learn much from this exercise. Plus I'm not as familiar with...

I think you will be looking at doing something like this. You will need to adjust it to suit your needs, but hopefully the concept makes sense? You will need...

It may also be possible to re-write your SP as a Function and use CROSS APPLY. Just thinking out loud.