Float Data Type for Money

Hi,
I'm now supporting a production database that uses the float data type to
store monetary values in one of the tables. No this is not my design but I
am required to support it and to generate reports for the data. I
understand that the float/real data types round incorrectly. How can I
round the data correctly? Here is an sample float value:
1139.3099999999999
I need 1139.31.
Thanks
JerryI also need to have any trailing zeros removed as well.
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OCxn4jjvFHA.908@.tk2msftngp13.phx.gbl...
> Hi,
> I'm now supporting a production database that uses the float data type to
> store monetary values in one of the tables. No this is not my design but
> I am required to support it and to generate reports for the data. I
> understand that the float/real data types round incorrectly. How can I
> round the data correctly? Here is an sample float value:
> 1139.3099999999999
> I need 1139.31.
> Thanks
> Jerry
>|||This seemed to work:
convert(decimal(10,2),round(convert(mone
y,column),2)) --column is the
float column
Is this ok or...?
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:emIDnmjvFHA.3400@.TK2MSFTNGP14.phx.gbl...
>I also need to have any trailing zeros removed as well.
> Thanks
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OCxn4jjvFHA.908@.tk2msftngp13.phx.gbl...
>|||You cannot support it. It will not work, thanks to floating point
rounding errors. It is also illegal in the EU and in violation of GAAP
in the United States. Under SOX, there is a good chance that your boss
is going to jail for this kind of accounting. I would update my resume
and send the boss a letter so that you do not get caught up in the mess
that is coming.
Oh, the stinking, dirty, unusable kludge is CAST() and/or ROUND(). Do
not convert to MONEY -- it is proprietary and has funny math.|||--CELKO-- wrote:
> You cannot support it. It will not work, thanks to floating point
> rounding errors. It is also illegal in the EU and in violation of GAAP
> in the United States. Under SOX, there is a good chance that your boss
> is going to jail for this kind of accounting. I would update my resume
> and send the boss a letter so that you do not get caught up in the mess
> that is coming.
> Oh, the stinking, dirty, unusable kludge is CAST() and/or ROUND(). Do
> not convert to MONEY -- it is proprietary and has funny math.
Hi Joe,
Please, please, please, can you post a reference to the illegality of
this in the EU? I can find *nothing* online (other than Euro
*conversion* rules), and We're about to have a new system introduced
here that uses floating point all over the place for currency, so if
you could provide a reference, I might be able to force a change to the
system.
Damien|||Thanks Joe.
Scary thing is it's an accounting-based software package. Yeah I noticed
this and about 5 other "Why did you...?" yesterday afternoon when I was
meeting with the vendor. I'll follow up with the vendor, management and
accounting today. Do you have any links that support the violation that I
can forward on?
For the reports I'll be generating in RS for account aging, what would the
kludge code look like to round to 2 decimal places and trucate trailing
zeros? Here is the code I came up with:
convert(decimal(10,2),round(convert(mone
y,column),2))
Thanks
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127274857.269620.320950@.g43g2000cwa.googlegroups.com...
> You cannot support it. It will not work, thanks to floating point
> rounding errors. It is also illegal in the EU and in violation of GAAP
> in the United States. Under SOX, there is a good chance that your boss
> is going to jail for this kind of accounting. I would update my resume
> and send the boss a letter so that you do not get caught up in the mess
> that is coming.
> Oh, the stinking, dirty, unusable kludge is CAST() and/or ROUND(). Do
> not convert to MONEY -- it is proprietary and has funny math.
>|||On Wed, 21 Sep 2005 08:46:44 -0700, Jerry Spivey wrote:
(snip)
>For the reports I'll be generating in RS for account aging, what would the
>kludge code look like to round to 2 decimal places and trucate trailing
>zeros? Here is the code I came up with:
> convert(decimal(10,2),round(convert(mone
y,column),2))
Hi Jerry,
No need to make it that complicated.
SELECT CONVERT(decimal(10,2), BadlyTypedColumn)
will do.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Overcomplicating things again...damn! ;-)
Thanks Hugo
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:p8i3j1p1g9bcicg3doqjdpca4qocv534dp@.
4ax.com...
> On Wed, 21 Sep 2005 08:46:44 -0700, Jerry Spivey wrote:
> (snip)
> Hi Jerry,
> No need to make it that complicated.
> SELECT CONVERT(decimal(10,2), BadlyTypedColumn)
> will do.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is a quick "cut & paste":
The MONEY datatype has rounding errors. Using more than one operation
(multiplication or division) on money columns will produce severe
rounding errors. A simple way to visualize money arithmetic is to place
a ROUND() function calls after every operation. For example,
Amount = (Portion / total_amt) * gross_amt
can be rewritten using money arithmetic as:
Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)
Rounding to four decimal places might not seem an issue, until the
numbers you are using are greater than 10,000.
BEGIN
DECLARE @.gross_amt MONEY,
@.total_amt MONEY,
@.my_part MONEY,
@.money_result MONEY,
@.float_result FLOAT,
@.all_floats FLOAT;
SET @.gross_amt = 55294.72;
SET @.total_amt = 7328.75;
SET @.my_part = 1793.33;
SET @.money_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.float_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.Retult3 = (CAST(@.my_part AS FLOAT)
/ CAST( @.total_amt AS FLOAT))
* CAST(FLOAT, @.gross_amtAS FLOAT);
SELECT @.money_result, @.float_result, @.all_floats;
END;
@.money_result = 13525.09 -- incorrect
@.float_result = 13525.0885 -- incorrect
@.all_floats = 13530.5038673171 -- correct, with a -5.42 error|||Thanks Joe!
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127417068.157889.200980@.g14g2000cwa.googlegroups.com...
> Here is a quick "cut & paste":
> The MONEY datatype has rounding errors. Using more than one operation
> (multiplication or division) on money columns will produce severe
> rounding errors. A simple way to visualize money arithmetic is to place
> a ROUND() function calls after every operation. For example,
> Amount = (Portion / total_amt) * gross_amt
> can be rewritten using money arithmetic as:
> Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)
> Rounding to four decimal places might not seem an issue, until the
> numbers you are using are greater than 10,000.
> BEGIN
> DECLARE @.gross_amt MONEY,
> @.total_amt MONEY,
> @.my_part MONEY,
> @.money_result MONEY,
> @.float_result FLOAT,
> @.all_floats FLOAT;
> SET @.gross_amt = 55294.72;
> SET @.total_amt = 7328.75;
> SET @.my_part = 1793.33;
> SET @.money_result = (@.my_part / @.total_amt) * @.gross_amt;
> SET @.float_result = (@.my_part / @.total_amt) * @.gross_amt;
> SET @.Retult3 = (CAST(@.my_part AS FLOAT)
> / CAST( @.total_amt AS FLOAT))
> * CAST(FLOAT, @.gross_amtAS FLOAT);
> SELECT @.money_result, @.float_result, @.all_floats;
> END;
> @.money_result = 13525.09 -- incorrect
> @.float_result = 13525.0885 -- incorrect
> @.all_floats = 13530.5038673171 -- correct, with a -5.42 error
>

flatfilesource(s) in a loop

I am trying to design a package to import the data of several .tx files into a table in sql server.

1) I created an execute task that truncates the sql server table i.e. truncate table tblContacts

2)
Placed a forrloop container with enumerator: foreach file enumerator
Folder points to the folder that holds the txt files
file: *.*
filename: fully qualified
variablemapping: User::FileName with Index 0

3)
placed a data flow task inside the forloop
this dataflow task has the following dataflow:
FlatFile Source: connection manager is pointing to one of the txt files
OLE DB Destination to place the txt data into tblContact in the database.

The question:
when the package is run, the tblContact gets populated only from the first txt file, i.e. the one which I placed in the flatfilesource connection manager.
How can I allow several files in the flatfilesource, instead of the one I have now...

Thanks

Firstly, in your For Each Loop, you could change filter from *.* to *.tx (not essential)

You need to make the connectionstring variable (ConnectionString is a property of your FlatFile connection Manager )

Click on your FlatFile Connection Manager (this is at the bottom of the BIDS screen).

Go to the properties of the CM, I mean the properties window that appears on the right of your screen.

Expand the Expressions property collection, and drill through to get the Property Expressions editor.

Choose property ConnectionString, and drill through the expression to get the Expression Builder.

Drag your variable (FileName) and drop it into the Expression box. I assume the FileName variable contains the entire file path and name.

Now it should work fine.

Flat file - row delimiter problem

Hi,

I'm trying to design this package where i take data from a source and need to transform it into a flatfile with some extra static information.

I use a SQL script like this (ex.):

SELECT '

BS0220131264202400000130001'+cast(wa.perf_applicant_number as nvarchar)+'000000000' + wa.perf_firstname + ' ' + wa.perf_lastname + CHAR(13)+

'BS0220131264202400000330001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000' + WAPD2.strvalue+ '

BS0520131264202410001130001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000 tekst der skal st? p? kortet' as nvarchar

FROM dbo.WAIT_Applicant WA (nolock)

This makes the text (from one record) split up over several lines in the output.

I succeded with this in a SQL2000 DTS package and the flat txt-file looked liked I wan't it to. But now i tried doing it in 2005. And now it is not workin' anymore

In my Flat File Connection Manager Editor i chose {LF} as the row delimiter and the preview looks really nice. Like this:

BS0220131264202400000130001000000015826727000000000S?ren Hesth

BS0220131264202400000330001000000015826727000000000adfasdf

BS0520131264202410001130001000000015827207000000000 tekst der skal st? p? kortet

But in the file that is created it doesn't split up over several lines. Instead of a carriage return it puts a [black box] - a sign which counts as the carriage return.

I don't know if I have explained this well enough, but I hope that someone can help me. I've been trying for 3 days now.

I'm guessing you want the Flat File to output records on individual lines (if you opened the file in notepad). If that is the case use {cr}{lf} as your row delimiter, since in a Windows environment that is the standard newline character combination. As single {lf} is often employed in Unix/Mainframe environments as a newline character, which is why it is an option.
Larry Pope
|||

That was also what i started with, but then i read in another discussion inhere, where they suggested to use {LF}, so i changed it.

What i want is, that one record is printed over several lines in the text-file. After that record, then the next record is printed, also over several lines in the text-file.

I have tried to change it back and tried almost every possible combination of

- rowdelimiter ( CRLF, CR, LF...)

- format (ragged right, delimied...)

and so on.

And nothing works.

Is there any other way of doing this. Maybe there is something I can do in the script.

|||If what you want is something like the following (Assume #is a comment line and doesn't exist in the file).
#Record1
Column1Column2
Column3Column4
Column5Column6
#Record2
Column1Column2
Column3Column4
Column5Column6
...
If that is what you want, then you will need to build both a custom component either through a script transform or a full-fledged component.
The code would be something similar to the following
Dim sw As New System.IO.StreamWriter("c:\temp\test.txt", True)
sw.WriteLine(Row.Column1.ToString & Row.Column2.ToString)
sw.WriteLine(Row.Column3.ToString & Row.Column4.ToString)
sw.WriteLine(Row.Column5.ToString & Row.Column6.ToString)
sw.Close()
This will append to an existing file, so if you may need to create a task that deletes the existing file prior to the data flow task. You'll also should check for errors (null values, stream writer was created, etc).
Larry Pope
|||

But i succeded with doing this with my the package i wrote in SQL server 2000.

There must be a way that I can make a carriage return, so that the notepad will read it correctly.

Flags on a table

Hi all

Been thrown a bit in the deep end and need some advice..... new to db design.

I have been emailed the lay out of the table which is not a problem, they then go on to talk about flags on columns if data is inserted , updated... ect.

How do i set them up ?

The data is going to be imported in via DTS from a excel spread sheet

Thanks in advance

Rich

No clue. Can you post the exact request (without any kind of private information of course :) If they wanted the date and time of updates and inserts, that would be easy enough, but a flag (generally thought of as a imitation boolean (often using a bit) that is set to TRUE/ON when some condition is met (like disabledFlag might indicate that an account was disabled.)

This wouldn't make sense to have an updatedFlag, since it could be updated many times.

|||

Could it be that they are talking about triggers.

In a trigger you can do things like:

If UPDATE(column name)

BEGIN

END

|||

Thanks for the responses, I now have more details and I don't explain the problem properly.

What is happening is the I will have to import data via DTS each night which will be located as a file on a FTP server, so for example the customer table has a change i.e update or insert, the documentation talks about flags so that it can do the necessary change or ignore the row and move to the next one.

Does this make more sense?

Rich

|||

Do you mean that the flags should function as some kind of indicators whether a certain row should be updated or not..?

(still a bit confused about what those flags are supposed to do..)

/Kenneth

|||

Richie_C wrote:

What is happening is the I will have to import data via DTS each night which will be located as a file on a FTP server, so for example the customer table has a change i.e update or insert, the documentation talks about flags so that it can do the necessary change or ignore the row and move to the next one.

Does this make more sense?

Getting there. What I am starting to think is that they want you to put flags on rows so you can see if the row has been changed since some last export perhaps? This can be done with triggers:

--untested
create trigger tableName$update
on tableName
after update
as

begin

update tableName
set updateFlag = 1
from tableName
join inserted
on tableName.key = inserted.key

end

|||

gents

thanks for all you responses, the flags are going to be programmed in xml for checking.

sorry for wasting everybodys time!!

|||

No time wasted here. I am just interested in how you are using XML? How did this come about? Can you explain?

FK "this or that" case best practice

If I have a table that can either have a FK to one table or another, what is
the best way to design it?
eg.
create table template
(
id int not null primary key,
description varchar(50) not null,
roleId int null references role(roleId),
userId int null references user(userId)
)
At the moment, because either a role or a user can own the template, I
haveset both columns to allow null, but I do not like this approach. Would
intermediate tables be better even though it is a 1:1 relationship?
create table template
(
id int not null primary key,
description varchar(50) not null,
)
create table templateuser
(
templateId int null references template(id) primary key,
userId int null references user(userId)
)
create table templaterole
(
templateId int null references template(id) primary key,
roleId int null references role(roleId)
)
Thanks
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--David,
One solution could be enforcing the RI using triggers.
AMB
"David J Rose" wrote:

> If I have a table that can either have a FK to one table or another, what
is
> the best way to design it?
> eg.
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> roleId int null references role(roleId),
> userId int null references user(userId)
> )
> At the moment, because either a role or a user can own the template, I
> haveset both columns to allow null, but I do not like this approach. Would
> intermediate tables be better even though it is a 1:1 relationship?
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> )
> create table templateuser
> (
> templateId int null references template(id) primary key,
> userId int null references user(userId)
> )
> create table templaterole
> (
> templateId int null references template(id) primary key,
> roleId int null references role(roleId)
> )
> Thanks
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News=
=--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||I think it is easier to go with one table, because then you can enforce with
a check constraint that a template is related to exactly a user or a role
(and not both or none), something which you can't do as easy if you use the
2 extra tables. The check constraint would be:
CONSTRAINT CK_template__either_role_or_user
CHECK((roleId IS NULL AND userID IS NOT NULL) OR (roleId IS NOT NULL AND
userID IS NULL))
Jacco Schalkwijk
SQL Server MVP
"David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
news:425d0d17$1_1@.127.0.0.1...
> If I have a table that can either have a FK to one table or another, what
> is the best way to design it?
> eg.
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> roleId int null references role(roleId),
> userId int null references user(userId)
> )
> At the moment, because either a role or a user can own the template, I
> haveset both columns to allow null, but I do not like this approach. Would
> intermediate tables be better even though it is a 1:1 relationship?
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> )
> create table templateuser
> (
> templateId int null references template(id) primary key,
> userId int null references user(userId)
> )
> create table templaterole
> (
> templateId int null references template(id) primary key,
> roleId int null references role(roleId)
> )
> Thanks
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||What is wrong with having two tables, one for users, one for roles. You
don't HAVE to have a row in both user and role, so this design makes sense.
I would put a FK to both tables, and probably set them to DELETE CASCADE.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
news:425d0d17$1_1@.127.0.0.1...
> If I have a table that can either have a FK to one table or another, what
> is the best way to design it?
> eg.
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> roleId int null references role(roleId),
> userId int null references user(userId)
> )
> At the moment, because either a role or a user can own the template, I
> haveset both columns to allow null, but I do not like this approach. Would
> intermediate tables be better even though it is a 1:1 relationship?
> create table template
> (
> id int not null primary key,
> description varchar(50) not null,
> )
> create table templateuser
> (
> templateId int null references template(id) primary key,
> userId int null references user(userId)
> )
> create table templaterole
> (
> templateId int null references template(id) primary key,
> roleId int null references role(roleId)
> )
> Thanks
>
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--

Fixing a messy database after the fact...?

2 questions, actually:

I am new to database design and a lot of things never made any sense to me regarding relationships and such. I have been working on a very large design that started out well enough, but as tables were added a lot of organization fell by the wayside. Now that I am getting closer to the end, I am finding a lot of places where there should be Foreign keys, maybe some triggers, etc (I have the same data item in 5 different places, when it is deleted in one place it must go from all). Assuming that the datatypes and sizes are identical for the duplicated bits of data, can I go about making FK-PK relationships and such now that there is a lot of stuff in the database, or do I have to start from scratch and rebuild the whole thing.

The other question is much more simple:

How do I make multiple rows "unique". I have a primary key, and an identity column, but I can't add a secong primary key, and Enterprise Manager only lets me make 'int' datatypes identity columns. I have tried the "add constraints" but it asks for an expression and I have no idea what the syntax might be.

Any help is appreciated.Try downloading AdventureWorks for SQL Server 2000 from the first link, copy the installation file into Query Analyzer and execute it. It is an 87 table Database using the Peter Chen ERD model. The second is PPT slides with the book used to create it, only 143 pages but it has a lot of sample Catalogs that will make things a little easier for you. The book is dry and abstract. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?familyid=487c9c23-2356-436e-94a8-2bfb66f0abdc&languageid=f49e8428-7071-4979-8a67-3cffcb0c2524&displaylang=en

http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html

Kind regards,
Gift Peddie

firehosemode

HI
I am trying to design merge replication.
step1:configure sqlservr1 as publisher and distributor and published pubs
database.
step2:from sqlserver2 i tried to pull the data using pull
subscription.everything works fine.
step3:again i decided to disabled publishing then start disabling step by
step finally i disabled sqlserver1 as publisher and distributor.
step4:i went to pubs database and start entering data in column mode some
tables allowing data but some tables raising errors saying that errors#
(transaction cannot start in firhosemode).
errors#(data entered is inconsistent value check datatype)
Can anyone help should be appreciated.
Message posted via http://www.droptable.com
Are you updating the tables using Enterprise Manager? Check
http://support.microsoft.com/default...;en-us;237398.
Adrian
"pardhi via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:b7beba92b262474fb634526f72c156c4@.droptable.co m...
> HI
> I am trying to design merge replication.
> step1:configure sqlservr1 as publisher and distributor and published pubs
> database.
> step2:from sqlserver2 i tried to pull the data using pull
> subscription.everything works fine.
> step3:again i decided to disabled publishing then start disabling step by
> step finally i disabled sqlserver1 as publisher and distributor.
> step4:i went to pubs database and start entering data in column mode some
> tables allowing data but some tables raising errors saying that errors#
> (transaction cannot start in firhosemode).
> errors#(data entered is inconsistent value check datatype)
> Can anyone help should be appreciated.
> --
> Message posted via http://www.droptable.com