Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Question Unanswered: need to know why two calculation methods result in different rounding effect

    Hi All,

    I have a view that does some calculations on data in the database. Note in the two code snippets, that the columns being used in the calculations are all type FLOAT(53).

    My confusion stems from the fact that both code snippets should be functionally the same in my "view" (*snicker* I kill me...), but they return different results which I think are related to rounding issues.

    The first snippet:
    Code:
    CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex
    AS
    SELECT     	PP.PortfolioID AS PortfolioID, 
    		PP.CreateDate AS CreateDate, 
    		(ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgHighPriceRatio) AS HighIndex, 
                          	CASE 	WHEN PPI.CloseIndex IS NULL 
    			THEN 100.00 
    			ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2)
    			END AS CloseIndex, 
    		(ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgLowPriceRatio) AS LowIndex,
    		PP.Volume as Volume
    FROM         	dbo.PortfolioIndex PPI INNER JOIN
                          	dbo.PortfolioPerformance PP ON 
    			PPI.PortfolioID = PP.PortfolioID AND 
    			PPI.CreateDate = PP.PrevDate
    GO
    and it's result set:
    PortfolioID CreateDate HighIndex CloseIndex LowIndex Volume
    10 2/20/2004 781.11 774.17 769.53 527896
    11 2/20/2004 757.97 750.36 742.93 605740
    12 2/20/2004 509.92 501.72 494.85 4180516
    13 2/20/2004 988.23 980.65 973.58 632337
    14 2/20/2004 1283.26 1269.57 1259.37 416145


    And the second snippet:
    Code:
    CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex
    AS
    SELECT     	PP.PortfolioID AS PortfolioID, 
    		PP.CreateDate AS CreateDate, 
    		(CloseIndex * PP.AvgHighPriceRatio) AS HighIndex, 
                          	CASE 	WHEN PPI.CloseIndex IS NULL 
    			THEN 100.00 
    			ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2)
    			END AS CloseIndex, 
    		(CloseIndex * PP.AvgLowPriceRatio) AS LowIndex,
    		PP.Volume as Volume
    FROM         	dbo.PortfolioIndex PPI INNER JOIN
                          	dbo.PortfolioPerformance PP ON 
    			PPI.PortfolioID = PP.PortfolioID AND 
    			PPI.CreateDate = PP.PrevDate
    which returns a different result set:
    PortfolioID CreateDate HighIndex CloseIndex LowIndex Volume
    10 2/20/2004 784.52 774.17 772.89 527896
    11 2/20/2004 755.64 750.36 740.64 605740
    12 2/20/2004 512.43 501.72 497.29 4180516
    13 2/20/2004 989.77 980.65 975.1 632337
    14 2/20/2004 1285.99 1269.57 1262.05 416145


    Specifically, I am concerned with the HighIndex and LowIndex values...since the only modification between the two code snippets is that in the second one, the HighIndex and LowIndex calculations use the column name of CloseIndex (as calculated in the select) in the calcs for those two columns, rather than repeating the code used to calculate the CloseIndex column's value.

    I am confused as to why the results of the HighIndex and LowIndex caculations are different in the two selects, when the only change (in my view/expectations) is that one references the CloseIndex column, and the other one just reproduces the calculation itself

    *scratching head*

    any thoughts? Thanks,
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    do you have a column [CloseIndex] in either of the tables? i think you do, that's why the confusion comes, because while you "think" you're using the explession, it actually uses the real column name, thus - different results.

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Originally posted by ms_sql_dba
    do you have a column [CloseIndex] in either of the tables? i think you do, that's why the confusion comes, because while you "think" you're using the explession, it actually uses the real column name, thus - different results.
    Thanks for your input, and a well-aimed shot

    That was my initial thought also, however, the CloseIndex column in the table is currently NULL (this view is used in the UPDATE select that sets the CloseIndex column to a value other than the NULL value with which the row is initially created).

    In fact, the UPDATE that populates the CloseIndex column (along with the other columns returned by the view under consideration) has as one of the UPDATE qualifiers: WHERE CloseIndex IS NULL.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Wait a minute...

    you may be on to something...let me try a quick test...the PortfolioIndex table in the select is, in fact, the previous day's row...so it would have a value there...let me verify that's what's happening...
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    wait a minute, (both selects did not fit horizontally while comparing) of course you do, and of course the result is different. just look at your highindex:

    (ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgHighPriceRatio) AS HighIndex

    vs.

    (CloseIndex * PP.AvgHighPriceRatio) AS HighIndex


    how can they not be different, unless dailypercharge=0 ?????

  6. #6
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Thumbs up Yep...you got me...

    That'll teach me to doubt a Guru *wink* (OK, probably not, but at least will make me double check before publicly doubting one *LOL*).

    That's exactly what is happening. Thanks much! I appreciate it...of course I could work around it, but it just bugged me that I couldn't figure it out.

    Now...what have we learned, Paul?

    It appears that, as I feared when I originally wrote that view...that I can't reference a column I am calculating and assigning via the "AS..." clause in another portion of the same select???

    I makes logical sense, I suppose...but without delving into the assignment timing of variables in a select (i.e. operator precedence and such...which I still need to do some reading on...)

    So, is there really no way to shorten a select that needs the same calculated result such as this one does? I know in a "normal" stored procedure, I might select and build a variable used in a separate, assignment-select later, but how would a select like this be written without duplicating the code used to calculate CloseIndex three times?

    Thanks again for your help!!!
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Originally posted by ms_sql_dba
    wait a minute, (both selects did not fit horizontally while comparing) of course you do, and of course the result is different. just look at your highindex:

    (ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgHighPriceRatio) AS HighIndex

    vs.

    (CloseIndex * PP.AvgHighPriceRatio) AS HighIndex


    how can they not be different, unless dailypercharge=0 ?????
    *L* well, in the HighIndex calculation you reference here, I was assuming (OK, crossing my fingers and HOPING) that "CloseIndex" as used in the HighIndex calculation was defined just below that point as:

    ...ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) END AS CloseIndex


    See? my problem (OK, one of the LEAST of the MANY problems I have ) is that I thought I might be able to calculate a value and use it on the fly in the same select.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    for that you can write a function:
    Code:
    create function dbo.fn_closeindex(
       @1 float(53),
       @2 float(53),
       @3 float(53 ) returns float(53) as
    begin
       return (
          ROUND((@1 + (@1 * @2 / 100.00)), 2) * @3
       )
    end
    missed it...

    and then use it:

    select dbo.fn_closeindex(PPI.CloseIndex, PP.DailyPerChg, PP.AvgHighPriceRatio) AS HighIndex...
    Last edited by ms_sql_dba; 02-23-04 at 16:39.

  9. #9
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Ah yes...the many-times irreverent and always ready to help Function call...

    I need really to expand my vision iwth SQL Server...My history has been Tandem SQL primarily...there wasn't NEARLY as much you could do besides the basic select stuff...

    It all sounds so simple when you guys lay it out like you do.

    Thanks again for helping widen my viewpoint a tad more today.
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  10. #10
    Join Date
    Sep 2003
    Posts
    522
    guys? i beg your pardon!

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 int PRIMARY KEY, col2 int)
    GO
    
    INSERT INTO myTable99(Col1,Col2)
    SELECT 1,2 UNION ALL
    SELECT 3,4 UNION ALL
    SELECT 5,6 UNION ALL
    SELECT 7,8 UNION ALL
    SELECT 9,0
    GO
    
    SELECT * FROM myTable99
    GO
    
    -- Doesn't work
    SELECT Col1 + Col2 AS Col3
    	, Col3-(Col1+Col2) AS Col4
      FROM myTable99
    GO
    
    --There ya go
        SELECT Col3-(l.Col1+l.Col2) AS Col4
          FROM myTable99 l 
    INNER JOIN (SELECT Col1, Col1 + Col2 AS Col3 
    	      FROM myTable99) As r
    	ON l.Col1 = r.Col1
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Originally posted by ms_sql_dba
    guys? i beg your pardon!
    *LOL* How insensitive of me

    I meant, you "guys" in a manner similar to the way folks refer to "man" as in "Mankind".


    Hmmmm...did I do OK, or am I still in the doghouse? *LOL*

    Anyhow...THANKS, you beautiful....err...I mean "professional looking" Database Babe....err, I mean *blush* "DBA-type of the female persuasion"....well...heck...

    what I MEAN is....THANKS!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by ms_sql_dba
    guys? i beg your pardon!

    chiiiiiiiiiiiiiiiiiiiiiillllllllllllllllllllllll


    Paul...that's why I try to use things like s/he wants to know....because you never know...

    btw...did you see the derived table thingy...you can reference the name if it's passed out like that (just kinda ineffecient going to the same table twice), but I hope you get the idea....

    Database Babe....
    You're from Texas. right? ..get slapped much?

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Thanks Brett, yeah, I'm not sure it has anything to do with Texas, but I DO (for some reason) have some pretty thick callouses on my cheeks...never made the connection until now though *L*

    Hmmm...I always have to watch out on forums where my body language isn't visible...got me somewhat of an odd sense of humor...so I often get virtual-slaps if not the real thang. *L*

    Anyway, yeah, I did see your code...and really appreciate you taking the time to give it a shot for me like that. I know it takes a little time anyway, so of course I appreciate you taking the time to help!

    I do tend to use derived table stuff from time to time for such things, if it makes sense. In this case, I'll probably just leave it as-is (fully typing out the calculation for CloseIndex) and just add some documentation to the view comment header just so folks coming later know what I've been up to.

    I try to be slick where I can, but grudgingly tip my hat in the direction of readability over slick operation when and where I must.

    It's nice to see other folks' point of view in such areas though, and for that, I thank you both!
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Just a caution on functions in queries. You will have to watch the cpu usage for large result sets. The function call has to be made row by row (anyone hear the word cursor?). Functions can be great, but like everything else, have to be used properly. Good luck.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •