Possible using where statement case 1

aspvbnetnerd · Jan 24, 2008

I have done an application that the users scan the FirstSerialNumber and LastSerialNumber I have to keep track of the LastSerialNumber is comes in order with the FirstSerialNumber that is scanned.

If the LastSerialNumber is 99926 Then the FirstSerialNumber should be 99927.

After the number 99999 it will begin with 00001

Code:

[b]AssignmentID BuntchID FirstSerialNumber LastSerialNumber ScanningStationID[/b]
26	       1	     99768	      99926	        2
26	       2	     99927	      43   	        2
26	       3	     44   	[COLOR=red]      82   [/color]	        2

This is my SQL Query. And it will return
99767 43 and I want to return 99767 82

Code:

[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]	@RetLastSerialNumber =
		[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], 
		@RetLastSerialNumberTemp = 
		[COLOR=blue]CASE[/color]
			[COLOR=blue]WHEN[/color] [COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER) - [COLOR=#FF00FF]MIN[/color](TB.LASTSERIALNUMBER) > 5000 [COLOR=blue]THEN[/color] [COLOR=#FF00FF]MIN[/color](TB.LASTSERIALNUMBER)
			[COLOR=blue]ELSE[/color] [COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER), 0)
		[COLOR=blue]END[/color]
 [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 
 AND	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 = 26)
			[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 = 26

[COLOR=blue]select[/color] @RetLastSerialNumber, @RetLastSerialNumberTemp

My question is this is it possible to use Where clause in Case Statement? Something like this.

Code:

[COLOR=blue]WHEN[/color] [COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER) - [COLOR=#FF00FF]MIN[/color](TB.LASTSERIALNUMBER) > 5000 [COLOR=blue]THEN[/color] [COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER) [COLOR=blue]WHERE[/color] TB.LASTSERIALNUMBER < 9000

gmmastros · Jan 24, 2008

No.

You can next your case statements though.

Code:

CASE WHEN MAX(TB.LASTSERIALNUMBER) - MIN(TB.LASTSERIALNUMBER) > 5000 
     THEN 
           [blue]Case When TB.LASTSERIALNUMBER < 9000
                Then MAX(TB.LASTSERIALNUMBER)
                Else 0
                End[/blue]
     END

-George

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

aspvbnetnerd · Jan 24, 2008

George, first of all, thank you for the response and thank you for trying to help me.
I feel like an idiot right now.

But I dont understand. This doesn't work.

Code:

@RetLastSerialNumberTemp = 
[COLOR=blue]CASE[/color] 
	[COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER) - [COLOR=#FF00FF]MIN[/color](TB.LASTSERIALNUMBER) > 5000 
		 [COLOR=blue]THEN[/color] 
			   [COLOR=blue]Case[/color] [COLOR=blue]When[/color] TB.LASTSERIALNUMBER < 9000
					[COLOR=blue]Then[/color] [COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER)
					[COLOR=blue]Else[/color] 0
					[COLOR=blue]End[/color]
		 [COLOR=blue]END[/color]
    [COLOR=blue]ELSE[/color] [COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER), 0)
[COLOR=blue]END[/color]

This got to complex for me. I am not so good with SQL

George

gmmastros · Jan 24, 2008

What I posted was an example of a nested Case statement. I didn't spend the time looking through your code to see if it makes sense.

You have an extra Case

Code:

@RetLastSerialNumberTemp =
[s][!]CASE[/!][/s]
    CASE WHEN MAX(TB.LASTSERIALNUMBER) - MIN(TB.LASTSERIALNUMBER) > 5000
         THEN
               Case When TB.LASTSERIALNUMBER < 9000
                    Then MAX(TB.LASTSERIALNUMBER)
                    Else 0
                    End
         END
    ELSE ISNULL(MAX(TB.LASTSERIALNUMBER), 0)
END

-George

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

gmmastros · Jan 24, 2008

There are other errors too. (I posted too quickly.)

When I write Case statements, I usually try to 'line things up' because it makes the code easier to read and helps to avoid syntax errors. The thing to remember about Case statements are:

1. Every Case must have an END
2. Each 'branch' of the case statement must return the same data type.

You appear to be OK with #2.

Take a look at the way this is laid out. Do you see how the formatting of the code makes this easier to understand?

Code:

[COLOR=blue]Select[/color] @RetLastSerialNumberTemp =
    [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER) - [COLOR=#FF00FF]MIN[/color](TB.LASTSERIALNUMBER) > 5000
         [COLOR=blue]THEN[/color]
               [COLOR=blue]Case[/color] [COLOR=blue]When[/color] TB.LASTSERIALNUMBER < 9000
                    [COLOR=blue]Then[/color] [COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER)
                    [COLOR=blue]Else[/color] 0
                    [COLOR=blue]End[/color]
         [COLOR=blue]ELSE[/color] [COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER), 0)
         [COLOR=blue]END[/color]

-George

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

gmmastros · Jan 24, 2008

I'm not suggesting that this is the ONLY formatting that works. Often times this is a personal choice/preference. I mention the formatting because it works for me. It's a good tip that you can choose to ignore if you want to. [smile]

-George

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

aspvbnetnerd · Jan 24, 2008

George, Are you shure that it is this what is causing the problem. Because you have 2 case statement and 3 end.

I am not an expert.

This is the error.
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'LEFT'.

Code:

[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]    @RetLastSerialNumber =
        [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], 
		@RetLastSerialNumberTemp =
		[COLOR=green]--CASE
[/color]			[COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER) - [COLOR=#FF00FF]MIN[/color](TB.LASTSERIALNUMBER) > 5000
				 [COLOR=blue]THEN[/color]
					   [COLOR=blue]Case[/color] [COLOR=blue]When[/color] TB.LASTSERIALNUMBER < 9000
							[COLOR=blue]Then[/color] [COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER)
							[COLOR=blue]Else[/color] 0
							[COLOR=blue]End[/color]
				 [COLOR=blue]END[/color]
			[COLOR=blue]ELSE[/color] [COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER), 0)
		[COLOR=blue]END[/color]
 [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 
 AND    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 = 26)
            [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 = 26

[COLOR=blue]select[/color] @RetLastSerialNumber, @RetLastSerialNumberTemp

aspvbnetnerd · Jan 24, 2008

George, I like the way you did it.
With way you did it, would you get the value 83 even if you set it to MAX and the max is 99926, because that is exactly what I was looking for

Please help me, I will not ask a question in SQL forum for at least 1 month :-(

George

gmmastros · Jan 24, 2008

Truth is... you have too many tables involved in this query. I'm not suggesting that it's a bad thing... it just makes it very difficult for me to understand your data and the relationships between the tables.

For the syntax error.... this should fix it.

Code:

DECLARE @RetLastSerialNumber AS INT
DECLARE @RetLastSerialNumberTemp AS INT

SELECT    @RetLastSerialNumber =
        CASE
            WHEN MAX(B.LASTSERIALNUMBER) - MIN(B.LASTSERIALNUMBER) > 5000 THEN MIN(B.LASTSERIALNUMBER)
            ELSE ISNULL(MAX(B.LASTSERIALNUMBER), 0)
        END,
        @RetLastSerialNumberTemp =
            CASE WHEN MAX(TB.LASTSERIALNUMBER) - MIN(TB.LASTSERIALNUMBER) > 5000
                 THEN
                       Case When TB.LASTSERIALNUMBER < 9000
                            Then MAX(TB.LASTSERIALNUMBER)
                            Else 0
                            End
                 ELSE ISNULL(MAX(TB.LASTSERIALNUMBER), 0)
                 END
            
 FROM    ASSIGNMENT A
            LEFT JOIN CARDBOARD CB ON CB.ASSIGNMENTID = A.ASSIGNMENTID
            LEFT JOIN BUNTCH B ON CB.CARDBOARDID = B.CARDBOARDID AND B.SCANNINGSTATIONID = 2
 AND    B.CARDBOARDID = (SELECT MAX(CCBB.CARDBOARDID)
                         FROM    ASSIGNMENT AA
                                    LEFT JOIN CARDBOARD CCBB ON CCBB.ASSIGNMENTID = AA.ASSIGNMENTID
                                    LEFT JOIN BUNTCH BB ON BB.CARDBOARDID = CCBB.CARDBOARDID AND BB.SCANNINGSTATIONID = 2
                                    LEFT JOIN Temp_Buntch TTBB ON TTBB.ASSIGNMENTID = AA.ASSIGNMENTID AND TTBB.SCANNINGSTATIONID = 2
                         WHERE        AA.ASSIGNMENTID = 26)
            LEFT JOIN Temp_Buntch TB ON TB.ASSIGNMENTID = A.ASSIGNMENTID AND TB.SCANNINGSTATIONID = 2
WHERE    A.ASSIGNMENTID = 26

select @RetLastSerialNumber, @RetLastSerialNumberTemp

Hope this helps.

-George

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

aspvbnetnerd · Jan 24, 2008

I added GROUP BY TB.LASTSERIALNUMBER it works but the Max LastSerialNumber is 99926 and that is true, but I want to retrieve the value 83 because it is after 99999 it will start over from 00001.

Do you understand what trying to explain, I feel like an idiot right now.

I know you are very angry at me right

gmmastros · Jan 24, 2008

No. I'm not very angry. I'm not angry at all.

I'm just not sure how I can help. Sorry.

-George

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

aspvbnetnerd · Jan 24, 2008

I would like to thank you with a star for trying to help. Hope I spelled it right this time. ;-)

You have helped 1000 time and this time cant explain what I want.

Last question promise, can I get the these to values in red.
using BuntchID column with one SQL query?
Which is the firstserialnumber och the lastserialnumber.

Code:

[b]AssignmentID BuntchID FirstSerialNumber LastSerialNumber ScanningStationID[/b]
26           1         [COLOR=red]99768[/color]          99926            2
26           2         99927          43               2
26           3         44             [COLOR=red]82[/color]               2

gmmastros · Jan 24, 2008

Code:

Declare @Temp Table(AssignmentId Int, BuntchId Int, FirstSerialNumber Int, LastSerialNumber int, ScanningStationId Int)

Insert Into @Temp Values(26,1,99768,99926,2)
Insert Into @Temp Values(26,2,99927,43   ,2)
Insert Into @Temp Values(26,3,44   ,82   ,2)

Select  A.AssignmentId,
        Min(Case When A.BuntchId = B.MinBuntchId Then FirstSerialNumber End) As FirstSerialNumber,
        Min(Case When A.BuntchId = B.MaxBuntchId Then LastSerialnumber End) As LastSerialNumber
From    @Temp As A
        Inner Join (
          Select AssignmentId, 
                 Min(BuntchId) As MinBuntchId, 
                 Max(BuntchId) As MaxBuntchId
          From   @Temp
          Group By AssignmentId
          ) As B
          On A.AssignmentId = B.AssignmentId
Group By A.AssignmentId

-George

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Possible using where statement case 1

aspvbnetnerd

Programmer

gmmastros

Programmer

aspvbnetnerd

Programmer

gmmastros

Programmer

gmmastros

Programmer

gmmastros

Programmer

aspvbnetnerd

Programmer

aspvbnetnerd

Programmer

gmmastros

Programmer

aspvbnetnerd

Programmer

gmmastros

Programmer

aspvbnetnerd

Programmer

gmmastros

Programmer

Similar threads

Part and Inventory Search

Sponsor