Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Case Statement

Posted on 2010-11-08
5
Medium Priority
?
364 Views
Last Modified: 2012-05-10
I have the following SQL query. I need to write a case statement that converts LOBtotal which is an int field to a negative number when it is positive and a positive number to negative. I am not certain how to do this.

SELECT LOBTOB
            ,LOB_LOB
            ,BatchID = 'IS4WPAWP'
            ,DocNbr = 'CUEPrem'
            ,[Account Nbr] = CASE
                                           WHEN LOB_LOB = 'CM' THEN '01-5100-00CM00-010000-00'
                                           WHEN LOB_LOB = 'OC' THEN '01-5100-00OC00-010000-00'
                                           WHEN LOB_LOB = 'GL' THEN '01-5100-00GL00-010000-00'
                                           WHEN LOB_LOB = 'Tails' THEN '01-5100-00TL00-010000-00'
                                           WHEN LOB_LOB = 'Mini-Tails' THEN '01-5100-00MTL0-010000-00'
                                           WHEN LOB_LOB = 'Claims Made Plus Prepaid Tail' THEN '01-5100-00PTL0-010000-00'
                                           WHEN LOB_LOB = 'Adjustment' THEN ''
                                          
                                      END
            ,LOBtotal
            ,[Description] = CASE
                                           WHEN LOB_LOB = 'CM' THEN 'CM CUEP'
                                           WHEN LOB_LOB = 'OC' THEN 'OC CUEP'
                                           WHEN LOB_LOB = 'GL' THEN 'GL CUEP'
                                           WHEN LOB_LOB = 'Tails' THEN 'TL CUEP'
                                           WHEN LOB_LOB = 'Mini-Tails' THEN 'MTL CUEP'
                                           WHEN LOB_LOB = 'Claims Made Plus Prepaid Tail' THEN 'PTL CUEP'
                                           WHEN LOB_LOB = 'Adjustment' THEN ''
                                          
                                      END
                              
FROM dbo.LOB
WHERE lobtob = 'Direct Written'
0
Comment
Question by:mburk1968
5 Comments
 
LVL 8

Expert Comment

by:OBonio
ID: 34083854
0 - LOBtotal

If LOBtotal = 5
0 - 5 = -5

If LOBtotal = -5
0 - - 5 = 5 (two minuses = a plus)
0
 

Author Comment

by:mburk1968
ID: 34083876
How would that look with the case tag wrapped around it. I apologize in advance for my ignorance.
0
 
LVL 8

Accepted Solution

by:
OBonio earned 2000 total points
ID: 34083897
LOBtotal isn't in the CASE statements, it's on its own.

SELECT LOBTOB
             ,LOB_LOB
            ,BatchID = 'IS4WPAWP'
             ,DocNbr = 'CUEPrem'
            ,[Account Nbr] = CASE
                                            WHEN LOB_LOB = 'CM' THEN  '01-5100-00CM00-010000-00'
                                            WHEN LOB_LOB = 'OC' THEN '01-5100-00OC00-010000-00'
                                            WHEN LOB_LOB = 'GL' THEN  '01-5100-00GL00-010000-00'
                                            WHEN LOB_LOB = 'Tails' THEN '01-5100-00TL00-010000-00'
                                            WHEN LOB_LOB = 'Mini-Tails' THEN  '01-5100-00MTL0-010000-00'
                                            WHEN LOB_LOB = 'Claims Made Plus Prepaid Tail' THEN  '01-5100-00PTL0-010000-00'
                                            WHEN LOB_LOB = 'Adjustment' THEN ''
                                           
                                      END
             ,0 - LOBtotal
            ,[Description] = CASE
                                            WHEN LOB_LOB = 'CM' THEN 'CM CUEP'
                                            WHEN LOB_LOB = 'OC' THEN 'OC CUEP'
                                            WHEN LOB_LOB = 'GL' THEN 'GL CUEP'
                                            WHEN LOB_LOB = 'Tails' THEN 'TL  CUEP'
                                           WHEN LOB_LOB =  'Mini-Tails' THEN 'MTL CUEP'
                                            WHEN LOB_LOB = 'Claims Made Plus Prepaid Tail' THEN 'PTL  CUEP'
                                           WHEN LOB_LOB =  'Adjustment' THEN ''
                                           
                                       END
                             
FROM  dbo.LOB
WHERE lobtob = 'Direct Written'
0
 
LVL 6

Expert Comment

by:dan_mason
ID: 34083911
Either as above or multiply the LOBtotal by -1.

CASE statement is not needed, just  (LOBtotal*-1) AS LOBtotal
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34084084
This would also do:

,-LOBtotal
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Integration Management Part 2
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question