?
Solved

Sql Server Arithmetic Calculations in Stored Procedures

Posted on 2006-05-15
3
Medium Priority
?
295 Views
Last Modified: 2008-01-09
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.
Please make note of the comments for "AdjTotalValue" below.

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
   
    Delete from EAS_IterationData where ret_cadaccountnumber = txroll_cadaccountnumber and txroll_cadaccountnumber = @ORGAccount

    Update EAS_IterationData Set
       AgeAdj = dbo.EAS_AgeAdjustment(@YearBuilt, txroll_YearBuilt, txroll_noticedimprovedvalue),
          SQFTAdj = dbo.EAS_ComSQFTAdjustment(@SQFT, txroll_gba, txroll_noticedimprovedvalue),
          LandSizeAdj = 0,
          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
          AdjTotalPerSqft = AdjTotalValue / 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
          Ratio = AdjTotalPerSqft / @TotalPerSQft
    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
             noi = (SELECT noi_amount FROM noi WHERE noi.CADAccountNumber = eas_iterationdata.ret_CADAccountNumber),
             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.

Any advice is gratly appreciated.



0
Comment
Question by:FastEddie___
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1500 total points
ID: 16684330
because

(   (SELECT noi_amount
       FROM noi
      WHERE noi.CADAccountNumber = @OrgAccount)
 -   (SELECT noi_amount
        FROM noi
       WHERE noi.CADAccountNumber = eas_iterationdata.ret_CADAccountNumber
      )
      )

= 0

as @orgaccount is eas_iterationdata.ret_CADAccountNumber

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

Author Comment

by:FastEddie___
ID: 16685132
Hey Lowfatspread,

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

by:FastEddie___
ID: 16692831
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)


but if I change the first minus to a plus sign instead, it will not work.

Weird huh... Any advice is appreciated.  

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

840 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