How can you estimate a student's college year based on their graduation date

Question

Crossing church would like to set a person attribute (set by a SQL job) that specifies what year in college a person would be (assuming they started college directly after graduating).

Answer 1

Following query would update an estimated college year attribute value for each person if their graduation date was within last 4 years:

DECLARE @AttributeId int = ??  --  <--Add Attribute Id

-- Get year that current seniors graduate 
-- (could be current year or next year depending on whether transition date has passed this year or not)
DECLARE @today DATE = GETDATE()
DECLARE @year INT = DATEPART(year, @today)
DECLARE @TransitionDate datetime = CAST( (
    SELECT TOP 1 COALESCE(V.[Value],A.[DefaultValue],'6/1')
    FROM [Attribute] A
    LEFT OUTER JOIN [AttributeValue] V ON V.[AttributeId] = A.[Id]
    WHERE [EntityTypeId] IS NULL
    AND [Key] = 'GradeTransitionDate'
) + '/' + CAST(@year AS varchar) AS datetime )
IF @today >= @TransitionDate SET @year = @year + 1

-- Add/Update new values
;MERGE INTO [AttributeValue] AS T
USING (
    SELECT
        @AttributeId AS [AttributeId],
        [Id] AS [EntityId],
        CASE ( [GraduationYear] - @year )
            WHEN -1 THEN 'Freshman'
            WHEN -2 THEN 'Sophomore'
            WHEN -3 THEN 'Junior'
            WHEN -4 THEN 'Senior'
        END AS [Value]
    FROM [Person]
    WHERE [GraduationYear] IS NOT NULL
    AND ( [GraduationYear] - @Year ) < 0 
    AND ( [GraduationYear] - @Year ) >= -4
    ) AS S
    ON S.[AttributeId] = T.[AttributeId]
    AND S.[EntityId] = T.[EntityId]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([IsSystem], [AttributeId], [EntityId], [Value], [CreatedDateTime], [ModifiedDateTime], [Guid] )
    VALUES (0, S.[AttributeId], S.[EntityId], S.[Value], @today, @today, NEWID() )
WHEN MATCHED THEN
    UPDATE SET 
        [Value] = S.[Value],
        [ModifiedDateTime] = @today
;

-- Clear any values not just updated
UPDATE [AttributeValue]
SET [Value] = ''
WHERE [AttributeId] = @AttributeId
AND ( [ModifiedDateTime] IS NULL OR [ModifiedDateTime] <> @today )