Age and member month computation

Transact-SQL
1

[table]
MemID FullName DOB sex EnrollmentID EnrollEffDate EnrollTermDate Age Member Month
MEM1 Testuser 1994-10-06 00:00:00.000 M E001 2015-07-01 00:00:00.000 2015-08-31 00:00:00.000
MEM1 Testuser 1994-10-06 00:00:00.000 M E002 2015-09-01 00:00:00.000 2015-11-30 00:00:00.000

[/table]

Expected OUTPUT:
[table]
MemID FullName DOB sex EnrollmentID EnrollEffDate EnrollTermDate Age Member Month
MEM1 Testuser 1994-10-06 00:00:00.000 M E001 2015-07-01 00:00:00.000 2015-08-31 00:00:00.000 20 0
MEM1 Testuser 1994-10-06 00:00:00.000 M E002 2015-09-01 00:00:00.000 2015-11-30 00:00:00.000 21 2

[/TABLE]

NOTES:
[TEXT]

[/TEXT]

code tried:
DECLARE @MEMAGE TABLE (MEMID VARCHAR(100),FULLNAME VARCHAR(100),DOB Datetime,sex char(10),enrollid varchar(100),EnrollEffDate datetime,
EnrollTermDate datetime,Age int,MemberMonth int)
insert into @MEMAGE
select 
'MEM1','Testuser','1994-10-06 00:00:00.000','M','E001','2015-07-01 00:00:00.000','2015-08-31 00:00:00.000',null,null		
union all
select 'MEM1','Testuser','1994-10-06 00:00:00.000','M','E002','2015-09-01 00:00:00.000','2015-11-30 00:00:00.000',null,null
select * from @MEMAGE
2

Have you tried DATEADD(YEAR, DOB, GETDATE())?

3

Thanks for your reply.

Basically I need the member month calculation based on DOB and enroll effective date.

When his age turns into 21 then from that point I need to calculate member months till enrollment term date.

4

Is this what you're looking for?

select memid
      ,fullname
      ,dob
      ,sex
      ,enrollid
      ,enrolleffdate
      ,enrolltermdate
      ,(datediff(month,dob,enrolleffdate)+1)/12 as age
      ,case
          when (datediff(month,dob,enrolleffdate)+1)/12<21
          then 0
          else datediff(month,enrolleffdate,enrolltermdate)
       end as membermonth
  from @memage
;
5

Thanks for your reply.
say for example if the enrollment eff date is 01-jun-2015 and enrollment Termdate is 30-Aug-2015. suppose during july only he/she attains the age as 21 then the member month should be 2. in your query the member month will show as 3 months under else part.

6

How about this:

select memid
      ,fullname
      ,dob
      ,sex
      ,enrollid
      ,enrolleffdate
      ,enrolltermdate
      ,(datediff(month,dob,enrolleffdate)+1)/12 as age
      ,case
          when (datediff(month,dob,enrolleffdate)+1)/12<21
          then 0
          else datediff(month,dob,enrolltermdate)-(21*12)+1
       end as membermonth
  from @memage
;