Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] SQL Server decimal rounding

Expand Messages
  • John Warner
    Travis, is that going to work for you? John Warner
    Message 1 of 23 , Jan 21, 2008
    • 0 Attachment
      Travis, is that going to work for you?

      John Warner




      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
      > Sent: Monday, January 21, 2008 12:18 PM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
      > Importance: Low
      >
      >
      > Please note how I said you would hate this. The problem with
      > floats is the way they are 'represented' in memory. As the
      > right side of the decimal point grows in scale the amount of
      > error increases pretty seriously. You might try the money
      > type instead of float for @v1 and @v2. I just played an
      > nothing broke... I just really hate all this casting.
      >
      > John Warner
      >
      >
      >
      >
      > > -----Original Message-----
      > > From: SQLQueriesNoCode@yahoogroups.com
      > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
      > > Sent: Monday, January 21, 2008 12:08 PM
      > > To: 'SQLQueriesNoCode@yahoogroups.com'
      > > Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
      > >
      > >
      > > John,
      > > Microsofts docs suggest staying away from float for
      > > precision financial calcs in favor of decimal & numeric. Like
      > > this from the BOL: "Because of the approximate nature of the
      > > float and real data types, do not use these data types when
      > > exact numeric behavior is required, such as in financial
      > > applications, in operations involving rounding, or in
      > > equality checks. Instead, use the integer, decimal, money, or
      > > smallmoney data types."
      > > However it wouldn't be the first time MS docs have been
      > > completely wrong. It just leaves me confused. :)
      > >
      > > Thanks,
      > > Travis-
      > >
      > > -----Original Message-----
      > > From: John Warner [mailto:john@...]
      > > Sent: Monday, January 21, 2008 10:50 AM
      > > To: SQLQueriesNoCode@yahoogroups.com
      > > Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
      > > Importance: Low
      > >
      > >
      > > You are going to hate this possible solution (I know I do),
      > > but it seems to work.
      > >
      > > DECLARE
      > > @v1 DECIMAL(19, 9),
      > > @v2 DECIMAL(19, 9),
      > > @v3 DECIMAL(19, 9)
      > > SET
      > > @v1 = 7.85
      > > SET
      > > @v2 = 455
      > > SET
      > > @v3 = .000001
      > >
      > > SELECT
      > > 7.85 * 455 * .000001, --calculates normally
      > > cast(Cast(@v1 as float) * cast(@v2 as float) * cast(@v3 as float) as
      > > decimal(19,9)) --rounds
      > >
      > > Like you I can't get on the website, but saw something about
      > > floats that got me thinking.
      > >
      > > John Warner
      > >
      > >
      > >
      > >
      > > > -----Original Message-----
      > > > From: SQLQueriesNoCode@yahoogroups.com
      > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
      > Travis Truax
      > > > Sent: Monday, January 21, 2008 9:37 AM
      > > > To: 'SQLQueriesNoCode@yahoogroups.com'
      > > > Subject: [SQLQueriesNoCode] SQL Server decimal rounding
      > > >
      > > >
      > > > I've seen this issue mentioned before a few different places, but
      > > > I'm not having any luck finding any details at the moment.
      > > >
      > > > I'm doing some financial calculations with decimal(28,10)
      > variables
      > > > and sql server is rounding at the 6th decimal position.
      > > >
      > > > I found this article :
      > > >
      > http://www.sqlmag.com/Article/ArticleID/45921/sql_server_45921.html
      > > > But their website is screwed up & won't let me subscribe -
      > > > and they are out of the office for the holiday.
      > > >
      > > > The article's example is all I could get:
      > > >
      > > > DECLARE @v1 DECIMAL(19, 9), @v2 DECIMAL(19, 9), @v3
      > DECIMAL(19, 9)
      > > > SET @v1 = 7.85 SET @v2 = 455 SET @v3 = .000001
      > > >
      > > > SELECT
      > > > 7.85 * 455 * .000001, --calculates normally
      > > > @v1 * @v2 * @v3 --rounds
      > > >
      > > >
      > > > Anyone not off for the holiday that's familiar with this issue?
      > > >
      > > > Thanks,
      > > > Travis-
      >
      >
      >
      >
      >
      > Yahoo! Groups Links
      >
      >
      >
      >
    • Travis Truax
      I did get different results, but floats round as well, and they apparently don t round the same everytime. That scares me. This is for a Bill of Materials Cost
      Message 2 of 23 , Jan 21, 2008
      • 0 Attachment
        I did get different results, but floats round as well, and they apparently
        don't round the same everytime. That scares me. This is for a Bill of
        Materials Cost Rollup routine, and it's dealing with very small numbers. At
        some point you may have to round things off, but I really need it to be as
        precise as can be and consistent as well.

        I'll try to get access to the sqlmag article tomorrow morning. I'll post
        what I end up doing to solve the problem.

        Thanks John

        Travis-


        -----Original Message-----
        From: John Warner [mailto:john@...]
        Sent: Monday, January 21, 2008 3:14 PM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
        Importance: Low


        Travis, is that going to work for you?

        John Warner




        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
        > Sent: Monday, January 21, 2008 12:18 PM
        > To: SQLQueriesNoCode@yahoogroups.com
        > Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
        > Importance: Low
        >
        >
        > Please note how I said you would hate this. The problem with
        > floats is the way they are 'represented' in memory. As the
        > right side of the decimal point grows in scale the amount of
        > error increases pretty seriously. You might try the money
        > type instead of float for @v1 and @v2. I just played an
        > nothing broke... I just really hate all this casting.
        >
        > John Warner
        >
        >
        >
        >
        > > -----Original Message-----
        > > From: SQLQueriesNoCode@yahoogroups.com
        > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
        > > Sent: Monday, January 21, 2008 12:08 PM
        > > To: 'SQLQueriesNoCode@yahoogroups.com'
        > > Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
        > >
        > >
        > > John,
        > > Microsofts docs suggest staying away from float for
        > > precision financial calcs in favor of decimal & numeric. Like
        > > this from the BOL: "Because of the approximate nature of the
        > > float and real data types, do not use these data types when
        > > exact numeric behavior is required, such as in financial
        > > applications, in operations involving rounding, or in
        > > equality checks. Instead, use the integer, decimal, money, or
        > > smallmoney data types."
        > > However it wouldn't be the first time MS docs have been
        > > completely wrong. It just leaves me confused. :)
        > >
        > > Thanks,
        > > Travis-
        > >
        > > -----Original Message-----
        > > From: John Warner [mailto:john@...]
        > > Sent: Monday, January 21, 2008 10:50 AM
        > > To: SQLQueriesNoCode@yahoogroups.com
        > > Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
        > > Importance: Low
        > >
        > >
        > > You are going to hate this possible solution (I know I do),
        > > but it seems to work.
        > >
        > > DECLARE
        > > @v1 DECIMAL(19, 9),
        > > @v2 DECIMAL(19, 9),
        > > @v3 DECIMAL(19, 9)
        > > SET
        > > @v1 = 7.85
        > > SET
        > > @v2 = 455
        > > SET
        > > @v3 = .000001
        > >
        > > SELECT
        > > 7.85 * 455 * .000001, --calculates normally
        > > cast(Cast(@v1 as float) * cast(@v2 as float) * cast(@v3 as float) as
        > > decimal(19,9)) --rounds
        > >
        > > Like you I can't get on the website, but saw something about
        > > floats that got me thinking.
        > >
        > > John Warner
        > >
        > >
        > >
        > >
        > > > -----Original Message-----
        > > > From: SQLQueriesNoCode@yahoogroups.com
        > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
        > Travis Truax
        > > > Sent: Monday, January 21, 2008 9:37 AM
        > > > To: 'SQLQueriesNoCode@yahoogroups.com'
        > > > Subject: [SQLQueriesNoCode] SQL Server decimal rounding
        > > >
        > > >
        > > > I've seen this issue mentioned before a few different places, but
        > > > I'm not having any luck finding any details at the moment.
        > > >
        > > > I'm doing some financial calculations with decimal(28,10)
        > variables
        > > > and sql server is rounding at the 6th decimal position.
        > > >
        > > > I found this article :
        > > >
        > http://www.sqlmag.com/Article/ArticleID/45921/sql_server_45921.html
        > > > But their website is screwed up & won't let me subscribe -
        > > > and they are out of the office for the holiday.
        > > >
        > > > The article's example is all I could get:
        > > >
        > > > DECLARE @v1 DECIMAL(19, 9), @v2 DECIMAL(19, 9), @v3
        > DECIMAL(19, 9)
        > > > SET @v1 = 7.85 SET @v2 = 455 SET @v3 = .000001
        > > >
        > > > SELECT
        > > > 7.85 * 455 * .000001, --calculates normally
        > > > @v1 * @v2 * @v3 --rounds
        > > >
        > > >
        > > > Anyone not off for the holiday that's familiar with this issue?
        > > >
        > > > Thanks,
        > > > Travis-
        >
        >
        >
        >
        >
        > Yahoo! Groups Links
        >
        >
        >
        >





        Yahoo! Groups Links
      • Noman Aftab
        Travis, You can use a miltiplier.Lets say for a precision of 9 digits, multiply each value by 10^9 (1000000000) to get a whole number, then multiply themself
        Message 3 of 23 , Jan 21, 2008
        • 0 Attachment
          Travis,
          You can use a miltiplier.Lets say for a precision of 9 digits, multiply each value by 10^9 (1000000000) to get a whole number, then multiply themself altogether, and then devide the resultant by the same number.

          cast as bigint, if it overflows

          Travis Truax <travis.truax@...> wrote:
          I did get different results, but floats round as well, and they apparently
          don't round the same everytime. That scares me. This is for a Bill of
          Materials Cost Rollup routine, and it's dealing with very small numbers. At
          some point you may have to round things off, but I really need it to be as
          precise as can be and consistent as well.

          I'll try to get access to the sqlmag article tomorrow morning. I'll post
          what I end up doing to solve the problem.

          Thanks John

          Travis-


          -----Original Message-----
          From: John Warner [mailto:john@...]
          Sent: Monday, January 21, 2008 3:14 PM
          To: SQLQueriesNoCode@yahoogroups.com
          Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
          Importance: Low

          Travis, is that going to work for you?

          John Warner

          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
          > Sent: Monday, January 21, 2008 12:18 PM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
          > Importance: Low
          >
          >
          > Please note how I said you would hate this. The problem with
          > floats is the way they are 'represented' in memory. As the
          > right side of the decimal point grows in scale the amount of
          > error increases pretty seriously. You might try the money
          > type instead of float for @v1 and @v2. I just played an
          > nothing broke... I just really hate all this casting.
          >
          > John Warner
          >
          >
          >
          >
          > > -----Original Message-----
          > > From: SQLQueriesNoCode@yahoogroups.com
          > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
          > > Sent: Monday, January 21, 2008 12:08 PM
          > > To: 'SQLQueriesNoCode@yahoogroups.com'
          > > Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
          > >
          > >
          > > John,
          > > Microsofts docs suggest staying away from float for
          > > precision financial calcs in favor of decimal & numeric. Like
          > > this from the BOL: "Because of the approximate nature of the
          > > float and real data types, do not use these data types when
          > > exact numeric behavior is required, such as in financial
          > > applications, in operations involving rounding, or in
          > > equality checks. Instead, use the integer, decimal, money, or
          > > smallmoney data types."
          > > However it wouldn't be the first time MS docs have been
          > > completely wrong. It just leaves me confused. :)
          > >
          > > Thanks,
          > > Travis-
          > >
          > > -----Original Message-----
          > > From: John Warner [mailto:john@...]
          > > Sent: Monday, January 21, 2008 10:50 AM
          > > To: SQLQueriesNoCode@yahoogroups.com
          > > Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
          > > Importance: Low
          > >
          > >
          > > You are going to hate this possible solution (I know I do),
          > > but it seems to work.
          > >
          > > DECLARE
          > > @v1 DECIMAL(19, 9),
          > > @v2 DECIMAL(19, 9),
          > > @v3 DECIMAL(19, 9)
          > > SET
          > > @v1 = 7.85
          > > SET
          > > @v2 = 455
          > > SET
          > > @v3 = .000001
          > >
          > > SELECT
          > > 7.85 * 455 * .000001, --calculates normally
          > > cast(Cast(@v1 as float) * cast(@v2 as float) * cast(@v3 as float) as
          > > decimal(19,9)) --rounds
          > >
          > > Like you I can't get on the website, but saw something about
          > > floats that got me thinking.
          > >
          > > John Warner
          > >
          > >
          > >
          > >
          > > > -----Original Message-----
          > > > From: SQLQueriesNoCode@yahoogroups.com
          > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
          > Travis Truax
          > > > Sent: Monday, January 21, 2008 9:37 AM
          > > > To: 'SQLQueriesNoCode@yahoogroups.com'
          > > > Subject: [SQLQueriesNoCode] SQL Server decimal rounding
          > > >
          > > >
          > > > I've seen this issue mentioned before a few different places, but
          > > > I'm not having any luck finding any details at the moment.
          > > >
          > > > I'm doing some financial calculations with decimal(28,10)
          > variables
          > > > and sql server is rounding at the 6th decimal position.
          > > >
          > > > I found this article :
          > > >
          > http://www.sqlmag.com/Article/ArticleID/45921/sql_server_45921.html
          > > > But their website is screwed up & won't let me subscribe -
          > > > and they are out of the office for the holiday.
          > > >
          > > > The article's example is all I could get:
          > > >
          > > > DECLARE @v1 DECIMAL(19, 9), @v2 DECIMAL(19, 9), @v3
          > DECIMAL(19, 9)
          > > > SET @v1 = 7.85 SET @v2 = 455 SET @v3 = .000001
          > > >
          > > > SELECT
          > > > 7.85 * 455 * .000001, --calculates normally
          > > > @v1 * @v2 * @v3 --rounds
          > > >
          > > >
          > > > Anyone not off for the holiday that's familiar with this issue?
          > > >
          > > > Thanks,
          > > > Travis-
          >
          >
          >
          >
          >
          > Yahoo! Groups Links
          >
          >
          >
          >

          Yahoo! Groups Links







          Noman Muhammad Aftab
          Software Engineer
          Avanza Solutions
          Tel:+92 21 567 5240 – 42 (ext: 762)
          Fax:+92 21 567 5244
          URL: http://www.avanzasolutions.com

          Email: noman.aftab@...


          ---------------------------------
          Yahoo! Answers - Get better answers from someone who knows. Tryit now.

          [Non-text portions of this message have been removed]
        • Damhuis Anton
          Hi TravisCan you give a easy to use sample of the problem you are having, and the value you are expecting.Then we (or I) can have a go at it.
          Message 4 of 23 , Jan 22, 2008
          • 0 Attachment
            Hi Travis



            Can you give a easy to use sample of the problem you are having, and the
            value you are expecting.

            Then we (or I) can have a go at it.



            Regards

            Anton



            Investment Solutions Disclaimer

            Please note: This email and its contents are subject to the disclaimer as displayed at the following link:
            http://www.investmentsolutions.co.za/EmailLegalNotice.htm
            <http://www.investmentsolutions.co.za/EmailLegalNotice.htm>

            Should you have web access, send and email to legalnotice@... <mailto:legalnotice@...>
            and a copy will be sent to you.


            [Non-text portions of this message have been removed]
          • Noman Aftab
            I have a simple web application with list and add/edit pages. The requirement is that there are two types of users; maker and checker, and for every table
            Message 5 of 23 , Jan 22, 2008
            • 0 Attachment
              I have a simple web application with list and add/edit pages. The requirement is that there are two types of users; maker and checker, and for every table there is a staging table (same structure plus an int cloumn acting as a PK) where requested changes are kept.

              whenever change is requested by the 'maker', the original table is not changed, but the changed values are kept in staging table.

              When the checker approves that change, the values from the staging are pasted (updated) in the original table and are deleted from the staging table.

              Lets suppose the table structure is as follows:

              SERVICE:
              SERVICE_ID (PK)
              SERVICE_NAME
              SERVICE_DISPLAY_NAME
              SERVICE_CODE
              CREATION_DATE

              SERVICE_STAGING:
              SERVICE_STAGING_ID (PK)
              SERVICE_ID
              SERVICE_NAME
              SERVICE_DISPLAY_NAME
              SERVICE_CODE
              CREATION_DATE

              I want a single update query which picks the values from the staging and updates them in the original.


              UPDATE EBPS_SERVICE
              SET SERVICE_NAME =???,
              SERVICE_DISPLAY_NAME =???,
              SERVICE_CODE =???,
              CREATION_DATE =???
              (SUB_QUERY)


              There are other solutions but they will require a round trip to DB.



              Noman Muhammad Aftab
              Software Engineer
              Avanza Solutions
              Tel:+92 21 567 5240 – 42 (ext: 762)
              Fax:+92 21 567 5244
              URL: http://www.avanzasolutions.com

              Email: noman.aftab@...


              ---------------------------------
              Sent from Yahoo! - a smarter inbox.

              [Non-text portions of this message have been removed]
            • Travis Truax
              Thanks for looking at my problem Anton, My first post has a quick example of my problem. Here it is again: DECLARE @v1 DECIMAL(19, 9), @v2 DECIMAL(19, 9), @v3
              Message 6 of 23 , Jan 22, 2008
              • 0 Attachment
                Thanks for looking at my problem Anton,
                My first post has a quick example of my problem.
                Here it is again:

                DECLARE @v1 DECIMAL(19, 9), @v2 DECIMAL(19, 9), @v3
                DECIMAL(19, 9) SET @v1 = 7.85 SET @v2 = 455 SET @v3 = .000001

                SELECT
                7.85 * 455 * .000001, --calculates normally (0.00357175)
                @v1 * @v2 * @v3 --rounds (0.003572)



                -----Original Message-----
                From: Damhuis Anton [mailto:DamhuisA@...]
                Sent: Tuesday, January 22, 2008 6:01 AM
                To: SQLQueriesNoCode@yahoogroups.com
                Subject: [SQLQueriesNoCode] Re: SQL Server decimal rounding


                Hi Travis



                Can you give a easy to use sample of the problem you are having, and the
                value you are expecting.

                Then we (or I) can have a go at it.



                Regards

                Anton



                Investment Solutions Disclaimer

                Please note: This email and its contents are subject to the disclaimer as
                displayed at the following link:
                http://www.investmentsolutions.co.za/EmailLegalNotice.htm
                <http://www.investmentsolutions.co.za/EmailLegalNotice.htm>

                Should you have web access, send and email to legalnotice@...
                <mailto:legalnotice@...>
                and a copy will be sent to you.


                [Non-text portions of this message have been removed]




                Yahoo! Groups Links
              • John Warner
                Darn it, I forgot to bring in my password to SQL Mag website. Please do share what that article says. I hate it that floats are that messed up for your needs,
                Message 7 of 23 , Jan 22, 2008
                • 0 Attachment
                  Darn it, I forgot to bring in my password to SQL Mag website. Please do
                  share what that article says. I hate it that floats are that messed up for
                  your needs, I understand though. Seems like a precision type could be
                  provided. I assume SQL 2005 here.

                  John Warner




                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
                  > Sent: Tuesday, January 22, 2008 8:21 AM
                  > To: 'SQLQueriesNoCode@yahoogroups.com'
                  > Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                  >
                  >
                  > Thanks for looking at my problem Anton,
                  > My first post has a quick example of my problem.
                  > Here it is again:
                  >
                  > DECLARE @v1 DECIMAL(19, 9), @v2 DECIMAL(19, 9), @v3
                  > DECIMAL(19, 9) SET @v1 = 7.85 SET @v2 = 455 SET @v3 = .000001
                  >
                  > SELECT
                  > 7.85 * 455 * .000001, --calculates normally (0.00357175)
                  > @v1 * @v2 * @v3 --rounds (0.003572)
                  >
                  >
                  >
                  > -----Original Message-----
                  > From: Damhuis Anton [mailto:DamhuisA@...]
                  > Sent: Tuesday, January 22, 2008 6:01 AM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                  >
                  >
                  > Hi Travis
                  >
                  >
                  >
                  > Can you give a easy to use sample of the problem you are
                  > having, and the value you are expecting.
                  >
                  > Then we (or I) can have a go at it.
                  >
                  >
                  >
                  > Regards
                  >
                  > Anton
                  >
                  >
                  >
                  > Investment Solutions Disclaimer
                  >
                  > Please note: This email and its contents are subject to the
                  > disclaimer as displayed at the following link:
                  > http://www.investmentsolutions.co.za/EmailLegalNotice.htm
                  > <http://www.investmentsolutions.co.za/EmailLegalNotice.htm>
                  >
                  > Should you have web access, send and email to
                  > legalnotice@... <mailto:legalnotice@...>
                  > and a copy will be sent to you.
                  >
                  >
                  > [Non-text portions of this message have been removed]
                  >
                  >
                  >
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                  >
                  >
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                  >
                • Travis Truax
                  Oh yes - I should have mentioned I was working with SQL2005. Thank you John ... From: John Warner [mailto:john@jwarner.com] Sent: Tuesday, January 22, 2008
                  Message 8 of 23 , Jan 22, 2008
                  • 0 Attachment
                    Oh yes - I should have mentioned I was working with SQL2005.
                    Thank you John

                    -----Original Message-----
                    From: John Warner [mailto:john@...]
                    Sent: Tuesday, January 22, 2008 7:26 AM
                    To: SQLQueriesNoCode@yahoogroups.com
                    Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                    Importance: Low


                    Darn it, I forgot to bring in my password to SQL Mag website. Please do
                    share what that article says. I hate it that floats are that messed up for
                    your needs, I understand though. Seems like a precision type could be
                    provided. I assume SQL 2005 here.

                    John Warner




                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
                    > Sent: Tuesday, January 22, 2008 8:21 AM
                    > To: 'SQLQueriesNoCode@yahoogroups.com'
                    > Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                    >
                    >
                    > Thanks for looking at my problem Anton,
                    > My first post has a quick example of my problem.
                    > Here it is again:
                    >
                    > DECLARE @v1 DECIMAL(19, 9), @v2 DECIMAL(19, 9), @v3
                    > DECIMAL(19, 9) SET @v1 = 7.85 SET @v2 = 455 SET @v3 = .000001
                    >
                    > SELECT
                    > 7.85 * 455 * .000001, --calculates normally (0.00357175)
                    > @v1 * @v2 * @v3 --rounds (0.003572)
                    >
                    >
                    >
                    > -----Original Message-----
                    > From: Damhuis Anton [mailto:DamhuisA@...]
                    > Sent: Tuesday, January 22, 2008 6:01 AM
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Subject: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                    >
                    >
                    > Hi Travis
                    >
                    >
                    >
                    > Can you give a easy to use sample of the problem you are
                    > having, and the value you are expecting.
                    >
                    > Then we (or I) can have a go at it.
                    >
                    >
                    >
                    > Regards
                    >
                    > Anton
                    >
                    >
                    >
                    > Investment Solutions Disclaimer
                    >
                    > Please note: This email and its contents are subject to the
                    > disclaimer as displayed at the following link:
                    > http://www.investmentsolutions.co.za/EmailLegalNotice.htm
                    > <http://www.investmentsolutions.co.za/EmailLegalNotice.htm>
                    >
                    > Should you have web access, send and email to
                    > legalnotice@... <mailto:legalnotice@...>
                    > and a copy will be sent to you.
                    >
                    >
                    > [Non-text portions of this message have been removed]
                    >
                    >
                    >
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >
                    >
                    >
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >
                    >





                    Yahoo! Groups Links
                  • Damhuis Anton
                    Hi JohnOh sorry I thoght that was a sample of the web site.I played around for 5 minutes and came up with the following:It seems like the decimal
                    Message 9 of 23 , Jan 23, 2008
                    • 0 Attachment
                      Hi John

                      Oh sorry I thoght that was a sample of the web site.



                      I played around for 5 minutes and came up with the following:

                      It seems like the decimal places goes haywire when the first two numbers
                      are multiplied, and then multiplying the 3rd number uses the rounded
                      value



                      SELECT 7.85 * 455 * .000001, --calculates normally
                      (0.00357175)

                      ( Cast(@v1 * @v2 as DECIMAL(19, 9)) * @v3) --no longer rounds
                      (0.003572)



                      What is strange is if you use Decimal(19,10) then the problem is not
                      there.

                      DECLARE @v1 DECIMAL(19, 10)

                      Declare @v2 DECIMAL(19, 10)

                      Declare @v3 DECIMAL(19, 10)



                      SET @v1 = 7.85

                      SET @v2 = 455

                      SET @v3 = .000001



                      SELECT 7.85 * 455 * .000001, --calculates normally (0.00357175)

                      @v1 * @v2 * @v3 -- this works fine.



                      Regards

                      Anton Damhuis





                      Investment Solutions Disclaimer

                      Please note: This email and its contents are subject to the disclaimer as displayed at the following link:
                      http://www.investmentsolutions.co.za/EmailLegalNotice.htm
                      <http://www.investmentsolutions.co.za/EmailLegalNotice.htm>

                      Should you have web access, send and email to legalnotice@... <mailto:legalnotice@...>
                      and a copy will be sent to you.


                      [Non-text portions of this message have been removed]
                    • John Warner
                      Boy, do I like your solution far far better than mine! I just hope I can make myself remember this sometime in the future. I wonder why SQL Server incorrectly
                      Message 10 of 23 , Jan 23, 2008
                      • 0 Attachment
                        Boy, do I like your solution far far better than mine! I just hope I can
                        make myself remember this sometime in the future. I wonder why SQL Server
                        incorrectly casts the already defined decimals at the end of the first
                        multiplication such that you have to cast the result back into the proper
                        type as you have done here. Just seems odd and a shame you have to figure
                        this out rather than have the behavior documented in Books Online. Never
                        the less, great solution and far more elegant then what I came up with.

                        John Warner


                        > -----Original Message-----
                        > From: SQLQueriesNoCode@yahoogroups.com
                        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Damhuis Anton
                        > Sent: Wednesday, January 23, 2008 7:18 AM
                        > To: SQLQueriesNoCode@yahoogroups.com
                        > Subject: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                        >
                        > Hi John
                        >
                        > Oh sorry I thoght that was a sample of the web site.
                        >
                        >
                        >
                        > I played around for 5 minutes and came up with the following:
                        >
                        > It seems like the decimal places goes haywire when the first two numbers
                        > are multiplied, and then multiplying the 3rd number uses the rounded
                        > value
                        >
                        >
                        >
                        > SELECT 7.85 * 455 * .000001, --calculates normally
                        > (0.00357175)
                        >
                        > ( Cast(@v1 * @v2 as DECIMAL(19, 9)) * @v3) --no longer rounds
                        > (0.003572)
                        >
                        >
                        >
                        > What is strange is if you use Decimal(19,10) then the problem is not
                        > there.
                        >
                        > DECLARE @v1 DECIMAL(19, 10)
                        >
                        > Declare @v2 DECIMAL(19, 10)
                        >
                        > Declare @v3 DECIMAL(19, 10)
                        >
                        >
                        >
                        > SET @v1 = 7.85
                        >
                        > SET @v2 = 455
                        >
                        > SET @v3 = .000001
                        >
                        >
                        >
                        > SELECT 7.85 * 455 * .000001, --calculates normally (0.00357175)
                        >
                        > @v1 * @v2 * @v3 -- this works fine.
                        >
                        >
                        >
                        > Regards
                        >
                        > Anton Damhuis
                        >
                        >
                        >
                        >
                        >
                        > Investment Solutions Disclaimer
                        >
                        > Please note: This email and its contents are subject to the disclaimer
                        as
                        > displayed at the following link:
                        > http://www.investmentsolutions.co.za/EmailLegalNotice.htm
                        > <http://www.investmentsolutions.co.za/EmailLegalNotice.htm>
                        >
                        > Should you have web access, send and email to legalnotice@...
                        > <mailto:legalnotice@...>
                        > and a copy will be sent to you.
                        >
                        >
                        > [Non-text portions of this message have been removed]
                        >
                        >
                        >
                        >
                        > Yahoo! Groups Links
                        >
                        >
                        >
                      • John Warner
                        Travis check this out. Based on Damhuis s wonderful suggestion. Note plug this into QA I was rather surprised. DECLARE @v1 DECIMAL(19, 9), @v2 DECIMAL(19, 9),
                        Message 11 of 23 , Jan 23, 2008
                        • 0 Attachment
                          Travis check this out. Based on Damhuis's wonderful suggestion. Note plug
                          this into QA I was rather surprised.

                          DECLARE
                          @v1 DECIMAL(19, 9),
                          @v2 DECIMAL(19, 9),
                          @v3 DECIMAL(19, 9)
                          SET
                          @v1 = 7.86
                          SET
                          @v2 = 454
                          SET
                          @v3 = .000001

                          SELECT
                          7.84 * 456 * .000001, --calculates normally
                          cast(@v1 * @v2 AS DECIMAL(19,9)) * @v3

                          Note the values are different from your original ones. The first SELECT in
                          fact rounds. Damhuis's solution is actually more accurate then what 'we'
                          both thought was the correct result.

                          John Warner


                          > -----Original Message-----
                          > From: SQLQueriesNoCode@yahoogroups.com
                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
                          > Sent: Tuesday, January 22, 2008 9:03 AM
                          > To: 'SQLQueriesNoCode@yahoogroups.com'
                          > Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                          >
                          > Oh yes - I should have mentioned I was working with SQL2005.
                          > Thank you John
                          >
                          > -----Original Message-----
                          > From: John Warner [mailto:john@...]
                          > Sent: Tuesday, January 22, 2008 7:26 AM
                          > To: SQLQueriesNoCode@yahoogroups.com
                          > Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                          > Importance: Low
                          >
                          >
                          > Darn it, I forgot to bring in my password to SQL Mag website. Please do
                          > share what that article says. I hate it that floats are that messed up
                          for
                          > your needs, I understand though. Seems like a precision type could be
                          > provided. I assume SQL 2005 here.
                          >
                          > John Warner
                          >
                          >
                          >
                          >
                          > > -----Original Message-----
                          > > From: SQLQueriesNoCode@yahoogroups.com
                          > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
                          > > Sent: Tuesday, January 22, 2008 8:21 AM
                          > > To: 'SQLQueriesNoCode@yahoogroups.com'
                          > > Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                          > >
                          > >
                          > > Thanks for looking at my problem Anton,
                          > > My first post has a quick example of my problem.
                          > > Here it is again:
                          > >
                          > > DECLARE @v1 DECIMAL(19, 9), @v2 DECIMAL(19, 9), @v3
                          > > DECIMAL(19, 9) SET @v1 = 7.85 SET @v2 = 455 SET @v3 = .000001
                          > >
                          > > SELECT
                          > > 7.85 * 455 * .000001, --calculates normally (0.00357175)
                          > > @v1 * @v2 * @v3 --rounds (0.003572)
                          > >
                          > >
                          > >
                          > > -----Original Message-----
                          > > From: Damhuis Anton [mailto:DamhuisA@...]
                          > > Sent: Tuesday, January 22, 2008 6:01 AM
                          > > To: SQLQueriesNoCode@yahoogroups.com
                          > > Subject: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                          > >
                          > >
                          > > Hi Travis
                          > >
                          > >
                          > >
                          > > Can you give a easy to use sample of the problem you are
                          > > having, and the value you are expecting.
                          > >
                          > > Then we (or I) can have a go at it.
                          > >
                          > >
                          > >
                          > > Regards
                          > >
                          > > Anton
                          > >
                          > >
                          > >
                          > > Investment Solutions Disclaimer
                          > >
                          > > Please note: This email and its contents are subject to the
                          > > disclaimer as displayed at the following link:
                          > > http://www.investmentsolutions.co.za/EmailLegalNotice.htm
                          > > <http://www.investmentsolutions.co.za/EmailLegalNotice.htm>
                          > >
                          > > Should you have web access, send and email to
                          > > legalnotice@... <mailto:legalnotice@...>
                          > > and a copy will be sent to you.
                          > >
                          > >
                          > > [Non-text portions of this message have been removed]
                          > >
                          > >
                          > >
                          > >
                          > > Yahoo! Groups Links
                          > >
                          > >
                          > >
                          > >
                          > >
                          > >
                          > > Yahoo! Groups Links
                          > >
                          > >
                          > >
                          > >
                          >
                          >
                          >
                          >
                          >
                          > Yahoo! Groups Links
                          >
                          >
                          >
                          >
                          >
                          >
                          > Yahoo! Groups Links
                          >
                          >
                          >
                        • John Warner
                          How does one know when a Checker approves? John Warner ... requirement ... table ... PK) ... table. ... and
                          Message 12 of 23 , Jan 23, 2008
                          • 0 Attachment
                            How does one know when a Checker approves?

                            John Warner


                            > -----Original Message-----
                            > From: SQLQueriesNoCode@yahoogroups.com
                            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
                            > Sent: Tuesday, January 22, 2008 7:04 AM
                            > To: SQLQueriesNoCode@yahoogroups.com
                            > Subject: [SQLQueriesNoCode] Single Update Query....Possible?
                            >
                            > I have a simple web application with list and add/edit pages. The
                            requirement
                            > is that there are two types of users; maker and checker, and for every
                            table
                            > there is a staging table (same structure plus an int cloumn acting as a
                            PK)
                            > where requested changes are kept.
                            >
                            > whenever change is requested by the 'maker', the original table is not
                            > changed, but the changed values are kept in staging table.
                            >
                            > When the checker approves that change, the values from the staging are
                            > pasted (updated) in the original table and are deleted from the staging
                            table.
                            >
                            > Lets suppose the table structure is as follows:
                            >
                            > SERVICE:
                            > SERVICE_ID (PK)
                            > SERVICE_NAME
                            > SERVICE_DISPLAY_NAME
                            > SERVICE_CODE
                            > CREATION_DATE
                            >
                            > SERVICE_STAGING:
                            > SERVICE_STAGING_ID (PK)
                            > SERVICE_ID
                            > SERVICE_NAME
                            > SERVICE_DISPLAY_NAME
                            > SERVICE_CODE
                            > CREATION_DATE
                            >
                            > I want a single update query which picks the values from the staging
                            and
                            > updates them in the original.
                            >
                            >
                            > UPDATE EBPS_SERVICE
                            > SET SERVICE_NAME =???,
                            > SERVICE_DISPLAY_NAME =???,
                            > SERVICE_CODE =???,
                            > CREATION_DATE =???
                            > (SUB_QUERY)
                            >
                            >
                            > There are other solutions but they will require a round trip to DB.
                            >
                            >
                            >
                            > Noman Muhammad Aftab
                            > Software Engineer
                            > Avanza Solutions
                            > Tel:+92 21 567 5240 - 42 (ext: 762)
                            > Fax:+92 21 567 5244
                            > URL: http://www.avanzasolutions.com
                            >
                            > Email: noman.aftab@...
                            >
                            >
                            > ---------------------------------
                            > Sent from Yahoo! - a smarter inbox.
                            >
                            > [Non-text portions of this message have been removed]
                            >
                            >
                            >
                            >
                            > Yahoo! Groups Links
                            >
                            >
                            >
                          • Travis Truax
                            Thanks guys, This is actually the same solution from the sqlmag article. They included a small table of how SQL Server calculates result precision & scale:
                            Message 13 of 23 , Jan 23, 2008
                            • 0 Attachment
                              Thanks guys,
                              This is actually the same solution from the sqlmag article. They
                              included a small table of how SQL Server calculates result precision &
                              scale:

                              Operation Result Precision
                              Result Scale
                              e1+e2 max(s1,s2)+ max(p1-s1,p2-s2)+ 1 max(s1,s2)
                              e1-e2 max(s1,s2)+ max(p1-s1,p2-s2)
                              max(s1,s2)
                              e1*e2 (p1-s1)+(p2-s2)+ 1 s1 +
                              s2
                              e1/e2 p1 - s1 + s2 + max(6,s1 + p2 + 1)
                              max(6,s1 + p2)

                              I've edited the table from the article, however - because the author gives a
                              slightly different precision calculation in the text of the article
                              (conflicting with the table).
                              (I'm still not sure everything in the table is right).

                              Using their table, multiplying DECIMAL(19,9) * DECIMAL(19,9) *
                              DECIMAL(19,9):
                              ((19-9)+(19-9)+ 1)+((19-9)+ 1) = 32

                              Leaving 6 digits (out of the maximum SQL2005 decimal max of 38) left for the
                              scale, even though it may have needed more to keep from having to round. So
                              it will round to 6 decimal positions.

                              Using their table, multiplying DECIMAL(19,10) * DECIMAL(19,10) *
                              DECIMAL(19,10):
                              ((19-10)+(19-10)+ 1)+((19-10)+ 1) = 29

                              Leaving more digits for the scale before rounding.

                              According to the article, you need to cast each expression seperately,
                              so a simple looking expression like @a * @b * @c * @d can turn into a
                              mess of parenthesis like so:

                              CAST(@a * CAST(@b * CAST(@c * @d AS DECIMAL(19,9)) AS DECIMAL(19,9))AS
                              DECIMAL(19,9))

                              So I broke my calculations up into smaller pieces and used a temp variable
                              to hold the interim values, and since this was actually happening in a
                              cursor, I wanted to make sure no previous values could sneak in between
                              iterations, so I initialized the temp variable to NULL each iteration,
                              before using it.

                              THAT HAD A DETRIMENTAL EFFECT ON THE SCRIPT. I was surprised that it had
                              such an impact, but it took a script that I thought was long at completing
                              in 37 seconds to taking well over half an hour. Changing the initialization
                              to 0 instead of NULL fixed the issue.

                              Anyone know what that's about? :)

                              Thanks for reading.

                              Travis-

                              -----Original Message-----
                              From: John Warner [mailto:john@...]
                              Sent: Wednesday, January 23, 2008 7:35 AM
                              To: SQLQueriesNoCode@yahoogroups.com
                              Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                              Importance: Low


                              Travis check this out. Based on Damhuis's wonderful suggestion. Note plug
                              this into QA I was rather surprised.

                              DECLARE
                              @v1 DECIMAL(19, 9),
                              @v2 DECIMAL(19, 9),
                              @v3 DECIMAL(19, 9)
                              SET
                              @v1 = 7.86
                              SET
                              @v2 = 454
                              SET
                              @v3 = .000001

                              SELECT
                              7.84 * 456 * .000001, --calculates normally
                              cast(@v1 * @v2 AS DECIMAL(19,9)) * @v3

                              Note the values are different from your original ones. The first SELECT in
                              fact rounds. Damhuis's solution is actually more accurate then what 'we'
                              both thought was the correct result.

                              John Warner


                              > -----Original Message-----
                              > From: SQLQueriesNoCode@yahoogroups.com
                              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
                              > Sent: Tuesday, January 22, 2008 9:03 AM
                              > To: 'SQLQueriesNoCode@yahoogroups.com'
                              > Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                              >
                              > Oh yes - I should have mentioned I was working with SQL2005.
                              > Thank you John
                              >
                              > -----Original Message-----
                              > From: John Warner [mailto:john@...]
                              > Sent: Tuesday, January 22, 2008 7:26 AM
                              > To: SQLQueriesNoCode@yahoogroups.com
                              > Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                              > Importance: Low
                              >
                              >
                              > Darn it, I forgot to bring in my password to SQL Mag website. Please do
                              > share what that article says. I hate it that floats are that messed up
                              for
                              > your needs, I understand though. Seems like a precision type could be
                              > provided. I assume SQL 2005 here.
                              >
                              > John Warner
                              >
                              >
                              >
                              >
                              > > -----Original Message-----
                              > > From: SQLQueriesNoCode@yahoogroups.com
                              > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
                              > > Sent: Tuesday, January 22, 2008 8:21 AM
                              > > To: 'SQLQueriesNoCode@yahoogroups.com'
                              > > Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                              > >
                              > >
                              > > Thanks for looking at my problem Anton,
                              > > My first post has a quick example of my problem.
                              > > Here it is again:
                              > >
                              > > DECLARE @v1 DECIMAL(19, 9), @v2 DECIMAL(19, 9), @v3
                              > > DECIMAL(19, 9) SET @v1 = 7.85 SET @v2 = 455 SET @v3 = .000001
                              > >
                              > > SELECT
                              > > 7.85 * 455 * .000001, --calculates normally (0.00357175)
                              > > @v1 * @v2 * @v3 --rounds (0.003572)
                              > >
                              > >
                              > >
                              > > -----Original Message-----
                              > > From: Damhuis Anton [mailto:DamhuisA@...]
                              > > Sent: Tuesday, January 22, 2008 6:01 AM
                              > > To: SQLQueriesNoCode@yahoogroups.com
                              > > Subject: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                              > >
                              > >
                              > > Hi Travis
                              > >
                              > >
                              > >
                              > > Can you give a easy to use sample of the problem you are
                              > > having, and the value you are expecting.
                              > >
                              > > Then we (or I) can have a go at it.
                              > >
                              > >
                              > >
                              > > Regards
                              > >
                              > > Anton
                              > >
                              > >
                              > >
                              > > Investment Solutions Disclaimer
                              > >
                              > > Please note: This email and its contents are subject to the
                              > > disclaimer as displayed at the following link:
                              > > http://www.investmentsolutions.co.za/EmailLegalNotice.htm
                              > > <http://www.investmentsolutions.co.za/EmailLegalNotice.htm>
                              > >
                              > > Should you have web access, send and email to
                              > > legalnotice@... <mailto:legalnotice@...>
                              > > and a copy will be sent to you.
                              > >
                              > >
                              > > [Non-text portions of this message have been removed]
                              > >
                              > >
                              > >
                              > >
                              > > Yahoo! Groups Links
                              > >
                              > >
                              > >
                              > >
                              > >
                              > >
                              > > Yahoo! Groups Links
                              > >
                              > >
                              > >
                              > >
                              >
                              >
                              >
                              >
                              >
                              > Yahoo! Groups Links
                              >
                              >
                              >
                              >
                              >
                              >
                              > Yahoo! Groups Links
                              >
                              >
                              >





                              Yahoo! Groups Links
                            • John Warner
                              Zero instead of Null may have to do with the object having to be completely recreated for Null where zero is just another assignment operation. This is just
                              Message 14 of 23 , Jan 23, 2008
                              • 0 Attachment
                                Zero instead of Null may have to do with the 'object' having to be
                                completely recreated for Null where zero is just another assignment
                                operation. This is just a guess and should be taken with a very large
                                (world record size) grain of salt.

                                John Warner

                                > -----Original Message-----
                                > From: SQLQueriesNoCode@yahoogroups.com
                                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
                                > Sent: Wednesday, January 23, 2008 10:21 AM
                                > To: 'SQLQueriesNoCode@yahoogroups.com'
                                > Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                                >
                                > Thanks guys,
                                > This is actually the same solution from the sqlmag article. They
                                > included a small table of how SQL Server calculates result precision &
                                > scale:
                                >
                                > Operation Result Precision
                                > Result Scale
                                > e1+e2 max(s1,s2)+ max(p1-s1,p2-s2)+ 1 max(s1,s2)
                                > e1-e2 max(s1,s2)+ max(p1-s1,p2-s2)
                                > max(s1,s2)
                                > e1*e2 (p1-s1)+(p2-s2)+ 1 s1
                                +
                                > s2
                                > e1/e2 p1 - s1 + s2 + max(6,s1 + p2 + 1)
                                > max(6,s1 + p2)
                                >
                                > I've edited the table from the article, however - because the author
                                gives a
                                > slightly different precision calculation in the text of the article
                                > (conflicting with the table).
                                > (I'm still not sure everything in the table is right).
                                >
                                > Using their table, multiplying DECIMAL(19,9) * DECIMAL(19,9) *
                                > DECIMAL(19,9):
                                > ((19-9)+(19-9)+ 1)+((19-9)+ 1) = 32
                                >
                                > Leaving 6 digits (out of the maximum SQL2005 decimal max of 38) left for
                                the
                                > scale, even though it may have needed more to keep from having to round.
                                So
                                > it will round to 6 decimal positions.
                                >
                                > Using their table, multiplying DECIMAL(19,10) * DECIMAL(19,10) *
                                > DECIMAL(19,10):
                                > ((19-10)+(19-10)+ 1)+((19-10)+ 1) = 29
                                >
                                > Leaving more digits for the scale before rounding.
                                >
                                > According to the article, you need to cast each expression seperately,
                                > so a simple looking expression like @a * @b * @c * @d can turn into a
                                > mess of parenthesis like so:
                                >
                                > CAST(@a * CAST(@b * CAST(@c * @d AS DECIMAL(19,9)) AS
                                > DECIMAL(19,9))AS
                                > DECIMAL(19,9))
                                >
                                > So I broke my calculations up into smaller pieces and used a temp
                                variable
                                > to hold the interim values, and since this was actually happening in a
                                > cursor, I wanted to make sure no previous values could sneak in between
                                > iterations, so I initialized the temp variable to NULL each iteration,
                                > before using it.
                                >
                                > THAT HAD A DETRIMENTAL EFFECT ON THE SCRIPT. I was surprised that
                                > it had
                                > such an impact, but it took a script that I thought was long at
                                completing
                                > in 37 seconds to taking well over half an hour. Changing the
                                initialization
                                > to 0 instead of NULL fixed the issue.
                                >
                                > Anyone know what that's about? :)
                                >
                                > Thanks for reading.
                                >
                                > Travis-
                                >
                                > -----Original Message-----
                                > From: John Warner [mailto:john@...]
                                > Sent: Wednesday, January 23, 2008 7:35 AM
                                > To: SQLQueriesNoCode@yahoogroups.com
                                > Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                                > Importance: Low
                                >
                                >
                                > Travis check this out. Based on Damhuis's wonderful suggestion. Note
                                plug
                                > this into QA I was rather surprised.
                                >
                                > DECLARE
                                > @v1 DECIMAL(19, 9),
                                > @v2 DECIMAL(19, 9),
                                > @v3 DECIMAL(19, 9)
                                > SET
                                > @v1 = 7.86
                                > SET
                                > @v2 = 454
                                > SET
                                > @v3 = .000001
                                >
                                > SELECT
                                > 7.84 * 456 * .000001, --calculates normally
                                > cast(@v1 * @v2 AS DECIMAL(19,9)) * @v3
                                >
                                > Note the values are different from your original ones. The first SELECT
                                in
                                > fact rounds. Damhuis's solution is actually more accurate then what 'we'
                                > both thought was the correct result.
                                >
                                > John Warner
                                >
                                >
                                > > -----Original Message-----
                                > > From: SQLQueriesNoCode@yahoogroups.com
                                > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
                                > > Sent: Tuesday, January 22, 2008 9:03 AM
                                > > To: 'SQLQueriesNoCode@yahoogroups.com'
                                > > Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                                > >
                                > > Oh yes - I should have mentioned I was working with SQL2005.
                                > > Thank you John
                                > >
                                > > -----Original Message-----
                                > > From: John Warner [mailto:john@...]
                                > > Sent: Tuesday, January 22, 2008 7:26 AM
                                > > To: SQLQueriesNoCode@yahoogroups.com
                                > > Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                                > > Importance: Low
                                > >
                                > >
                                > > Darn it, I forgot to bring in my password to SQL Mag website. Please
                                do
                                > > share what that article says. I hate it that floats are that messed up
                                > for
                                > > your needs, I understand though. Seems like a precision type could be
                                > > provided. I assume SQL 2005 here.
                                > >
                                > > John Warner
                                > >
                                > >
                                > >
                                > >
                                > > > -----Original Message-----
                                > > > From: SQLQueriesNoCode@yahoogroups.com
                                > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis
                                > Truax
                                > > > Sent: Tuesday, January 22, 2008 8:21 AM
                                > > > To: 'SQLQueriesNoCode@yahoogroups.com'
                                > > > Subject: RE: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                                > > >
                                > > >
                                > > > Thanks for looking at my problem Anton,
                                > > > My first post has a quick example of my problem.
                                > > > Here it is again:
                                > > >
                                > > > DECLARE @v1 DECIMAL(19, 9), @v2 DECIMAL(19, 9), @v3
                                > > > DECIMAL(19, 9) SET @v1 = 7.85 SET @v2 = 455 SET @v3 = .000001
                                > > >
                                > > > SELECT
                                > > > 7.85 * 455 * .000001, --calculates normally (0.00357175)
                                > > > @v1 * @v2 * @v3 --rounds (0.003572)
                                > > >
                                > > >
                                > > >
                                > > > -----Original Message-----
                                > > > From: Damhuis Anton [mailto:DamhuisA@...]
                                > > > Sent: Tuesday, January 22, 2008 6:01 AM
                                > > > To: SQLQueriesNoCode@yahoogroups.com
                                > > > Subject: [SQLQueriesNoCode] Re: SQL Server decimal rounding
                                > > >
                                > > >
                                > > > Hi Travis
                                > > >
                                > > >
                                > > >
                                > > > Can you give a easy to use sample of the problem you are
                                > > > having, and the value you are expecting.
                                > > >
                                > > > Then we (or I) can have a go at it.
                                > > >
                                > > >
                                > > >
                                > > > Regards
                                > > >
                                > > > Anton
                                > > >
                                > > >
                                > > >
                                > > > Investment Solutions Disclaimer
                                > > >
                                > > > Please note: This email and its contents are subject to the
                                > > > disclaimer as displayed at the following link:
                                > > > http://www.investmentsolutions.co.za/EmailLegalNotice.htm
                                > > > <http://www.investmentsolutions.co.za/EmailLegalNotice.htm>
                                > > >
                                > > > Should you have web access, send and email to
                                > > > legalnotice@... <mailto:legalnotice@...>
                                > > > and a copy will be sent to you.
                                > > >
                                > > >
                                > > > [Non-text portions of this message have been removed]
                                > > >
                                > > >
                                > > >
                                > > >
                                > > > Yahoo! Groups Links
                                > > >
                                > > >
                                > > >
                                > > >
                                > > >
                                > > >
                                > > > Yahoo! Groups Links
                                > > >
                                > > >
                                > > >
                                > > >
                                > >
                                > >
                                > >
                                > >
                                > >
                                > > Yahoo! Groups Links
                                > >
                                > >
                                > >
                                > >
                                > >
                                > >
                                > > Yahoo! Groups Links
                                > >
                                > >
                                > >
                                >
                                >
                                >
                                >
                                >
                                > Yahoo! Groups Links
                                >
                                >
                                >
                                >
                                >
                                >
                                > Yahoo! Groups Links
                                >
                                >
                                >
                              • Paul Anca
                                If you are using SQL Server, here is your query: update SERVICE set SERVICE_NAME = B.SERVICE_NAME, SERVICE_DISPLAY_NAME = B.SERVICE_DISPLAY_NAME, SERVICE_CODE
                                Message 15 of 23 , Jan 23, 2008
                                • 0 Attachment
                                  If you are using SQL Server, here is your query:

                                  update
                                  SERVICE

                                  set
                                  SERVICE_NAME = B.SERVICE_NAME,
                                  SERVICE_DISPLAY_NAME = B.SERVICE_DISPLAY_NAME,
                                  SERVICE_CODE = B.SERVICE_CODE,
                                  CREATION_DATE = B.CREATION_DATE

                                  from
                                  SERVICE A
                                  inner join
                                  SERVICE_STAGING B
                                  on
                                  A.SERVICE_ID = B.SERVICE_ID

                                  where
                                  B.APPROVED = true



                                  Best regards,

                                  Paul Anca
                                  www.ancasolutions.com


                                  >I have a simple web application with list and add/edit pages. The requirement
                                  >is that there are two types of users; maker and checker, and for every table
                                  >there is a staging table (same structure plus an int cloumn acting as a PK)
                                  >where requested changes are kept.
                                  >
                                  > whenever change is requested by the 'maker', the original table is not
                                  >changed, but the changed values are kept in staging table.
                                  >
                                  > When the checker approves that change, the values from the staging are
                                  >pasted (updated) in the original table and are deleted from the staging
                                  >table.
                                  >
                                  > Lets suppose the table structure is as follows:
                                  >
                                  > SERVICE:
                                  > SERVICE_ID (PK)
                                  > SERVICE_NAME
                                  > SERVICE_DISPLAY_NAME
                                  > SERVICE_CODE
                                  > CREATION_DATE
                                  >
                                  > SERVICE_STAGING:
                                  > SERVICE_STAGING_ID (PK)
                                  > SERVICE_ID
                                  > SERVICE_NAME
                                  > SERVICE_DISPLAY_NAME
                                  > SERVICE_CODE
                                  > CREATION_DATE
                                  >
                                  > I want a single update query which picks the values from the staging and
                                  >updates them in the original.
                                  >
                                  >
                                  > UPDATE EBPS_SERVICE
                                  >SET SERVICE_NAME =???,
                                  > SERVICE_DISPLAY_NAME =???,
                                  > SERVICE_CODE =???,
                                  > CREATION_DATE =???
                                  > (SUB_QUERY)
                                  >
                                  >
                                  > There are other solutions but they will require a round trip to DB.
                                  >
                                  >
                                  >
                                  >Noman Muhammad Aftab
                                  >Software Engineer
                                  >Avanza Solutions
                                  >Tel:+92 21 567 5240 – 42 (ext: 762)
                                  >Fax:+92 21 567 5244
                                  >URL: http://www.avanzasolutions.com
                                  >
                                  >Email: noman.aftab@...
                                  >
                                  >
                                  >---------------------------------
                                  > Sent from Yahoo! - a smarter inbox.
                                  >
                                  >[Non-text portions of this message have been removed]
                                  >
                                  >
                                  >
                                  >
                                  >Yahoo! Groups Links
                                  >
                                  >
                                  >
                                • Travis Truax
                                  That s an interesting suggestion, Noman Thanks- ... From: Noman Aftab [mailto:noman17pk@yahoo.com] Sent: Monday, January 21, 2008 11:09 PM To:
                                  Message 16 of 23 , Jan 23, 2008
                                  • 0 Attachment
                                    That's an interesting suggestion, Noman
                                    Thanks-

                                    -----Original Message-----
                                    From: Noman Aftab [mailto:noman17pk@...]
                                    Sent: Monday, January 21, 2008 11:09 PM
                                    To: SQLQueriesNoCode@yahoogroups.com
                                    Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding


                                    Travis,
                                    You can use a miltiplier.Lets say for a precision of 9 digits, multiply each
                                    value by 10^9 (1000000000) to get a whole number, then multiply themself
                                    altogether, and then devide the resultant by the same number.

                                    cast as bigint, if it overflows

                                    Travis Truax <travis.truax@...> wrote:
                                    I did get different results, but floats round as well, and they
                                    apparently
                                    don't round the same everytime. That scares me. This is for a Bill of
                                    Materials Cost Rollup routine, and it's dealing with very small numbers. At
                                    some point you may have to round things off, but I really need it to be as
                                    precise as can be and consistent as well.

                                    I'll try to get access to the sqlmag article tomorrow morning. I'll post
                                    what I end up doing to solve the problem.

                                    Thanks John

                                    Travis-


                                    -----Original Message-----
                                    From: John Warner [mailto:john@...]
                                    Sent: Monday, January 21, 2008 3:14 PM
                                    To: SQLQueriesNoCode@yahoogroups.com
                                    Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
                                    Importance: Low

                                    Travis, is that going to work for you?

                                    John Warner

                                    > -----Original Message-----
                                    > From: SQLQueriesNoCode@yahoogroups.com
                                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                                    > Sent: Monday, January 21, 2008 12:18 PM
                                    > To: SQLQueriesNoCode@yahoogroups.com
                                    > Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
                                    > Importance: Low
                                    >
                                    >
                                    > Please note how I said you would hate this. The problem with
                                    > floats is the way they are 'represented' in memory. As the
                                    > right side of the decimal point grows in scale the amount of
                                    > error increases pretty seriously. You might try the money
                                    > type instead of float for @v1 and @v2. I just played an
                                    > nothing broke... I just really hate all this casting.
                                    >
                                    > John Warner
                                    >
                                    >
                                    >
                                    >
                                    > > -----Original Message-----
                                    > > From: SQLQueriesNoCode@yahoogroups.com
                                    > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
                                    > > Sent: Monday, January 21, 2008 12:08 PM
                                    > > To: 'SQLQueriesNoCode@yahoogroups.com'
                                    > > Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
                                    > >
                                    > >
                                    > > John,
                                    > > Microsofts docs suggest staying away from float for
                                    > > precision financial calcs in favor of decimal & numeric. Like
                                    > > this from the BOL: "Because of the approximate nature of the
                                    > > float and real data types, do not use these data types when
                                    > > exact numeric behavior is required, such as in financial
                                    > > applications, in operations involving rounding, or in
                                    > > equality checks. Instead, use the integer, decimal, money, or
                                    > > smallmoney data types."
                                    > > However it wouldn't be the first time MS docs have been
                                    > > completely wrong. It just leaves me confused. :)
                                    > >
                                    > > Thanks,
                                    > > Travis-
                                    > >
                                    > > -----Original Message-----
                                    > > From: John Warner [mailto:john@...]
                                    > > Sent: Monday, January 21, 2008 10:50 AM
                                    > > To: SQLQueriesNoCode@yahoogroups.com
                                    > > Subject: RE: [SQLQueriesNoCode] SQL Server decimal rounding
                                    > > Importance: Low
                                    > >
                                    > >
                                    > > You are going to hate this possible solution (I know I do),
                                    > > but it seems to work.
                                    > >
                                    > > DECLARE
                                    > > @v1 DECIMAL(19, 9),
                                    > > @v2 DECIMAL(19, 9),
                                    > > @v3 DECIMAL(19, 9)
                                    > > SET
                                    > > @v1 = 7.85
                                    > > SET
                                    > > @v2 = 455
                                    > > SET
                                    > > @v3 = .000001
                                    > >
                                    > > SELECT
                                    > > 7.85 * 455 * .000001, --calculates normally
                                    > > cast(Cast(@v1 as float) * cast(@v2 as float) * cast(@v3 as float) as
                                    > > decimal(19,9)) --rounds
                                    > >
                                    > > Like you I can't get on the website, but saw something about
                                    > > floats that got me thinking.
                                    > >
                                    > > John Warner
                                    > >
                                    > >
                                    > >
                                    > >
                                    > > > -----Original Message-----
                                    > > > From: SQLQueriesNoCode@yahoogroups.com
                                    > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
                                    > Travis Truax
                                    > > > Sent: Monday, January 21, 2008 9:37 AM
                                    > > > To: 'SQLQueriesNoCode@yahoogroups.com'
                                    > > > Subject: [SQLQueriesNoCode] SQL Server decimal rounding
                                    > > >
                                    > > >
                                    > > > I've seen this issue mentioned before a few different places, but
                                    > > > I'm not having any luck finding any details at the moment.
                                    > > >
                                    > > > I'm doing some financial calculations with decimal(28,10)
                                    > variables
                                    > > > and sql server is rounding at the 6th decimal position.
                                    > > >
                                    > > > I found this article :
                                    > > >
                                    > http://www.sqlmag.com/Article/ArticleID/45921/sql_server_45921.html
                                    > > > But their website is screwed up & won't let me subscribe -
                                    > > > and they are out of the office for the holiday.
                                    > > >
                                    > > > The article's example is all I could get:
                                    > > >
                                    > > > DECLARE @v1 DECIMAL(19, 9), @v2 DECIMAL(19, 9), @v3
                                    > DECIMAL(19, 9)
                                    > > > SET @v1 = 7.85 SET @v2 = 455 SET @v3 = .000001
                                    > > >
                                    > > > SELECT
                                    > > > 7.85 * 455 * .000001, --calculates normally
                                    > > > @v1 * @v2 * @v3 --rounds
                                    > > >
                                    > > >
                                    > > > Anyone not off for the holiday that's familiar with this issue?
                                    > > >
                                    > > > Thanks,
                                    > > > Travis-
                                    >
                                    >
                                    >
                                    >
                                    >
                                    > Yahoo! Groups Links
                                    >
                                    >
                                    >
                                    >

                                    Yahoo! Groups Links







                                    Noman Muhammad Aftab
                                    Software Engineer
                                    Avanza Solutions
                                    Tel:+92 21 567 5240 - 42 (ext: 762)
                                    Fax:+92 21 567 5244
                                    URL: http://www.avanzasolutions.com

                                    Email: noman.aftab@...


                                    ---------------------------------
                                    Yahoo! Answers - Get better answers from someone who knows. Tryit now.

                                    [Non-text portions of this message have been removed]




                                    Yahoo! Groups Links
                                  • Noman Aftab
                                    Thanks Anca. Paul Anca wrote: If you are using SQL Server, here is your query: update SERVICE set SERVICE_NAME = B.SERVICE_NAME,
                                    Message 17 of 23 , Jan 25, 2008
                                    • 0 Attachment
                                      Thanks Anca.

                                      Paul Anca <paul@...> wrote:
                                      If you are using SQL Server, here is your query:

                                      update
                                      SERVICE

                                      set
                                      SERVICE_NAME = B.SERVICE_NAME,
                                      SERVICE_DISPLAY_NAME = B.SERVICE_DISPLAY_NAME,
                                      SERVICE_CODE = B.SERVICE_CODE,
                                      CREATION_DATE = B.CREATION_DATE

                                      from
                                      SERVICE A
                                      inner join
                                      SERVICE_STAGING B
                                      on
                                      A.SERVICE_ID = B.SERVICE_ID

                                      where
                                      B.APPROVED = true

                                      Best regards,

                                      Paul Anca
                                      www.ancasolutions.com

                                      >I have a simple web application with list and add/edit pages. The requirement
                                      >is that there are two types of users; maker and checker, and for every table
                                      >there is a staging table (same structure plus an int cloumn acting as a PK)
                                      >where requested changes are kept.
                                      >
                                      > whenever change is requested by the 'maker', the original table is not
                                      >changed, but the changed values are kept in staging table.
                                      >
                                      > When the checker approves that change, the values from the staging are
                                      >pasted (updated) in the original table and are deleted from the staging
                                      >table.
                                      >
                                      > Lets suppose the table structure is as follows:
                                      >
                                      > SERVICE:
                                      > SERVICE_ID (PK)
                                      > SERVICE_NAME
                                      > SERVICE_DISPLAY_NAME
                                      > SERVICE_CODE
                                      > CREATION_DATE
                                      >
                                      > SERVICE_STAGING:
                                      > SERVICE_STAGING_ID (PK)
                                      > SERVICE_ID
                                      > SERVICE_NAME
                                      > SERVICE_DISPLAY_NAME
                                      > SERVICE_CODE
                                      > CREATION_DATE
                                      >
                                      > I want a single update query which picks the values from the staging and
                                      >updates them in the original.
                                      >
                                      >
                                      > UPDATE EBPS_SERVICE
                                      >SET SERVICE_NAME =???,
                                      > SERVICE_DISPLAY_NAME =???,
                                      > SERVICE_CODE =???,
                                      > CREATION_DATE =???
                                      > (SUB_QUERY)
                                      >
                                      >
                                      > There are other solutions but they will require a round trip to DB.
                                      >
                                      >
                                      >
                                      >Noman Muhammad Aftab
                                      >Software Engineer
                                      >Avanza Solutions
                                      >Tel:+92 21 567 5240 – 42 (ext: 762)
                                      >Fax:+92 21 567 5244
                                      >URL: http://www.avanzasolutions.com
                                      >
                                      >Email: noman.aftab@...
                                      >
                                      >
                                      >---------------------------------
                                      > Sent from Yahoo! - a smarter inbox.
                                      >
                                      >[Non-text portions of this message have been removed]
                                      >
                                      >
                                      >
                                      >
                                      >Yahoo! Groups Links
                                      >
                                      >
                                      >






                                      Noman Muhammad Aftab
                                      Software Engineer
                                      Avanza Solutions
                                      Tel:+92 21 567 5240 – 42 (ext: 762)
                                      Fax:+92 21 567 5244
                                      URL: http://www.avanzasolutions.com

                                      Email: noman.aftab@...


                                      ---------------------------------
                                      Sent from Yahoo! - a smarter inbox.

                                      [Non-text portions of this message have been removed]
                                    • Arnie Rowland
                                      If you are using SQL Server, you can JOIN the two tables in the UPDATE statement. UPDATE EBPS_SERVICE SET SERVICE_NAME = g.Service_Name,
                                      Message 18 of 23 , Feb 1 2:38 PM
                                      • 0 Attachment
                                        If you are using SQL Server, you can JOIN the two tables in the UPDATE statement.

                                        UPDATE EBPS_SERVICE
                                        SET
                                        SERVICE_NAME = g.Service_Name,
                                        SERVICE_DISPLAY_NAME = g.Service_Display_Name,
                                        SERVICE_CODE = g.Service_Code,
                                        CREATION_DATE = g.Creation_Date
                                        FROM EBPS_Service s
                                        JOIN EBPS_Service_Staging g
                                        ON s. Service_ID = g.Service_Staging_ID

                                        Regards,
                                        - Arnie Rowland, MVP (SQL Server)
                                        "I am a great believer in luck, and I find that the harder I work, the more I have of it." - Thomas Jefferson (1743-1826)


                                        -----Original Message-----
                                        From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
                                        Sent: Wednesday, January 23, 2008 4:25 AM
                                        To: Arnie
                                        Subject: [SQLQueriesNoCode] Single Update Query....Possible?

                                        I have a simple web application with list and add/edit pages. The requirement is that there are two types of users; maker and checker, and for every table there is a staging table (same structure plus an int cloumn acting as a PK) where requested changes are kept.

                                        whenever change is requested by the 'maker', the original table is not changed, but the changed values are kept in staging table.

                                        When the checker approves that change, the values from the staging are pasted (updated) in the original table and are deleted from the staging table.

                                        Lets suppose the table structure is as follows:

                                        SERVICE:
                                        SERVICE_ID (PK)
                                        SERVICE_NAME
                                        SERVICE_DISPLAY_NAME
                                        SERVICE_CODE
                                        CREATION_DATE

                                        SERVICE_STAGING:
                                        SERVICE_STAGING_ID (PK)
                                        SERVICE_ID
                                        SERVICE_NAME
                                        SERVICE_DISPLAY_NAME
                                        SERVICE_CODE
                                        CREATION_DATE

                                        I want a single update query which picks the values from the staging and updates them in the original.


                                        UPDATE EBPS_SERVICE
                                        SET SERVICE_NAME =???,
                                        SERVICE_DISPLAY_NAME =???,
                                        SERVICE_CODE =???,
                                        CREATION_DATE =???
                                        (SUB_QUERY)


                                        There are other solutions but they will require a round trip to DB.



                                        Noman Muhammad Aftab
                                        Software Engineer
                                        Avanza Solutions
                                        Tel:+92 21 567 5240 - 42 (ext: 762)
                                        Fax:+92 21 567 5244
                                        URL: http://www.avanzasolutions.com

                                        Email: noman.aftab@...


                                        ---------------------------------
                                        Sent from Yahoo! - a smarter inbox.

                                        [Non-text portions of this message have been removed]




                                        Yahoo! Groups Links







                                        Disclaimer - February 1, 2008
                                        This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                                        This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                                      • John Warner
                                        Arnie, where you been hiding. Welcome back Charles sorry for the OT John Warner ... more I have ... requirement ... table ... PK) ... table. ... and ...
                                        Message 19 of 23 , Feb 1 3:20 PM
                                        • 0 Attachment
                                          Arnie, where you been hiding. Welcome back

                                          Charles sorry for the OT

                                          John Warner


                                          > -----Original Message-----
                                          > From: SQLQueriesNoCode@yahoogroups.com
                                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                                          > Sent: Friday, February 01, 2008 5:38 PM
                                          > To: SQLQueriesNoCode@yahoogroups.com
                                          > Subject: RE: [SQLQueriesNoCode] Single Update Query....Possible?
                                          >
                                          > If you are using SQL Server, you can JOIN the two tables in the UPDATE
                                          > statement.
                                          >
                                          > UPDATE EBPS_SERVICE
                                          > SET
                                          > SERVICE_NAME = g.Service_Name,
                                          > SERVICE_DISPLAY_NAME = g.Service_Display_Name,
                                          > SERVICE_CODE = g.Service_Code,
                                          > CREATION_DATE = g.Creation_Date
                                          > FROM EBPS_Service s
                                          > JOIN EBPS_Service_Staging g
                                          > ON s. Service_ID = g.Service_Staging_ID
                                          >
                                          > Regards,
                                          > - Arnie Rowland, MVP (SQL Server)
                                          > "I am a great believer in luck, and I find that the harder I work, the
                                          more I have
                                          > of it." - Thomas Jefferson (1743-1826)
                                          >
                                          >
                                          > -----Original Message-----
                                          > From: SQLQueriesNoCode@yahoogroups.com
                                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
                                          > Sent: Wednesday, January 23, 2008 4:25 AM
                                          > To: Arnie
                                          > Subject: [SQLQueriesNoCode] Single Update Query....Possible?
                                          >
                                          > I have a simple web application with list and add/edit pages. The
                                          requirement
                                          > is that there are two types of users; maker and checker, and for every
                                          table
                                          > there is a staging table (same structure plus an int cloumn acting as a
                                          PK)
                                          > where requested changes are kept.
                                          >
                                          > whenever change is requested by the 'maker', the original table is not
                                          > changed, but the changed values are kept in staging table.
                                          >
                                          > When the checker approves that change, the values from the staging are
                                          > pasted (updated) in the original table and are deleted from the staging
                                          table.
                                          >
                                          > Lets suppose the table structure is as follows:
                                          >
                                          > SERVICE:
                                          > SERVICE_ID (PK)
                                          > SERVICE_NAME
                                          > SERVICE_DISPLAY_NAME
                                          > SERVICE_CODE
                                          > CREATION_DATE
                                          >
                                          > SERVICE_STAGING:
                                          > SERVICE_STAGING_ID (PK)
                                          > SERVICE_ID
                                          > SERVICE_NAME
                                          > SERVICE_DISPLAY_NAME
                                          > SERVICE_CODE
                                          > CREATION_DATE
                                          >
                                          > I want a single update query which picks the values from the staging
                                          and
                                          > updates them in the original.
                                          >
                                          >
                                          > UPDATE EBPS_SERVICE
                                          > SET SERVICE_NAME =???,
                                          > SERVICE_DISPLAY_NAME =???,
                                          > SERVICE_CODE =???,
                                          > CREATION_DATE =???
                                          > (SUB_QUERY)
                                          >
                                          >
                                          > There are other solutions but they will require a round trip to DB.
                                          >
                                          >
                                          >
                                          > Noman Muhammad Aftab
                                          > Software Engineer
                                          > Avanza Solutions
                                          > Tel:+92 21 567 5240 - 42 (ext: 762)
                                          > Fax:+92 21 567 5244
                                          > URL: http://www.avanzasolutions.com
                                          >
                                          > Email: noman.aftab@...
                                          >
                                          >
                                          > ---------------------------------
                                          > Sent from Yahoo! - a smarter inbox.
                                          >
                                          > [Non-text portions of this message have been removed]
                                          >
                                          >
                                          >
                                          >
                                          > Yahoo! Groups Links
                                          >
                                          >
                                          >
                                          >
                                          >
                                          >
                                          >
                                          > Disclaimer - February 1, 2008
                                          > This email and any files transmitted with it are confidential and
                                          intended solely
                                          > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                                          > addressee you should not disseminate, distribute, copy or alter this
                                          email. Any
                                          > views or opinions presented in this email are solely those of the author
                                          and
                                          > might not represent those of Westwood Consulting, Inc. Warning: Although
                                          > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                                          > viruses are present in this email, the company cannot accept
                                          responsibility for
                                          > any loss or damage arising from the use of this email or attachments.
                                          > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                                          >
                                          >
                                          >
                                          > Yahoo! Groups Links
                                          >
                                          >
                                          >
                                        Your message has been successfully submitted and would be delivered to recipients shortly.