January 20, 2015 at 10:50 am
Good Afternoon, trying to create a CASE Statement, when the value is NULL, a calculation occurs if the value is not null, a different calculation occurs. Getting the following error Incorrect syntax near the keyword 'IS'. referring to the IS NULL in the CASE Statement.
Also like to figure out how to convert all calculation outputs to (2) decimals.
Thinking the SELECT STATEMENT is sufficient for review as well as the DATA Output
SELECT
d.office_name AS 'Office Name'
,a.account_id AS 'Account ID'
,RTRIM(l.account_name) AS 'Account Name'
,c.country_name AS 'Bond Currency'
,SUM(a.bond_amt*i.exchange_rate) AS 'Rolling 12 Month Exposure'
,CASE a.exposure_amt
WHEN a.exposure_amt IS NULL THEN SUM(a.bond_amt*i.exchange_rate)
ELSE SUM(a.exposure_amt*i.exchange_rate) END AS 'Rolling 12 Month Company Exposure'
,SUM(a.gross_written_premium_amount*i.exchange_rate) AS 'Rolling 12 Month Gross Written Premium'
,SUM(a.assumed_premium_amount*i.exchange_rate) AS 'Rolling 12 Month Assumed Premium'
,SUM(a.total_premium*i.exchange_rate) AS 'Rolling 12 Month Total Premium'
,Sum(a.total_commission*i.exchange_rate) AS 'Rolling 12 Month Total Commission'
,ISNULL(m.Collateral,0)AS 'Account Collateral'
The data output is prior to me trying to add in the CASE Statement.
Office NameAccount IDAccount Name Currency Rolling 12 Month Exposure Rolling 12 Month Company Exposure Rolling 12 Month Gross Written Premium Rolling 12 Month Assumed Premium Rolling 12 Month Total Premium Rolling 12 Month Total Commission Collateral
Office Name 1111111Account 1 Canada $318,470.87 NULL $- $3,184.71 $3,184.71 $- $-
Office Name 1111111Account 1 United States $44,784.00 NULL $379.00 $- $379.00 $120.68 $-
Office Name 1222222Account 2 Canada $47,570,708.87 NULL $- $31,652.81 $31,652.81 $- $-
Office Name 1222222Account 2 United States $185,069,731.00 $468,852.00 $95,849.00 $- $95,849.00 $29,923.98 $-
Office Name 1333333Account 3 United States $6,321,952.00 NULL $19,625.00 $- $19,625.00 $6,306.25 $-
Office Name 1333333Account 3 United States $2,714,238,842.45 $16,618,162.00 $1,934,766.00 $- $1,934,766.00 $88,853.35 $39,500,000.00
Office Name 1444444Account 4 Argentina $2,430,000.00 NULL $- $12,002.00 $12,002.00 $2,530.97 $-
Office Name 1444444Account 4 United States $2,214,242.00 NULL $22,142.00 $- $22,142.00 $8,856.80 $-
Office Name 1555555Account 5 United States $200,000.00 NULL $- $- $- $- $-
Office Name 1666666Account 6 United States $27,112,670.00 NULL $18,753.00 $- $18,753.00 $3,651.50 $-
Office Name 1777777Account 7 United States $302,056,429.00 $4,293,041.00 $931,000.00 $- $931,000.00 $186,200.00 $-
January 20, 2015 at 10:57 am
your case statement was 99% there:
you got caught with syntax, minor tweak to get ove rthe "IS" error:
CASE --nothing goes here when formula is used in a WHEN a.exposure_amt
WHEN a.exposure_amt IS NULL
THEN Sum(a.bond_amt * i.exchange_rate)
ELSE Sum(a.exposure_amt * i.exchange_rate)
END AS 'Rolling 12 Month Company Exposure',
:
and the two decimal thing just requires an explicit cast/convert
CONVERT(decimal(19,2),
CASE --nothing goes here when formula is used in a WHEN a.exposure_amt
WHEN a.exposure_amt IS NULL
THEN Sum(a.bond_amt * i.exchange_rate)
ELSE Sum(a.exposure_amt * i.exchange_rate)
END) AS 'Rolling 12 Month Company Exposure',
Lowell
January 20, 2015 at 11:21 am
TY for the quick response
January 20, 2015 at 12:56 pm
Have a new challenge I'm afraid b/c I need (2) case statements and those values must be in the GROUP BY, I'm ending up with repeating account values that correspond to the values built into the CASE STATEMENT currency_id and exposure_amt,........Trying to avoid creating a 3rd temp table but maybe that's the only option?
SELECT
d.office_name AS 'Office Name'
,a.account_id AS 'Account ID'
,RTRIM(l.account_name) AS 'Account Name'
,CONVERT(decimal(19,2),SUM(a.bond_amt*i.exchange_rate)) AS 'Rolling 12 Month Exposure'
,CASE
WHEN a.exposure_amt IS NULL THEN CONVERT(decimal(19,2),SUM(a.bond_amt*i.exchange_rate))
ELSE CONVERT(decimal(19,2),SUM(a.exposure_amt*i.exchange_rate)) END AS 'Net Rolling 12 Month Exposure'
,CONVERT(decimal(19,2),SUM(a.gross_written_premium_amount*i.exchange_rate)) AS 'Rolling 12 Month Gross Written Premium'
,CONVERT(decimal(19,2),SUM(a.assumed_premium_amount*i.exchange_rate)) AS 'Rolling 12 Month Assumed Premium'
,CONVERT(decimal(19,2),SUM(a.total_premium*i.exchange_rate)) AS 'Rolling 12 Month Total Premium'
,CASE
WHEN a.transaction_currency_id = 7 THEN CONVERT(decimal(19,2),SUM(a.total_commission*i.exchange_rate))
WHEN a.transaction_currency_id = 6 THEN CONVERT(decimal(19,2),SUM(a.total_commission*i.exchange_rate))
ELSE SUM(a.total_commission) END AS 'Rolling 12 Month Total Commission'
,ISNULL(m.Collateral,0)AS 'Account Collateral'
FROM Bond_Transaction_History a
LEFT JOIN Country c
ON a.country_id = c.country_id
LEFT JOIN Office d
ON a.office_id = d.office_id
LEFT JOIN Month_End_Close f
ON a.month_end_close_id =f.month_end_close_id
LEFT JOIN Currency h
ON a.transaction_currency_id = h.currency_id
LEFT JOIN ##CurrentExchangeRates i
ON a.transaction_currency_id = i.currency_id
LEFT JOIN account l
ON a.account_id = l.account_id
LEFT JOIN ##CurrentCollateral m
ON a.account_id = m.account_id
WHERE a.lu_business_segment in ('6','7')
AND a.lu_method_acquisition != 2
AND DateDiff(dd,f.month_end_close_date,GETDATE()) <= 365
GROUP BY a.account_ID
,l.account_name
,d.office_name
,m.Collateral
,i.currency_id
,a.exposure_amt
ORDER BY d.office_name
,a.account_id;
Office NameAccount IDAccount NameRolling 12 Month ExposureNet Rolling 12 Month ExposureRolling 12 Month Gross Written PremiumRolling 12 Month Assumed PremiumRolling 12 Month Total PremiumRolling 12 Month Total CommissionAccount Collateral
Office 111111Account 1$44,784$44,784$379$0$379$121$0
Office 111111Account 1$318,471$318,471$0$3,185$3,185$0$0
Office 122222Account 2$204,600,879$204,600,879$94,705$0$94,705$29,581$0
Office 122222Account 2$2,852$2,852$0$0$0$0$0
Office 122222Account 2$5,000$5,000$0$0$0$0$0
Office 122222Account 2$80,000$80,000$200$0$200$60$0
Office 122222Account 2$12,000$12,000$0$0$0$0$0
Office 122222Account 2$14,000$14,000$100$0$100$30$0
Office 122222Account 2$15,000$15,000$100$0$100$30$0
Office 122222Account 2$40,000$40,000$160$0$160$48$0
Office 122222Account 2$300,000$300,000$584$0$584$175$0
Office 122222Account 2$27,570,709$27,570,709$0$31,653$31,653$0$0
January 20, 2015 at 1:04 pm
You just need to further simplify your case expression. You should replace the whole case expression with this:
SUM(ISNULL(a.exposure_amt, a.bond_amt) * i.exchange_rate) AS 'Rolling 12 Month Company Exposure'
This has the added bonus of not needing to add additional group by because this is now an aggregate.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply