Case statement with variable

[aspvbnetnerd]

I have this SQL that work good.

[COLOR=blue]SELECT[/color]	[COLOR=blue]CASE[/color] 
			[COLOR=blue]WHEN[/color] [COLOR=#FF00FF]MAX[/color](B.LASTSERIALNUMBER) - [COLOR=#FF00FF]MIN[/color](B.LASTSERIALNUMBER) > 5000 [COLOR=blue]THEN[/color] [COLOR=#FF00FF]MIN[/color](B.LASTSERIALNUMBER)
			[COLOR=blue]ELSE[/color] [COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](B.LASTSERIALNUMBER), 0)
		[COLOR=blue]END[/color] [COLOR=blue]AS[/color] LastSerialNumber, 
		[COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER), 0)
[COLOR=blue]FROM[/color]	ASSIGNMENT A 
			[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] CARDBOARD CB [COLOR=blue]ON[/color] CB.ASSIGNMENTID = A.ASSIGNMENTID 
			[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BUNTCH B [COLOR=blue]ON[/color] CB.CARDBOARDID = B.CARDBOARDID AND B.SCANNINGSTATIONID = 2
			[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Temp_Buntch TB [COLOR=blue]ON[/color] TB.ASSIGNMENTID = A.ASSIGNMENTID AND TB.SCANNINGSTATIONID = 2
[COLOR=blue]WHERE[/color]	A.ASSIGNMENTID = 161
AND		A.ACTIVE = 1
OR		B.CARDBOARDID = ([COLOR=blue]SELECT[/color] [COLOR=#FF00FF]MAX[/color](CCBB.CARDBOARDID)
						 [COLOR=blue]FROM[/color]	ASSIGNMENT AA
									[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] CARDBOARD CCBB [COLOR=blue]ON[/color] CCBB.ASSIGNMENTID = AA.ASSIGNMENTID 
									[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BUNTCH BB [COLOR=blue]ON[/color] BB.CARDBOARDID = CCBB.CARDBOARDID AND BB.SCANNINGSTATIONID = 2
									[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Temp_Buntch TTBB [COLOR=blue]ON[/color] TTBB.ASSIGNMENTID = AA.ASSIGNMENTID AND TTBB.SCANNINGSTATIONID = 2
						 [COLOR=blue]WHERE[/color]		AA.ASSIGNMENTID = 161)

I want to set @RetLastSerialNumber and @RetLastSerialNumberTemp but it does not work

[COLOR=blue]DECLARE[/color] @RetLastSerialNumber [COLOR=blue]AS[/color] [COLOR=blue]INT[/color]
[COLOR=blue]DECLARE[/color] @RetLastSerialNumberTemp [COLOR=blue]AS[/color] [COLOR=blue]INT[/color]

[COLOR=blue]SELECT[/color]	[COLOR=blue]CASE[/color] 
			[COLOR=blue]WHEN[/color] [COLOR=#FF00FF]MAX[/color](B.LASTSERIALNUMBER) - [COLOR=#FF00FF]MIN[/color](B.LASTSERIALNUMBER) > 5000 [COLOR=blue]THEN[/color] @RetLastSerialNumber = [COLOR=#FF00FF]MIN[/color](B.LASTSERIALNUMBER)
			[COLOR=blue]ELSE[/color] @RetLastSerialNumber = [COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](B.LASTSERIALNUMBER), 0)
		[COLOR=blue]END[/color] [COLOR=blue]AS[/color] LastSerialNumber, 
		@RetLastSerialNumberTemp = [COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER), 0)
[COLOR=blue]FROM[/color]	ASSIGNMENT A 
			[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] CARDBOARD CB [COLOR=blue]ON[/color] CB.ASSIGNMENTID = A.ASSIGNMENTID 
			[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BUNTCH B [COLOR=blue]ON[/color] CB.CARDBOARDID = B.CARDBOARDID AND B.SCANNINGSTATIONID = 2
			[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Temp_Buntch TB [COLOR=blue]ON[/color] TB.ASSIGNMENTID = A.ASSIGNMENTID AND TB.SCANNINGSTATIONID = 2
[COLOR=blue]WHERE[/color]	A.ASSIGNMENTID = 161
AND		A.ACTIVE = 1
OR		B.CARDBOARDID = ([COLOR=blue]SELECT[/color] [COLOR=#FF00FF]MAX[/color](CCBB.CARDBOARDID)
						 [COLOR=blue]FROM[/color]	ASSIGNMENT AA
									[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] CARDBOARD CCBB [COLOR=blue]ON[/color] CCBB.ASSIGNMENTID = AA.ASSIGNMENTID 
									[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BUNTCH BB [COLOR=blue]ON[/color] BB.CARDBOARDID = CCBB.CARDBOARDID AND BB.SCANNINGSTATIONID = 2
									[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Temp_Buntch TTBB [COLOR=blue]ON[/color] TTBB.ASSIGNMENTID = AA.ASSIGNMENTID AND  TTBB.SCANNINGSTATIONID = 2
						 [COLOR=blue]WHERE[/color]		AA.ASSIGNMENTID = 161)

The error that I am getting Line 5: Incorrect syntax near '='.

Does anyone know what I am doing wrong?

George

 

[gmmastros]

SELECT @RetLastSerialNumber 
         = CASE WHEN MAX(B.LASTSERIALNUMBER) - MIN(B.LASTSERIALNUMBER) > 5000 
                THEN MIN(B.LASTSERIALNUMBER)
                ELSE ISNULL(MAX(B.LASTSERIALNUMBER), 0)
                END,
        @RetLastSerialNumberTemp = ISNULL(MAX(TB.LASTSERIALNUMBER), 0)
FROM    etc.....

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[aspvbnetnerd]

Works perfect. Thank you George.

George