SQLTeam.com | Weblogs | Forums

Picking Between what SQL to use


#1

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


#2

Something like this perhaps?

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 ...

#3

P.S. Don't know why I used CROSS APPLY a JOIN would do ...


#4

Could I see an example of the join?


#5

also I don't want to have to update the year, each year, so is your t.baseyear dynamic to pull the current year? sorry im really bad at SQL


#6

Yes

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 ...