Solved

# Sql Server Arithmetic Calculations in Stored Procedures

Posted on 2006-05-15
285 Views
Hello,

I'm having trouble adding two values in a sql server stored procedure.
I'm able to confirm that the two values that I'm trying to add together can indeed be returned individually but when I try to add them toghether no data is displayed.

Here is the stored proc:

=====================================================================================
CREATE Procedure EAS_ComCalcIterationData
@OrgAccount  nvarchar(25),
@PID numeric(18,0),
@ReportYear  numeric(18,0),
@CID int
AS
Declare  @YearBuilt int
Declare  @LandSize Numeric(18,0)
Declare  @SQFT Numeric(18,0)
Declare  @TotalValue Numeric(18,4)
Declare  @TotalPerSQFT Numeric(18,4)
Declare  @ImpValue Numeric(18,4)
Declare  @ReturnAmount Numeric(18,0)
Declare  @CutOffRatio Numeric(18,4)
Set @YearBuilt = (Select txroll_yearbuilt from tbl_taxroll where txroll_cadaccountnumber = @OrgAccount and txroll_year = @ReportYear and txroll_CadID = @CID)
Set @LandSize = (Select txroll_Landsize from tbl_taxroll where txroll_cadaccountnumber = @OrgAccount and txroll_year = @ReportYear and txroll_CadID = @CID)
Set @SQFT = (Select txroll_GBA from tbl_taxroll where txroll_cadaccountnumber = @OrgAccount and txroll_year = @ReportYear and txroll_CadID = @CID)
Set @TotalValue = (Select txroll_NoticedTotalValue from tbl_taxroll where txroll_cadaccountnumber = @OrgAccount and txroll_year = @ReportYear and txroll_CadID = @CID)
Set @ImpValue = (Select txroll_NoticedImprovedValue from tbl_taxroll where txroll_cadaccountnumber = @OrgAccount and txroll_year = @ReportYear and txroll_CadID = @CID)
Set @CutOffRatio = (Select CutOffRatio from EAS_Profiles where PID = @PID)
Set @ReturnAmount = (Select ReturnAmount from EAS_Profiles where PID = @PID)
if (@Sqft > 0)
Set @TotalPerSqft = @TotalValue / @Sqft
else
Return

Update EAS_IterationData Set
TotalPerSQft = txroll_noticedtotalvalue/txroll_gba
From EAS_IterationData, tbl_taxroll Where (eas_iterationdata.txroll_cadaccountnumber = @OrgAccount) and (tbl_taxroll.txroll_CadID = @CID) and (eas_iterationdata.ret_CADAccountNumber = tbl_taxroll.txroll_cadaccountnumber) and (txroll_landsize > 0) and (txroll_noticedtotalvalue > 0) and (txroll_gba > 0) and (@TotalValue > 0) and (pid = @PID) and (eas_iterationdata.txroll_year = @ReportYear) and (tbl_taxroll.txroll_year = @ReportYear)

Update EAS_IterationData Set
--AdjTotalValue =  total value + noi adjustments  // this is the formula to be used
-- AdjTotalValue = txroll_NoticedTotalValue        // this displays the total value with no problem.
-- AdjTotalValue =   ( (   (SELECT noi_amount FROM noi WHERE noi.CADAccountNumber = @OrgAccount)   -   (SELECT noi_amount FROM noi WHERE noi.CADAccountNumber = eas_iterationdata.ret_CADAccountNumber)  )     /     .09 )     *   (SELECT TOP 1 txroll_GBA  FROM tbl_TaxRoll  WHERE txroll_CADAccountNumber = eas_iterationdata.ret_CADAccountNumber ORDER BY txroll_Year DESC)        // this displays the noi adjustment  with no problem.

--  //  but when I try to add them together no data is displayed.
AdjTotalValue = ((txroll_NoticedTotalValue) + (   ( (   (SELECT noi_amount FROM noi WHERE noi.CADAccountNumber = @OrgAccount)   -   (SELECT noi_amount FROM noi WHERE noi.CADAccountNumber = eas_iterationdata.ret_CADAccountNumber)  )     /     .09 )     *   (SELECT TOP 1 txroll_GBA  FROM tbl_TaxRoll  WHERE txroll_CADAccountNumber = eas_iterationdata.ret_CADAccountNumber ORDER BY txroll_Year DESC) ) )
From EAS_IterationData, tbl_taxroll Where (eas_iterationdata.txroll_cadaccountnumber = @OrgAccount) and (tbl_taxroll.txroll_CadID = @CID) and (eas_iterationdata.ret_CADAccountNumber = tbl_taxroll.txroll_cadaccountnumber) and (txroll_landsize > 0) and (txroll_noticedtotalvalue > 0) and (txroll_gba > 0) and (@TotalValue > 0) and (pid = @PID) and (eas_iterationdata.txroll_year = @ReportYear) and (tbl_taxroll.txroll_year = @ReportYear)

Update EAS_IterationData Set
From EAS_IterationData, tbl_taxroll Where (eas_iterationdata.txroll_cadaccountnumber = @OrgAccount) and (tbl_taxroll.txroll_CadID = @CID) and (eas_iterationdata.ret_CADAccountNumber = tbl_taxroll.txroll_cadaccountnumber) and (txroll_landsize > 0) and (txroll_noticedtotalvalue > 0) and (txroll_gba > 0) and (@TotalValue > 0) and (pid = @PID) and (eas_iterationdata.txroll_year = @ReportYear) and (tbl_taxroll.txroll_year = @ReportYear)

Update EAS_IterationData Set
From EAS_IterationData, tbl_taxroll Where (eas_iterationdata.txroll_cadaccountnumber = @OrgAccount) and (tbl_taxroll.txroll_CadID = @CID) and (eas_iterationdata.ret_CADAccountNumber = tbl_taxroll.txroll_cadaccountnumber) and (txroll_landsize > 0) and (txroll_noticedtotalvalue > 0) and (txroll_gba > 0) and (@TotalValue > 0) and (pid = @PID) and (eas_iterationdata.txroll_year = @ReportYear) and (tbl_taxroll.txroll_year = @ReportYear)

Update EAS_IterationData Set
noiadj =   ( (   (SELECT noi_amount FROM noi WHERE noi.CADAccountNumber = @OrgAccount)   -   (SELECT noi_amount FROM noi WHERE noi.CADAccountNumber = eas_iterationdata.ret_CADAccountNumber)  )     /     .09 )     *   (SELECT TOP 1 txroll_GBA  FROM tbl_TaxRoll  WHERE txroll_CADAccountNumber = eas_iterationdata.ret_CADAccountNumber ORDER BY txroll_Year DESC) ,
noisub = (SELECT noi_amount FROM noi WHERE  noi.CADAccountNumber = @OrgAccount  )
From EAS_IterationData, tbl_taxroll Where (eas_iterationdata.txroll_cadaccountnumber = @OrgAccount) and (tbl_taxroll.txroll_CadID = @CID) and (eas_iterationdata.ret_CADAccountNumber = tbl_taxroll.txroll_cadaccountnumber) and (txroll_landsize > 0) and (txroll_noticedtotalvalue > 0) and (txroll_gba > 0) and (@TotalValue > 0) and (pid = @PID) and (eas_iterationdata.txroll_year = @ReportYear) and (tbl_taxroll.txroll_year = @ReportYear)

GO
========================================================================

Can someone please give me an idea of what might be going wrong?
It doesn't make sense that I can display both the "total value" and the "noi adjustments" individually but not be able to add them together.
One thing to note is that in some cases the "total value" and the "noi adjustment" could be a negative number.

0
Question by:FastEddie___

LVL 50

Accepted Solution

because

(   (SELECT noi_amount
FROM noi
-   (SELECT noi_amount
FROM noi
)
)

= 0

please explain in business terms what the calculation is trying to do..
0

Author Comment

Thanks for getting back to me so quicly. Essentially I want to add the total value column (which is expressed as "txroll_NoticedTotalValue") with the NOI Adj column which is a dynamic field. Since I wasn't the original author of this app I'm not 100% sure the business logic but I know that records are taken from two tables, the tbl_taxroll table and the noi table and based on certain criteria in the app the results get placed into a temp table called EAS_IterationData. EAS_IterationData has a field called ret_CADAccountNumber that has the value of account id of a realestate sales transaction. The ret_CadAccountNumer is actually a string and not an int but that is ok because it's only used to get the value of  "noi_amount" which is of type money. Within EAS_IterationData I'm taking two existing columns and adding them together to make a third column called Adj Total Value.

The funny thing is that I am getting back correct data for "Total Value" and "NOI Adj" when I process the data. But when I try to add them together I get back no data.

This line: txroll_NoticedTotalValue returns 577,157 as expected.
and this entire line:

( (   (SELECT noi_amount FROM noi WHERE noi.CADAccountNumber = @OrgAccount)   -   (SELECT noi_amount FROM noi WHERE noi.CADAccountNumber = eas_iterationdata.ret_CADAccountNumber)  )     /     .09 )     *   (SELECT TOP 1 txroll_GBA  FROM tbl_TaxRoll  WHERE txroll_CADAccountNumber = eas_iterationdata.ret_CADAccountNumber ORDER BY txroll_Year DESC)

returns 4,350,192 as I expected.

So given that I can return those values, I don't understand why I can't perform an addition calculation on them.

Please let me know know if you need further info.

Thanks.

0

Author Comment

Oh one other strange anomolly is that when I change the addition character to a substration charactor the statement does work, for example:

This works (notice first artithmatic charater is a minus):

AdjTotalValue = ((txroll_NoticedTotalValue) - (   ( (   (SELECT noi_amount FROM noi WHERE noi.CADAccountNumber = @OrgAccount)   -   (SELECT noi_amount FROM noi WHERE noi.CADAccountNumber = eas_iterationdata.ret_CADAccountNumber)  )     /     .09 )     *   (SELECT TOP 1 txroll_GBA  FROM tbl_TaxRoll  WHERE txroll_CADAccountNumber = eas_iterationdata.ret_CADAccountNumber ORDER BY txroll_Year DESC) ) )
From EAS_IterationData, tbl_taxroll Where (eas_iterationdata.txroll_cadaccountnumber = @OrgAccount) and (tbl_taxroll.txroll_CadID = @CID) and (eas_iterationdata.ret_CADAccountNumber = tbl_taxroll.txroll_cadaccountnumber) and (txroll_landsize > 0) and (txroll_noticedtotalvalue > 0) and (txroll_gba > 0) and (@TotalValue > 0) and (pid = @PID) and (eas_iterationdata.txroll_year = @ReportYear) and (tbl_taxroll.txroll_year = @ReportYear)

Weird huh... Any advice is appreciated.

0

## Featured Post

### Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.