Here are some comments regarding the code. Of course, the best way to learn what is happening is to break things down and display the parts in the query.
datediff(day,case when ENROLLMENTDATE < dateadd(year,@WorkingYear - 1900,0)
then dateadd(year,@WorkingYear - 1900,0)
else ENROLLMENTDATE
end,
case when TERMINATIONDATE >= dateadd(year,@WorkingYear - 1900 + 1,0)
then dateadd(year,@WorkingYear - 1900 + 1,-1)
else TERMINATIONDATE
end) + 1
case when ENROLLMENTDATE < dateadd(year,@WorkingYear - 1900,0)
then dateadd(year,@WorkingYear - 1900,0)
else ENROLLMENTDATE
end -- This case statement identifies the start of an enrollment period. If the enrollmentdate is less than the first day of the year
-- we will substitute the first day of the active year for the enrollment date.
case when TERMINATIONDATE >= dateadd(year,@WorkingYear - 1900 + 1,0)
then dateadd(year,@WorkingYear - 1900 + 1,-1)
else TERMINATIONDATE
end -- This case statement identifies the termination date of the enrollment period. If the termination date is equal to or greater than
-- the first day of the next year we substitute the last day of the active year for the termination date.
-- we then add one to this difference to include the termination date. For instance datediff(day,'2013-01-01','2013-01-31) returns 30, but
-- the number of days is 31, we need to add 1.
dateadd(year,@WorkingYear - 1900,0) -- returns the first day of the working year
dateadd(year,@WorkingYear - 1900 + 1,0) -- returns the first day of the following year
dateadd(year,@WorkingYear - 1900 + 1,-1) -- returns the last day of the working year
sum(datediff(day,case when ENROLLMENTDATE < dateadd(year,@WorkingYear - 1900,0)
then dateadd(year,@WorkingYear - 1900,0)
else ENROLLMENTDATE
end,
case when TERMINATIONDATE >= dateadd(year,@WorkingYear - 1900 + 1,0)
then dateadd(year,@WorkingYear - 1900 + 1,-1)
else TERMINATIONDATE
end) + 1) as NumDays -- As part of the GROUP BY, the HAVING clause is summing all the differences between
-- EnrollmentDate and TerminationDate + 1 for each UNIVERSALMEMBERID. This is then subtracted
-- from the total number of days for the year and we keep those rows where this difference is 45 or less