Solved

SQL Case Statement

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need some help wiht :CAST AS Double 11 46
Incremental load example 2 54
Grid querry results 41 78
SQL Server: SNAPSHOT replication to include a newly added table. 2 29
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

832 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