Solved

SQL Case Statement

Posted on 2010-11-08
5
346 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now