Solved

SQL Case Statement

Posted on 2010-11-08
5
357 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with SQL - TOP 10 by date and by group 13 42
CROSS APPLY 4 62
How can i get data when i use where clause with group by? 3 48
Applying Roles in Common Scenarios 3 22
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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

730 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