I have a script that looks at a users grade level, and converts a grad year base on that grade. The only problem is, depending on when I run the SQL query, the grad year will be different since a students school year runs from say 2014-mid 2015.
Here is what I have, and I wanted to know of an easier way to do this, I cant use stored procs or functions, I don't have rights.
Is there cleaner, code that can be done here?
we are using this after dec 31
CASE
WHEN X.GL = '12' THEN YEAR(GETDATE())
WHEN X.GL = '11' THEN YEAR(GETDATE()) + 1
WHEN X.GL = '10' THEN YEAR(GETDATE()) + 2
WHEN X.GL = '09' THEN YEAR(GETDATE()) + 3
WHEN X.GL = '08' THEN YEAR(GETDATE()) + 4
WHEN X.GL = '07' THEN YEAR(GETDATE()) + 5
WHEN X.GL = '06' THEN YEAR(GETDATE()) + 6
WHEN X.GL = '05' THEN YEAR(GETDATE()) + 7
WHEN X.GL = '04' THEN YEAR(GETDATE()) + 8
WHEN X.GL = '03' THEN YEAR(GETDATE()) + 9
WHEN X.GL = '02' THEN YEAR(GETDATE()) + 10
WHEN X.GL = '01' THEN YEAR(GETDATE()) + 11
WHEN X.GL = 'KG' THEN YEAR(GETDATE()) + 12
WHEN X.GL = 'PK' THEN YEAR(GETDATE()) + 13
WHEN X.GL = 'EE' THEN YEAR(GETDATE()) + 14
END AS GRADYR, A.CAMPUS_ID
before dec 31
CASE
WHEN X.GL = '12' THEN YEAR(GETDATE()) +1
WHEN X.GL = '11' THEN YEAR(GETDATE()) + 2
WHEN X.GL = '10' THEN YEAR(GETDATE()) + 3
WHEN X.GL = '09' THEN YEAR(GETDATE()) + 4
WHEN X.GL = '08' THEN YEAR(GETDATE()) + 5
WHEN X.GL = '07' THEN YEAR(GETDATE()) + 6
WHEN X.GL = '06' THEN YEAR(GETDATE()) + 7
WHEN X.GL = '05' THEN YEAR(GETDATE()) + 8
WHEN X.GL = '04' THEN YEAR(GETDATE()) + 9
WHEN X.GL = '03' THEN YEAR(GETDATE()) + 10
WHEN X.GL = '02' THEN YEAR(GETDATE()) + 11
WHEN X.GL = '01' THEN YEAR(GETDATE()) + 12
WHEN X.GL = 'KG' THEN YEAR(GETDATE()) + 13
WHEN X.GL = 'PK' THEN YEAR(GETDATE()) + 14
WHEN X.GL = 'EE' THEN YEAR(GETDATE()) + 15
END AS GRADYR, A.CAMPUS_ID
SELECT ...,
CASE
WHEN X.GL = '12' THEN T.T_BaseYear
WHEN X.GL = '11' THEN T.T_BaseYear + 1
WHEN X.GL = '10' THEN T.T_BaseYear + 2
WHEN X.GL = '09' THEN T.T_BaseYear + 3
WHEN X.GL = '08' THEN T.T_BaseYear + 4
WHEN X.GL = '07' THEN T.T_BaseYear + 5
WHEN X.GL = '06' THEN T.T_BaseYear + 6
WHEN X.GL = '05' THEN T.T_BaseYear + 7
WHEN X.GL = '04' THEN T.T_BaseYear + 8
WHEN X.GL = '03' THEN T.T_BaseYear + 9
WHEN X.GL = '02' THEN T.T_BaseYear + 10
WHEN X.GL = '01' THEN T.T_BaseYear + 11
WHEN X.GL = 'KG' THEN T.T_BaseYear + 12
WHEN X.GL = 'PK' THEN T.T_BaseYear + 13
WHEN X.GL = 'EE' THEN T.T_BaseYear + 14
END AS GRADYR, A.CAMPUS_ID
FROM MyTable
CROSS APPLY
(
SELECT [T_BaseYear] = YEAR(GETDATE())
+ CASE WHEN MONTH(GETDATE()) <= 6 -- Adjust according to something safe when school year ends and new year has not started
THEN 0
ELSE 1
END
) AS T
WHERE ...
ORDER BY ...
SELECT ...,
CASE
WHEN X.GL = '12' THEN T.T_BaseYear
WHEN X.GL = '11' THEN T.T_BaseYear + 1
WHEN X.GL = '10' THEN T.T_BaseYear + 2
WHEN X.GL = '09' THEN T.T_BaseYear + 3
WHEN X.GL = '08' THEN T.T_BaseYear + 4
WHEN X.GL = '07' THEN T.T_BaseYear + 5
WHEN X.GL = '06' THEN T.T_BaseYear + 6
WHEN X.GL = '05' THEN T.T_BaseYear + 7
WHEN X.GL = '04' THEN T.T_BaseYear + 8
WHEN X.GL = '03' THEN T.T_BaseYear + 9
WHEN X.GL = '02' THEN T.T_BaseYear + 10
WHEN X.GL = '01' THEN T.T_BaseYear + 11
WHEN X.GL = 'KG' THEN T.T_BaseYear + 12
WHEN X.GL = 'PK' THEN T.T_BaseYear + 13
WHEN X.GL = 'EE' THEN T.T_BaseYear + 14
END AS GRADYR, A.CAMPUS_ID
FROM MyTable
JOIN
(
SELECT [T_BaseYear] = YEAR(GETDATE())
+ CASE WHEN MONTH(GETDATE()) <= 6 -- Adjust according to something safe when school year ends and new year has not started
THEN 0
ELSE 1
END
) AS T
ON 1 = 1
WHERE ...
ORDER BY ...