Computing a value

I'm wondering if someone knows how to do this.

I am trying to add a sum of to two values within a column. I know it's easy but for some reason I can't this to function properly so I can use this value in Crystal.

I'm trying to add hudnumber 801 and 808 which are Broker Fee 1 and Broker Fee 2 in my case stmt. I'm trying to add their fees but I can't seem to get the syntax right. Here is the code:

SELECT DISTINCT
loan_main.loanid,
loan_query.cmlastname + ',' + loan_query.cmfirstname AS 'Borrower Name',
loan_main.totalloanamount,
loan_main.loanrate AS 'Int. Rate',
setups_businesschannels.channelalias AS 'Business Channel',
loan_main.businesschannelid,
custom_loanmain.Type4DisburseDT,
loan_main.dateofdisclosure,
loan_main.actualclosingdate,
loan_postclosing.disbursementdate,
loan_query.lsstatusalias,
loan_query.pmstate,
--loan_funding_detail.[description] AS 'Fee Description',
Case hudnumber
When 809 Then 'YSP'
When 811 Then 'Admin Fee'
When 813 Then 'Appraisal'
When 814 Then 'Credit Report'
When 801 Then 'Broker Fee 1' -
When 808 Then 'Broker Fee 2' - I'm trying to add these two description fee amounts
End As Fees,
loan_funding_detail.fundamount AS 'Fee Amount',
loan_funding_detail.hudamount,
re.name AS 'Broker'



FROM loan_main
INNER Join loan_channelentities lce ON loan_main.lenderdatabaseid = lce.entitylenderdatabaseid AND loan_main.loanrecordid = lce.loanrecordid

INNER JOIN rolodex_entity re ON lce.lenderdatabaseid = re.lenderdatabaseid AND lce.entityid = re.entityid AND (lce.entitycategoryid = 66 OR
lce.entitycategoryid = 67)

INNER JOIN loan_query ON loan_main.lenderdatabaseid = loan_query.lenderdatabaseid AND
loan_main.loanrecordid = loan_query.loanrecordid AND
loan_main.statusid = loan_query.lsstatusid
INNER JOIN loan_postclosing ON loan_main.lenderdatabaseid = loan_postclosing.lenderdatabaseid
AND loan_main.loanrecordid = loan_postclosing.loanrecordid
INNER JOIN custom_loanmain ON loan_main.lenderdatabaseid = custom_loanmain.lenderdatabaseid
AND loan_main.loanrecordid = custom_loanmain.loanrecordid

INNER JOIN loan_funding ON loan_main.loanrecordid = loan_funding.loanrecordid

INNER JOIN loan_funding_detail ON loan_funding_detail.loanrecordid = loan_funding.loanrecordid
AND loan_funding.sequencenumber = loan_funding_detail.sequencenumber

INNER JOIN setups_businesschannels ON loan_main.businesschannelid = setups_businesschannels.channelid


WHERE ((loan_main.businesschannelid = 8) OR (loan_main.businesschannelid = 2)
OR (loan_main.businesschannelid = 7 ))
AND loan_funding_detail.hudnumber in (801, 808, 809, 811, 813, 814)

order by setups_businesschannels.channelalias

You want add the values of Broker Fee 1 column and Broker Fee 2 column?

Something like this??

Maybe I misunderstanding but isn't that concatenating the string?

I want to add the values of Broker Fee 1 and Broker Fee 2 - fund amounts.

It only concatenates a string if the data types are strings, otherwise if the data types are a numeric value, it adds them together.

Is there something like

Case
hudnumber = 801 Then BrokerFeeAmt = hudfundamount.801 + hudfundamount.808
End

?

Is hudfundamount.801 a column name??

Provide me an example of the column names and some sample data, and I try to write something for you, but be quick, I'm leaving soon.

801 and 808 are numbers but when I do that is concates the string Broker Fee 1 + Broker Fee 2, it doesn't add the amount.

The fields are the following, the two that I want to add:

Fees Fee Amount
Broker Fee 1 1000.00
Broker Fee 2 500.00

I want to combine these two fields

That is because you have enclosed the field names in quotes, which designates them as a string.
So you aren't adding the values of the fields, you are concatenating the string values 'Broker Fee 1' and 'Broker Fee 2'

My example above should work, you may need to denote the table the column is in.

Example:

Thanks but it didn't work. I'll keep trying.

I see where I am miscommunicating,

The two rows that I am trying to combine are Broker Fee 1 and Broker Fee 2. The column that the values of these two rows are in the Fee Amount column.

Fees Fee Amount
Broker Fee 1 1000.00
Broker Fee 2 500.00

So the desire results would be Broker Fee 1 + Broker Fee 2 As 'Broker Fee'

Fees Fee Amount
Broker Fee 1500.00 -> adding the fee amounts of Broker Fee 1 and Broker Fee 2

I want to see if providing a more detailed result set may help:

Loan# Fee Fee Amount
3246 Admin Fee 175.0
3246 Broker Fee 1 2100.0 These are the two records that I need to combine.
3246 Broker Fee 2 575.0

The Desired Result Set:

Loan# Fee Fee Amount
3246 Admin Fee 175.0
3246 Broker Fee 2675.0

I'm not sure your result can actually be calculated like that within the query.

If BrokerFee1 & BrokerFee2 were columns then you could, but the column is FeeAmount, and there isn't any way of determining which FeeAmount values to calculate.

Ok thanks. I'll think of something or maybe the user will just have to view them as separate fields if it cannot be done.


Thanks for all of your help.