SQL Server query to find the number of years enrolled by member

Question

I have 2 tables:

Table tbmembers

id   name
----------
 1    abc
 2    def
 3    ghi

Table tbmemberenrollment

id    memberid(foreign key)   startyear    endyear
--------------------------------------------------
 1            1                2007       2009
 2            1                2011       2012
 3            1                2013       2017

In the tbmemberenrollment table I want to calculate the number of years for which the member is enrolled till the current year, in this case result would be 6 years(2007, 2008, 2009, 2011, 2012, 2013)

I want to calculate the above result by SQL query, I don't know how to use for loops in SQL Server or how can we use cursor to get the above result, please help.....

Answer 1

Assuming your years might overlap (2007-2008 and then 2008-2009), the best option I see would be to create a Years lookup table and query against it, like such:

SELECT m.id, m.name, COUNT(DISTINCT y.yearfield) YearCount
FROM tblmembers m 
  CROSS JOIN YearLookup Y 
  INNER JOIN tbmemberenrollment me 
    ON m.id = me.memberid 
      AND YEAR(y.yearfield) >= YEAR(me.startyear)
      AND YEAR(y.yearfield) <= YEAR(me.endyear)
AND YEAR(Y.yearField) <= YEAR(GetDate())
GROUP BY m.id, m.name

SQL Fiddle Demo

If your data won't have these overlapped years, then you can do something like this to get the results:

SELECT m.id, m.name, 
  SUM(
    CASE 
      WHEN YEAR(me.endyear) > YEAR(getDate()) 
      THEN YEAR(getDate()) 
      ELSE YEAR(me.endyear) 
    END - YEAR(me.startyear) + 1
    ) totYears
FROM tblmembers m 
  LEFT JOIN tbmemberenrollment me on m.id = me.memberid
WHERE YEAR(me.startyear) <= YEAR(getDate())
GROUP BY  m.id, m.name

SQL Fiddle Demo

EDIT: Using a recursive CTE vs. a Lookup table

While I would still recommend using the lookup table, sometimes that is not a viable option. In those cases, you can accomplish the same thing using a recursive CTE.

WITH years AS (
  SELECT MAX(endyear) maxyear, MIN(startyear) minyear 
  FROM tbmemberenrollment
  ),
RecursiveCTE AS (
  SELECT minyear yearfield
  FROM years
  UNION ALL
  SELECT DATEADD(year, 1, yearfield)
  FROM RecursiveCTE R 
    JOIN years T
      ON R.yearfield < T.maxyear
  )
SELECT m.id, m.name, COUNT(DISTINCT y.yearfield) YearCount
FROM tblmembers m 
  CROSS JOIN RecursiveCTE Y 
  INNER JOIN tbmemberenrollment me 
    ON m.id = me.memberid 
      AND YEAR(y.yearfield) >= YEAR(me.startyear)
      AND YEAR(y.yearfield) <= YEAR(me.endyear)
AND YEAR(Y.yearField) <= YEAR(GetDate())
GROUP BY m.id, m.name