|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL view giving wrong resultamounts in a table with a calculated amount to find differences. When I run it, the column named MedDiff is giving 9.99999E-03 as a result. Do I need to use CONVERT or something so that I can compare the result to get all non zero in column FICADiff and MedDiff? The FICADiff correctly returned zero. Thanks. SELECT CheckID, EmployeeNumber, EarnGross, ROUND(EarnGross * .062, 2) AS CalcFICA, TaxFICA, ROUND(EarnGross * .0145, 2) AS CalcMed, TaxMedicare, ROUND(EarnGross * .062, 2) - TaxFICA AS FICADiff, TaxMedicare - ROUND(EarnGross * .0145, 2) AS MedDiff FROM dbo.PayInfoNHS WHERE (CheckDate = CONVERT(DATETIME, '2005-08-05 00:00:00', 102)) AND (TaxMedicare <> ROUND(EarnGross * .0145, 2)) OR (CheckDate = CONVERT(DATETIME, '2005-08-05 00:00:00', 102)) AND (TaxFICA <> ROUND(EarnGross * .062, 2)) David >> When I run it, the column named MedDiff is giving 9.99999E-03 as a It is hard to verify your narrative, when others in this newsgroup does not >> result. Do I need to use CONVERT or something so that I can compare the >> result to get all non zero in column FICADiff and MedDiff? The FICADiff >> correctly returned zero. have any idea how the table PayInfoNHS looks like. Pl. refer to www.aspfaq.com/5006 and post the table structures & a few sample data which illustrates the inconsistencies. -- Anith On Thu, 18 Aug 2005 13:53:41 -0500, David Chase wrote:
>Below is the code for my SQL View. I am trying to compare the actual Hi David,>amounts in a table with a calculated amount to find differences. When I run >it, the column named MedDiff is giving 9.99999E-03 as a result. Do I need >to use CONVERT or something so that I can compare the result to get all non >zero in column FICADiff and MedDiff? The FICADiff correctly returned zero. >Thanks. I can't give any specific comment (see Anith's post), but I do have a general question: are your columns defiend with datatype float or real? If so, then be aware that this datatype is called "approximate numeric": rounding errors are to be expected when working with float/real. Only use this for scientific purposes. For monetary calculations (as your query looks like), choose decimal or numeric datatype instead. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Yes, they are real data types. The database was uploaded from Access and
that is the data type it gave the SQL table. I only care about 2 digit precision on these real data type columns. Will I lose any data if I change them to decimal? David Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:9vu9g19pmfah6t6ja5beidigi5fgnfj5c2@4ax.com... > On Thu, 18 Aug 2005 13:53:41 -0500, David Chase wrote: > >>Below is the code for my SQL View. I am trying to compare the actual >>amounts in a table with a calculated amount to find differences. When I >>run >>it, the column named MedDiff is giving 9.99999E-03 as a result. Do I need >>to use CONVERT or something so that I can compare the result to get all >>non >>zero in column FICADiff and MedDiff? The FICADiff correctly returned >>zero. >>Thanks. > > Hi David, > > I can't give any specific comment (see Anith's post), but I do have a > general question: are your columns defiend with datatype float or real? > If so, then be aware that this datatype is called "approximate numeric": > rounding errors are to be expected when working with float/real. Only > use this for scientific purposes. > > For monetary calculations (as your query looks like), choose decimal or > numeric datatype instead. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) Interstingly, I changed the datatypes from real to smallmoney and the
view now gives the correct result. David *** Sent via Developersdex http://www.developersdex.com *** On Fri, 19 Aug 2005 05:21:41 -0700, David wrote:
>Interstingly, I changed the datatypes from real to smallmoney and the Hi David,>view now gives the correct result. Be aware that money and smallmoney have some issues. First, they are proprietary datatypes; why use them if the ANSI-standard datatypes do just as well? Second, they can cause sneaky errors in calculations: http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/8c527f0c0d95bd45?hl=en& Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  
Other interesting topics
|
|||||||||||||||||||||||
