malikomran
asked on
Nested CASE statement in T-sQL
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.
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
WHEN ((SUBSTRING((CAST (PROD.dbo.FBDETAIL.ACCOUNT
WHEN ((SUBSTRING((CAST (PROD.dbo.FBDETAIL.ACCOUNT
WHEN ((SUBSTRING((CAST (PROD.dbo.FBDETAIL.ACCOUNT
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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')
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
malikomran - How's it going? Looks like you have three good comments to work with..
ASKER
Qlemo, this is a mistake, i want to grant you 400 points. i have sent a request to the moderator
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
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
Open in new window