Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Nested CASE statement  in T-sQL

Posted on 2014-11-10
8
Medium Priority
?
64 Views
Last Modified: 2016-06-15
I have a crystal report with A financial detail table that has 13 accounting period columns in it and based on the accounting period sent by a parameter  between 1 to 13  it should display the corresponding amount for the corresponding account for that specific accounting period. then the amount is further manipulated to show negative or positive values.

DECLARE @FY AS INT ,@COMPANY as varchar(100),@ACCTPERIOD AS VARCHAR(26)

SELECT  
COMPANY, ACCOUNT,

CASE
      WHEN @ACCTPERIOD='1'  THEN DB_AMOUNT_01
      WHEN @ACCTPERIOD='2'  THEN DB_AMOUNT_02
      WHEN @ACCTPERIOD='3'  THEN DB_AMOUNT_03
      WHEN @ACCTPERIOD='4'  THEN DB_AMOUNT_04
    WHEN @ACCTPERIOD='5'  THEN DB_AMOUNT_05
    WHEN @ACCTPERIOD='6'  THEN DB_AMOUNT_06
    WHEN @ACCTPERIOD='7'  THEN DB_AMOUNT_07
      WHEN @ACCTPERIOD='8'  THEN DB_AMOUNT_08
      WHEN @ACCTPERIOD='9'  THEN DB_AMOUNT_09
      WHEN @ACCTPERIOD='10' THEN DB_AMOUNT_10
      WHEN @ACCTPERIOD='11' THEN DB_AMOUNT_11
      WHEN @ACCTPERIOD='12' THEN DB_AMOUNT_12
    WHEN @ACCTPERIOD='13' THEN DB_AMOUNT_13

 ELSE ''
 END AS DBAMOUNT,
 


CASE       
WHEN ((SUBSTRING((CAST (PROD.dbo.FBDETAIL.ACCOUNT AS VARCHAR(10))),1,1))='4') AND (DB_AMOUNT_01 < 0) THEN  (ABS(DB_AMOUNT_01))
WHEN ((SUBSTRING((CAST (PROD.dbo.FBDETAIL.ACCOUNT AS VARCHAR(10))),1,1))='4') AND (DB_AMOUNT_01 > 0) THEN  (-1*(DB_AMOUNT_01))
WHEN ((SUBSTRING((CAST (PROD.dbo.FBDETAIL.ACCOUNT AS VARCHAR(10))),1,1))='5') AND (DB_AMOUNT_01 < 0) THEN  (ABS(DB_AMOUNT_01))
WHEN ((SUBSTRING((CAST (PROD.dbo.FBDETAIL.ACCOUNT AS VARCHAR(10))),1,1))='5') AND (DB_AMOUNT_01 > 0) THEN  (-1*(DB_AMOUNT_01))
ELSE DB_AMOUNT_01
END AS DOLLARS,



FISCAL_YEAR,  0 AS PERIOD,'BUDGET' "TIME CLASS", DB_UNITS_01 AS UNITS
FROM PROD.dbo.FBDETAIL  

WHERE      (FISCAL_YEAR = @FY)
AND COMPANY='100'



Now i need to take the DBAMOUNT column and further manipulated it  for accounts starting with 4 and 5 as shown in second case statement,  but i would like to reference the  DBAMOUNT rather than the first DB_AMOUNT_01  i am currently reffering in the first case statement.

i know i can create a table valued function and put the first case statement init but i am having trouble retrieving the function in the stored procedure as it returns multiple values.


any ideas how can i  reference the column for first case statment into second case statment.
0
Comment
Question by:malikomran
[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
8 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 40433867
use a subquery:

select DBAMOUNT 
FROM 
(
.... your current query
) t

Open in new window

0
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 40433907
You know that
CASE       
WHEN ((SUBSTRING((CAST (PROD.dbo.FBDETAIL.ACCOUNT AS VARCHAR(10))),1,1))='4') AND (DB_AMOUNT_01 < 0) THEN  (ABS(DB_AMOUNT_01)) 
WHEN ((SUBSTRING((CAST (PROD.dbo.FBDETAIL.ACCOUNT AS VARCHAR(10))),1,1))='4') AND (DB_AMOUNT_01 > 0) THEN  (-1*(DB_AMOUNT_01)) 
WHEN ((SUBSTRING((CAST (PROD.dbo.FBDETAIL.ACCOUNT AS VARCHAR(10))),1,1))='5') AND (DB_AMOUNT_01 < 0) THEN  (ABS(DB_AMOUNT_01)) 
WHEN ((SUBSTRING((CAST (PROD.dbo.FBDETAIL.ACCOUNT AS VARCHAR(10))),1,1))='5') AND (DB_AMOUNT_01 > 0) THEN  (-1*(DB_AMOUNT_01)) 
ELSE DB_AMOUNT_01
END AS DOLLARS, 

Open in new window

is the same as
CASE       
WHEN ((SUBSTRING((CAST (PROD.dbo.FBDETAIL.ACCOUNT AS VARCHAR(10))),1,1)) in ('4','5') THEN  -DB_AMOUNT_01 
ELSE DB_AMOUNT_01
END AS DOLLARS, 

Open in new window

?
0
 
LVL 32

Expert Comment

by:awking00
ID: 40438057
It appears that when the db_amount is negative, you use abs(db_amount) to make it positive and when it's positive you multiply by -1 to make it negative whether account = '4' or '5'. Why not just multiply by -1 for every case? Then you can make your first case statement -
CASE
      WHEN @ACCTPERIOD='1'  THEN DB_AMOUNT_01*(-1)
      WHEN @ACCTPERIOD='2'  THEN DB_AMOUNT_02*(-1)
      WHEN @ACCTPERIOD='3'  THEN DB_AMOUNT_03*(-1)
      WHEN @ACCTPERIOD='4'  THEN DB_AMOUNT_04*(-1)
    WHEN @ACCTPERIOD='5'  THEN DB_AMOUNT_05*(-1)
    WHEN @ACCTPERIOD='6'  THEN DB_AMOUNT_06*(-1)
    WHEN @ACCTPERIOD='7'  THEN DB_AMOUNT_07*(-1)
      WHEN @ACCTPERIOD='8'  THEN DB_AMOUNT_08*(-1)
      WHEN @ACCTPERIOD='9'  THEN DB_AMOUNT_09*(-1)
      WHEN @ACCTPERIOD='10' THEN DB_AMOUNT_10*(-1)
      WHEN @ACCTPERIOD='11' THEN DB_AMOUNT_11*(-1)
      WHEN @ACCTPERIOD='12' THEN DB_AMOUNT_12*(-1)
    WHEN @ACCTPERIOD='13' THEN DB_AMOUNT_13*(-1)
 ELSE ''
 END AS DBDOLLARAMOUNT,
...
WHERE SUBSTRING(CAST (PROD.dbo.FBDETAIL.ACCOUNT AS VARCHAR(10)),1,1) IN ('4','5')
0
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 40715461
malikomran - How's it going?  Looks like you have three good comments to work with..
0
 

Author Comment

by:malikomran
ID: 40735941
Qlemo, this is a mistake, i want to grant you 400 points. i have sent a request to the moderator
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 41655223
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: Qlemo (https:#a40433907)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

sjwales
Experts-Exchange Cleanup Volunteer
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

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