Floating point fun

I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
migration. I've read certain floating-point values can't be represented
accurately, however, .1 doesn't look like one of them as the FLOAT(2) field
copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1 round
to .10000000000000001? Converting the column to DECIMAL is probably not an
option.
Thanks
Damien
CREATE TABLE #float_test ( ft_id INT PRIMARY KEY, rate1 FLOAT, rate2
FLOAT(2) )
-- Try and insert the value 4.1 into the float table
INSERT INTO #float_test ( ft_id, rate1 )
SELECT 1, 4.1 UNION
SELECT 2, 4 + .1 UNION
SELECT 3, '4.10' UNION
SELECT 4, 4.11 UNION
SELECT 5, 4.95 UNION
SELECT 6, CONVERT( REAL, 4.1, 0 ) UNION
SELECT 7, .1
GO
UPDATE #float_test
SET rate2 = rate1
SELECT * FROM #float_test
DROP TABLE #float_testHi
Read http://www.aspfaq.com/show.asp?id=2477
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:C4AF11D4-0AD6-4EE3-8453-FFF203829E25@.microsoft.com...
> I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
> migration. I've read certain floating-point values can't be represented
> accurately, however, .1 doesn't look like one of them as the FLOAT(2)
> field
> copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1
> round
> to .10000000000000001? Converting the column to DECIMAL is probably not
> an
> option.
> Thanks
> Damien
> CREATE TABLE #float_test ( ft_id INT PRIMARY KEY, rate1 FLOAT, rate2
> FLOAT(2) )
> -- Try and insert the value 4.1 into the float table
> INSERT INTO #float_test ( ft_id, rate1 )
> SELECT 1, 4.1 UNION
> SELECT 2, 4 + .1 UNION
> SELECT 3, '4.10' UNION
> SELECT 4, 4.11 UNION
> SELECT 5, 4.95 UNION
> SELECT 6, CONVERT( REAL, 4.1, 0 ) UNION
> SELECT 7, .1
> GO
> UPDATE #float_test
> SET rate2 = rate1
> SELECT * FROM #float_test
> DROP TABLE #float_test
>|||On Tue, 23 Aug 2005 01:43:07 -0700, Damien
<Damien@.discussions.microsoft.com> wrote:

>I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
>migration. I've read certain floating-point values can't be represented
>accurately, however, .1 doesn't look like one of them as the FLOAT(2) field
>copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1 round
>to .10000000000000001? Converting the column to DECIMAL is probably not an
>option.
It's not clear to me what you expect to happen. Floating-point values
*are* precisely represented by floating-point values. (cough)
OTOH, certain fractional values are not in the domain of certain
floating-point data types. In those cases, software generally picks
the closest value available. (The result is "error of approximation",
not a rounding error.)
You seem to think that every number that ends in '.1' should have the
same behavior. That's simply not true of floating-point data types.
It might help to think of it this way. Neighboring, distinct values in
exact data types are the same distance apart on a number line. That
is, each value in a SQL INTEGER data type is plus or minus 1 from its
neighbor.
But neighboring, distinct values in a floating-point data type are not
the same distance apart on a number line. The closer you get to zero
(from either direction), the more closely spaced the neighboring
distinct values are.
Mike Sherrill|||> Also, why does 4.1 round to .0999999999999996 but 0.1 round
to .10000000000000001?
because under the hood floats are stored as binaries. So, binary
numbers are represented accurately, up to some accuracy, of course.
Decimals are rounded to binaries. When you convert binaries back to
decimals, expect some mismatch|||On 24 Aug 2005 14:36:10 -0700, ford_desperado@.yahoo.com wrote:
[snip]
>Decimals are rounded to binaries.
Error of approximation, which seems to be what you're stumbling
toward, doesn't mean "fixed point numbers are rounded to binary". See
Knuth, vol 2.

Float to Datetime Conversion

I need to convert values in a float data type field to that of datetime. The float data type field currently contains values such as 20060927,20060928, etc. Any suggestions?
Thanks in advance,
sajmeraconvert(datetime,cast(cast(foo as integer) as char),112)|||Thanks for the quick reply. I also tried the following and got the result.

convert(datetime,convert(varchar(20),convert(int,c onvert(float,<field name>))))

Thanks again!|||SELECT CAST(CONVERT(VARCHAR, Col1) AS DATETIME) AS NewValue
FROM Table1

Float return

When the table below is created -the data selected
is different for values > 10. Does some one know why
float behaves this way ? - I am stumped
create table tempdb.dbo.TestValue (ColId int, TheValue
Float)
Insert into TestValue Values (1, 10.25)
Insert into TestValue Values (2, 10.99)
Insert into TestValue Values (3, 9.9)
Insert into TestValue Values (4, 6.59)
select * from TestValue
Results:
========
1 10.25
2 10.99
3 9.9000000000000004
4 6.5899999999999999PBrent
Read up "float and real" chapter in the BOL as well as visit on Aaron's web
site www.aspfaq.com to get more info and examples whu this datatype behaves
this way.
"PBrent" <PBrent@.discussions.microsoft.com> wrote in message
news:23d001c53f65$e8aff6a0$a401280a@.phx.gbl...
> When the table below is created -the data selected
> is different for values > 10. Does some one know why
> float behaves this way ? - I am stumped
> create table tempdb.dbo.TestValue (ColId int, TheValue
> Float)
> Insert into TestValue Values (1, 10.25)
> Insert into TestValue Values (2, 10.99)
> Insert into TestValue Values (3, 9.9)
> Insert into TestValue Values (4, 6.59)
> select * from TestValue
> Results:
> ========
> 1 10.25
> 2 10.99
> 3 9.9000000000000004
> 4 6.5899999999999999|||It's nothing special about 10. If you insert the values 16.9 into a float,
the actual floating-point value stored is just under 16.9, and you get this
Insert into TestValue Values (5, 16.9)
...
16.899999999999999
Of the numbers you inserted into the table, only 10.25 can be
stored exactly as a float. The others are stored as the nearest
representable floating-point value. When these approximations
are converted back to decimals for display, sometimes you see
the difference from the original number you tried to enter, and
sometimes you are lucky and they are rounded back to the
number you started with.
Steve Kass
Drew University
PBrent wrote:

>When the table below is created -the data selected
>is different for values > 10. Does some one know why
>float behaves this way ? - I am stumped
>create table tempdb.dbo.TestValue (ColId int, TheValue
>Float)
>Insert into TestValue Values (1, 10.25)
>Insert into TestValue Values (2, 10.99)
>Insert into TestValue Values (3, 9.9)
>Insert into TestValue Values (4, 6.59)
>select * from TestValue
>Results:
>========
>1 10.25
>2 10.99
>3 9.9000000000000004
>4 6.5899999999999999
>

Float Errors

Simple way of testing this
CREATE TABLE TEST (COL1 FLOAT)
INSERT INTO TEST (COL1) VALUES (8746.02)
SELECT * FROM TEST
This is the result.
8746.0200000000004
How do I stop this from happening, I am inserting into someone else's system
so I can not change the data type.
Any help would be appreciated.
Thanks,
DanielSorry
SQL Server 2000 SP3
Dan
"Daniel Jeffrey" <daniel@.enprisesolutions.com> wrote in message
news:eFQtcyZ9DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Simple way of testing this
> CREATE TABLE TEST (COL1 FLOAT)
> INSERT INTO TEST (COL1) VALUES (8746.02)
> SELECT * FROM TEST
> This is the result.
> 8746.0200000000004
> How do I stop this from happening, I am inserting into someone else's
system
> so I can not change the data type.
> Any help would be appreciated.
> Thanks,
> Daniel
>|||The problem is in the datatype. BOL will tell you that float is an approxim
ate datatype. Which means that the exact number stored is not always what w
as inteded for storage. There is no way to get around this unless you handl
e your own rounding (See Ro
und function in BOL). Even then you can get unexpected results.|||... so if you expect to get out what you put in, use an exact datatype, lik
e
a NUMERIC datatype, for instance.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Doug Guerena" <anonymous@.discussions.microsoft.com> wrote in message
news:5D884697-8F47-45A7-88A3-4E0B38DA61DF@.microsoft.com...
> The problem is in the datatype. BOL will tell you that float is an
approximate datatype. Which means that the exact number stored is not
always what was inteded for storage. There is no way to get around this
unless you handle your own rounding (See Round function in BOL). Even then
you can get unexpected results.|||Daniel,
The FLOAT data type can only represent finitely many of the
infinitely-many real numbers. The exact values FLOAT can represent are
those of the form N/power(2,k) where N is an integer with absolute value
between 2^52 and 2^53 and k is an integer between -930 and +1077 (or
something close to this - I didn't verify the exact details). The real
number 8746.02 cannot be written in that form, so the closest
representable float is inserted into the table.
So basically, whoever created the table TEST did not provide a place
to put the exact value 8746.02. If, however, you know that all values
inserted into TEST.COL1 were base-ten decimals with at most 10
significant digits and at most 2 decimal places, the value inserted can
be retrieved with SELECT CAST(COL1 AS DECIMAL(10,2)) FROM TEST, since
there is a unique decimal(10,2) that could have produced each value of
COL1 between -100000000.00 and 100000000.00 in the table.
SK
Daniel Jeffrey wrote:

>Simple way of testing this
>CREATE TABLE TEST (COL1 FLOAT)
>INSERT INTO TEST (COL1) VALUES (8746.02)
>SELECT * FROM TEST
>This is the result.
>8746.0200000000004
>How do I stop this from happening, I am inserting into someone else's syste
m
>so I can not change the data type.
>Any help would be appreciated.
>Thanks,
>Daniel
>
>|||I have found rounding issues with this operation as well

Float Errors

Simple way of testing this
CREATE TABLE TEST (COL1 FLOAT)
INSERT INTO TEST (COL1) VALUES (8746.02)
SELECT * FROM TEST
This is the result.
8746.0200000000004
How do I stop this from happening, I am inserting into someone else's system
so I can not change the data type.
Any help would be appreciated.
Thanks,
DanielSorry
SQL Server 2000 SP3
Dan
"Daniel Jeffrey" <daniel@.enprisesolutions.com> wrote in message
news:eFQtcyZ9DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Simple way of testing this
> CREATE TABLE TEST (COL1 FLOAT)
> INSERT INTO TEST (COL1) VALUES (8746.02)
> SELECT * FROM TEST
> This is the result.
> 8746.0200000000004
> How do I stop this from happening, I am inserting into someone else's
system
> so I can not change the data type.
> Any help would be appreciated.
> Thanks,
> Daniel
>|||The problem is in the datatype. BOL will tell you that float is an approximate datatype. Which means that the exact number stored is not always what was inteded for storage. There is no way to get around this unless you handle your own rounding (See Round function in BOL). Even then you can get unexpected results.|||... so if you expect to get out what you put in, use an exact datatype, like
a NUMERIC datatype, for instance.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Doug Guerena" <anonymous@.discussions.microsoft.com> wrote in message
news:5D884697-8F47-45A7-88A3-4E0B38DA61DF@.microsoft.com...
> The problem is in the datatype. BOL will tell you that float is an
approximate datatype. Which means that the exact number stored is not
always what was inteded for storage. There is no way to get around this
unless you handle your own rounding (See Round function in BOL). Even then
you can get unexpected results.|||Daniel,
The FLOAT data type can only represent finitely many of the
infinitely-many real numbers. The exact values FLOAT can represent are
those of the form N/power(2,k) where N is an integer with absolute value
between 2^52 and 2^53 and k is an integer between -930 and +1077 (or
something close to this - I didn't verify the exact details). The real
number 8746.02 cannot be written in that form, so the closest
representable float is inserted into the table.
So basically, whoever created the table TEST did not provide a place
to put the exact value 8746.02. If, however, you know that all values
inserted into TEST.COL1 were base-ten decimals with at most 10
significant digits and at most 2 decimal places, the value inserted can
be retrieved with SELECT CAST(COL1 AS DECIMAL(10,2)) FROM TEST, since
there is a unique decimal(10,2) that could have produced each value of
COL1 between -100000000.00 and 100000000.00 in the table.
SK
Daniel Jeffrey wrote:
>Simple way of testing this
>CREATE TABLE TEST (COL1 FLOAT)
>INSERT INTO TEST (COL1) VALUES (8746.02)
>SELECT * FROM TEST
>This is the result.
>8746.0200000000004
>How do I stop this from happening, I am inserting into someone else's system
>so I can not change the data type.
>Any help would be appreciated.
>Thanks,
>Daniel
>
>|||I have found rounding issues with this operation as wellsql

Float Datatype Truncation Bug

Hi Friends,

I have a table

Create table #table1(a float)

insert into #table1 values(123456789.987654321)

select a from #table1

drop table #table1

when i run this in SQLServer 2005 Management Studio i get the following truncated output

(1 row(s) affected)

a

-

123456789.987654

(1 row(s) affected)

when i run this query using SQL Query Analyser or OSQL Utility i get the following output

(1 row(s) affected)

a
--
123456789.98765431

(1 row(s) affected)

I want the full output in SQLServer 2005 itself.... Is this a microsoft bug?

I would like to know how to fix this?

Thanks and Regards,

It is not a bug. Your data is not truncated on the table(while storing). its bcs of the Management Console only.(MC result only truncate the values). Connect the same SQL Server 2005 from QA you will get the same result as 2000 (in your case your proper result).

If you want to trust your result use the following query.(explicit precision setting)

Code Snippet

Create table #table1(a float)

Insert into #table1 values(123456789.987654321)

Select cast(a as numeric(38,8)) from #table1

Drop table #table1

Float Datatype

Hi Friends,

I have a table

Create table #table1(a float)

insert into #table1 values(123456789.987654321)

select a from #table1

drop table #table1

when i run this in SQLServer 2005 Management Studio i get the following truncated output

(1 row(s) affected)

a

-

123456789.987654

(1 row(s) affected)

when i run this query using SQL Query Analyser or OSQL Utility i get the following output

(1 row(s) affected)

a
--
123456789.98765431

(1 row(s) affected)

I want the full output in SQLServer 2005 itself.... Is this a microsoft bug?

I would like to know how to fix this?

Thanks and Regards,

It is not a bug. Your data is not truncated on the table(while storing). its bcs of the Management Console only.(MC result only truncate the values). Connect the same SQL Server 2005 from QA you will get the same result as 2000 (in your case your proper result).

If you want to trust your result use the following query.(explicit precision setting)

Code Snippet

Create table #table1(a float)

Insert into #table1 values(123456789.987654321)

Select cast(a as numeric(38,8)) from #table1

Drop table #table1

sql

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
>

Float data not replicated exactly

Why are float values not replicated exactly? For example:
33.333333333333343 -> 33.333333333333336
233.33333333333331 -> 233.33333333333334
I saw a previous post about this, but I'm not satisfied with the answer
"float is an imprecise data type". Note that I don't have this problem with
DTS, let alone backup/restore. And it isn't meerly an issue of display
precision: SQL treats these as different values in <> conditions and the
values return different checksums (which is the real problem for us).
First, as Steve Kass explains to me, when you select a float and see
something like 9.000000000010, you are seeing a decimal approximation of the
exact value stored. Even if you see the same viewable output from two
floats, you can't conclude they are the same value - only viewing the float
as a binary(8) can make sure of that. So, this makes things difficult, eg
If you insert these into a float column:
insert into T values (1234.5678901)
insert into T values (1234.5678901000000000)
And then convert to binary, these are 2 different values!!!
Replicationwise, the problem seems to be that the log reader reads the float
value and puts a call into an insert SP in distribution as usual. If you
convert the publisher's floats to binary and compare to the parameter values
in msrepl_commands (also converted to binary), they are the same 99% of the
time, but the float value seems to be occasionally different. Visually it
has 1 less dp than the original (viewable using sp_browsereplcmds), which
may be the cause.
The situation might be further complicated by different hardware issues on
publisher & subscriber, resulting in different float storages.
HTH,
Paul Ibison
|||Hi Suzanne,
Even given the imprecise nature of floating-point numbers, replication
should not be changing the binary representation of floating-point numbers
as persisted at the publisher if it is configured to use binary
parameters\native mode bcp so the discrepancies that you observed are indeed
quite strange. And since I wasn't entirely sure whether my expectations were
indeed correct, I created a small scenario replicating the numbers that you
provided below both during the inital (native mode) snapshot and as (binary
parameters) incremental changes in transactional replication and the numbers
showed up exactly the same at the subscriber. I did, however, observe a
slight discrepancy of 233.33333333333331 -> 233.33333333333329 when it is
replicated as an string-literal incremental change so I am guessing that
perhaps your publication is not setup to use binary parameters (for non-SQL
Server subscriber support?). I also notice that the numbers provided below
have 17 digits whereas SQL books online states that float has a precision of
15 so the last two digits are technically up in the air. Nevertheless, I am
not sure how such discrepancies can arise without resorting to the more
exotic explanations of differing CPU architectures\OS|CRT floating handling
between the publisher and the subscriber. It would be great if you can tell
us more about your environment so we can understand better the underlying
issues involved.
-Raymond
This posting is provided "as is" with no warranties and confers no rights.
"SuzanneJ - formerly in PSS-SQL" <SuzanneJ - formerly in
PSS-SQL@.discussions.microsoft.com> wrote in message
news:EB2F9785-0ABB-4F0A-8132-ACBC9E1CBB21@.microsoft.com...
> Why are float values not replicated exactly? For example:
> 33.333333333333343 -> 33.333333333333336
> 233.33333333333331 -> 233.33333333333334
> I saw a previous post about this, but I'm not satisfied with the answer
> "float is an imprecise data type". Note that I don't have this problem
with
> DTS, let alone backup/restore. And it isn't meerly an issue of display
> precision: SQL treats these as different values in <> conditions and the
> values return different checksums (which is the real problem for us).
sql

Flat file with a standard of 4

Hi

I am trying to import a flat file into a table, and from there select values from the table and insert the appropriate values into different tables

The flat file is pipe delimited. I.E

File Example:

01|Name|Surname|BenCode|Counter||||||DateTime

02|Name|Surname|BenCode|SchemeID|SchemeName|

03|Name|Surname|BenCode|ID||||Date_From|Date_To||||||||||

04|Name|Surname|BenCode|SchemeID|SchemeName||||CodeID|CodeDescription||

All these different fields are in one flat file. (It would be nice if they were in 4 seperate flat files but they're not)

I want to take the file, where the ID = 01 then the data must go into table Q1

WHERE the ID = 02 then the data must go into table Q2 and so on

When i tried to do it with SSIS, it started creating columns according to the file, but it takes the first row and counts only that rows fields and calculates the columns based on the firs record, but some of the records have more fields than that of the first row.

If i can just get this flat file imported into a single table then i can split the data up based on the table.

Any ideas will be welcome. I'm quite new to SSIS.

Kind Regards

Carel Greaves

To handle the varying number of columns, you can bring each row in as a single column, then parse it in a script component. By adding multiple outputs to the script task, you can send each record type to it's own unique output. Here's a few examples:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/07/12/processing-a-flat-file-with-header-and-detail-rows.aspx

Fixed X-Axis (-100 to + 100) in chart

Hi,
I have a problem to get a fixed X-Axis in my chart.
I need a scale from -100 to +100 which does not depend on the values that come from my database. At this moment The scale increases or decreases when I for example only have values between 20 and 30. This is not useful for comparing different charts.
I tried different settings for the chart (setting maximum to 100 and minimum to -100 and Cross at 0) without effect.
Anyone got a suggestion? Thanks!Pull up chart properties dialog, go to X Axis tab, and check the "Numeric or
timescale values" checkbox.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jan" <Jan@.discussions.microsoft.com> wrote in message
news:9B4FCC9E-9CCD-44C9-A198-CF268D3A5053@.microsoft.com...
> Hi,
> I have a problem to get a fixed X-Axis in my chart.
> I need a scale from -100 to +100 which does not depend on the values that
come from my database. At this moment The scale increases or decreases when
I for example only have values between 20 and 30. This is not useful for
comparing different charts.
> I tried different settings for the chart (setting maximum to 100 and
minimum to -100 and Cross at 0) without effect.
> Anyone got a suggestion? Thanks!|||You will also need to set the Scale Minimum and Maximum on the X Axis tab
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:%239fYJXnaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Pull up chart properties dialog, go to X Axis tab, and check the "Numeric
or
> timescale values" checkbox.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jan" <Jan@.discussions.microsoft.com> wrote in message
> news:9B4FCC9E-9CCD-44C9-A198-CF268D3A5053@.microsoft.com...
> > Hi,
> >
> > I have a problem to get a fixed X-Axis in my chart.
> >
> > I need a scale from -100 to +100 which does not depend on the values
that
> come from my database. At this moment The scale increases or decreases
when
> I for example only have values between 20 and 30. This is not useful for
> comparing different charts.
> >
> > I tried different settings for the chart (setting maximum to 100 and
> minimum to -100 and Cross at 0) without effect.
> >
> > Anyone got a suggestion? Thanks!
>|||Thanks for both tips. Fixed my problems (and axis...).
"Bruce Johnson [MSFT]" wrote:
> You will also need to set the Scale Minimum and Maximum on the X Axis tab
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:%239fYJXnaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> > Pull up chart properties dialog, go to X Axis tab, and check the "Numeric
> or
> > timescale values" checkbox.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Jan" <Jan@.discussions.microsoft.com> wrote in message
> > news:9B4FCC9E-9CCD-44C9-A198-CF268D3A5053@.microsoft.com...
> > > Hi,
> > >
> > > I have a problem to get a fixed X-Axis in my chart.
> > >
> > > I need a scale from -100 to +100 which does not depend on the values
> that
> > come from my database. At this moment The scale increases or decreases
> when
> > I for example only have values between 20 and 30. This is not useful for
> > comparing different charts.
> > >
> > > I tried different settings for the chart (setting maximum to 100 and
> > minimum to -100 and Cross at 0) without effect.
> > >
> > > Anyone got a suggestion? Thanks!
> >
> >
>
>

First, Last, Middle ??

I have a Matrix that always displays two rows of data.
One row show values from Jan 1 of the current year.
The second row shows values for today.
In the foot I show the % the values have changed so YTD with the following
formula:
=(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
First(Fields!Core.Value) * 100
This works great and looks like this:
Date | Core
--
Jan 1 2005 | $400
Aug 31 2005 | $500
--
Footer +25%
I have been asked to add in the value from a year ago today but still
display the change in value from just Jan 1.
Date | Core
--
Aug 31 2004 | $300
Jan 1 2005 | $400
Aug 31 2005 | $500
--
Footer +25% (Diff between Jan 1 and Aug 31 2005)
How would I calc the % change in the footer. My formula will not work as the
"First" value is a year ago today not Jan 1. Is there a "Middle" function ?
:) :)
Thoughts ?
Thanks in Advance
Pete MitchellTry something like this:
=(Last(Fields!Core.Value) - CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))))
/
CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))) * 100
GeoSynch
"PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
news:EE7A94F6-8F3B-46EC-87E3-7DA323C927D0@.microsoft.com...
>I have a Matrix that always displays two rows of data.
> One row show values from Jan 1 of the current year.
> The second row shows values for today.
> In the foot I show the % the values have changed so YTD with the following
> formula:
> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
> First(Fields!Core.Value) * 100
> This works great and looks like this:
> Date | Core
> --
> Jan 1 2005 | $400
> Aug 31 2005 | $500
> --
> Footer +25%
> I have been asked to add in the value from a year ago today but still
> display the change in value from just Jan 1.
> Date | Core
> --
> Aug 31 2004 | $300
> Jan 1 2005 | $400
> Aug 31 2005 | $500
> --
> Footer +25% (Diff between Jan 1 and Aug 31 2005)
> How would I calc the % change in the footer. My formula will not work as the
> "First" value is a year ago today not Jan 1. Is there a "Middle" function ?
> :) :)
> Thoughts ?
> Thanks in Advance
> Pete Mitchell|||Actually, it probably shoud be:
=(Last(Fields!Core.Value) - CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))))
/ CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))) * 100
GeoSynch
"GeoSynch" <SpamSlayed@.Casablanca.com> wrote in message
news:ecLK3yprFHA.3884@.TK2MSFTNGP11.phx.gbl...
> Try something like this:
> =(Last(Fields!Core.Value) -
> CDate("1/1/"&CStr(Year(First(Fields!Core.Value))))) /
> CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))) * 100
>
> GeoSynch
>
> "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
> news:EE7A94F6-8F3B-46EC-87E3-7DA323C927D0@.microsoft.com...
>>I have a Matrix that always displays two rows of data.
>> One row show values from Jan 1 of the current year.
>> The second row shows values for today.
>> In the foot I show the % the values have changed so YTD with the following
>> formula:
>> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
>> First(Fields!Core.Value) * 100
>> This works great and looks like this:
>> Date | Core
>> --
>> Jan 1 2005 | $400
>> Aug 31 2005 | $500
>> --
>> Footer +25%
>> I have been asked to add in the value from a year ago today but still
>> display the change in value from just Jan 1.
>> Date | Core
>> --
>> Aug 31 2004 | $300
>> Jan 1 2005 | $400
>> Aug 31 2005 | $500
>> --
>> Footer +25% (Diff between Jan 1 and Aug 31 2005)
>> How would I calc the % change in the footer. My formula will not work as the
>> "First" value is a year ago today not Jan 1. Is there a "Middle" function ?
>> :) :)
>> Thoughts ?
>> Thanks in Advance
>> Pete Mitchell
>|||Thanks a bunch.
How does that work ?
There are two fields in play here : Date and Core
How is that get the Core.value when the Date.value = Jan 1 2005 ?
Pete
"GeoSynch" wrote:
> Actually, it probably shoud be:
> =(Last(Fields!Core.Value) - CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))))
> / CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))) * 100
>
> GeoSynch
>
> "GeoSynch" <SpamSlayed@.Casablanca.com> wrote in message
> news:ecLK3yprFHA.3884@.TK2MSFTNGP11.phx.gbl...
> > Try something like this:
> > =(Last(Fields!Core.Value) -
> > CDate("1/1/"&CStr(Year(First(Fields!Core.Value))))) /
> > CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))) * 100
> >
> >
> > GeoSynch
> >
> >
> > "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
> > news:EE7A94F6-8F3B-46EC-87E3-7DA323C927D0@.microsoft.com...
> >>I have a Matrix that always displays two rows of data.
> >> One row show values from Jan 1 of the current year.
> >> The second row shows values for today.
> >>
> >> In the foot I show the % the values have changed so YTD with the following
> >> formula:
> >>
> >> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
> >> First(Fields!Core.Value) * 100
> >>
> >> This works great and looks like this:
> >> Date | Core
> >> --
> >> Jan 1 2005 | $400
> >> Aug 31 2005 | $500
> >> --
> >> Footer +25%
> >>
> >> I have been asked to add in the value from a year ago today but still
> >> display the change in value from just Jan 1.
> >>
> >> Date | Core
> >> --
> >> Aug 31 2004 | $300
> >> Jan 1 2005 | $400
> >> Aug 31 2005 | $500
> >> --
> >> Footer +25% (Diff between Jan 1 and Aug 31 2005)
> >>
> >> How would I calc the % change in the footer. My formula will not work as the
> >> "First" value is a year ago today not Jan 1. Is there a "Middle" function ?
> >> :) :)
> >>
> >> Thoughts ?
> >>
> >> Thanks in Advance
> >>
> >> Pete Mitchell
> >
> >
>
>|||CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))) evaluates thusly:
Last(Fields!Core.Value) = '08/31/2005' data type Date
Year(Last(Fields!Core.Value)) = '2005' data type Integer
CStr(Year(Last(Fields!Core.Value))) converts it to a string value
so that when concatenated with "1/1/" it will evaluate to string value
"1/1/2005"
CDate converts it back to an actual date value of '01/01/2005'
GeoSynch
"PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
news:940A01C6-D8A5-4C44-8BA4-3AF232AD790F@.microsoft.com...
> Thanks a bunch.
> How does that work ?
> There are two fields in play here : Date and Core
> How is that get the Core.value when the Date.value = Jan 1 2005 ?
> Pete
>
> "GeoSynch" wrote:
>> Actually, it probably shoud be:
>> =(Last(Fields!Core.Value) -
>> CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))))
>> / CDate("1/1/"&CStr(Year(Last(Fields!Core.Value)))) * 100
>>
>> GeoSynch
>>
>> "GeoSynch" <SpamSlayed@.Casablanca.com> wrote in message
>> news:ecLK3yprFHA.3884@.TK2MSFTNGP11.phx.gbl...
>> > Try something like this:
>> > =(Last(Fields!Core.Value) -
>> > CDate("1/1/"&CStr(Year(First(Fields!Core.Value))))) /
>> > CDate("1/1/"&CStr(Year(First(Fields!Core.Value)))) * 100
>> >
>> >
>> > GeoSynch
>> >
>> >
>> > "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
>> > news:EE7A94F6-8F3B-46EC-87E3-7DA323C927D0@.microsoft.com...
>> >>I have a Matrix that always displays two rows of data.
>> >> One row show values from Jan 1 of the current year.
>> >> The second row shows values for today.
>> >>
>> >> In the foot I show the % the values have changed so YTD with the following
>> >> formula:
>> >>
>> >> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
>> >> First(Fields!Core.Value) * 100
>> >>
>> >> This works great and looks like this:
>> >> Date | Core
>> >> --
>> >> Jan 1 2005 | $400
>> >> Aug 31 2005 | $500
>> >> --
>> >> Footer +25%
>> >>
>> >> I have been asked to add in the value from a year ago today but still
>> >> display the change in value from just Jan 1.
>> >>
>> >> Date | Core
>> >> --
>> >> Aug 31 2004 | $300
>> >> Jan 1 2005 | $400
>> >> Aug 31 2005 | $500
>> >> --
>> >> Footer +25% (Diff between Jan 1 and Aug 31 2005)
>> >>
>> >> How would I calc the % change in the footer. My formula will not work as
>> >> the
>> >> "First" value is a year ago today not Jan 1. Is there a "Middle" function
>> >> ?
>> >> :) :)
>> >>
>> >> Thoughts ?
>> >>
>> >> Thanks in Advance
>> >>
>> >> Pete Mitchell
>> >
>> >
>>

First, Last, Again

I have a Matrix that always displays two rows of data.
One row show values from Jan 1 of the current year.
The second row shows values for today.
In the foot I show the % the values have changed so YTD with the following
formula:
=(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
First(Fields!Core.Value) * 100
This works great and looks like this:
Date | Core
--
Jan 1 2005 | $400
Aug 31 2005 | $500
--
Footer +25%
I have been asked to add in the value from a year ago today but still
display the change in value from just Jan 1.
Date | Core
--
Aug 31 2004 | $300
Jan 1 2005 | $400
Aug 31 2005 | $500
--
Footer +25% (Diff between Jan 1 and Aug 31 2005)
How would I calc the % change in the footer. My formula will not work as the
"First" value is a year ago today not Jan 1. Is there a "Middle" function ?
:) :)
Thoughts ?
Thanks in Advance
Pete Mitchellmaybe you could use groups to accomplish the division of the first row from
the other two. Then you could just hide the group header and footer of
group1 and hide the header of group 2. I am not sure how the first and last
will work with groups ... just a thought
> Date | Core
> --
> Aug 31 2004 | $300 ======== > group 1
> Jan 1 2005 | $400 ========> group 2
> Aug 31 2005 | $500 ========> group 2
> --
> Footer +25% (Diff between Jan 1 and Aug 31 2005)
"PeteMitchell" wrote:
> I have a Matrix that always displays two rows of data.
> One row show values from Jan 1 of the current year.
> The second row shows values for today.
> In the foot I show the % the values have changed so YTD with the following
> formula:
> =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
> First(Fields!Core.Value) * 100
> This works great and looks like this:
> Date | Core
> --
> Jan 1 2005 | $400
> Aug 31 2005 | $500
> --
> Footer +25%
> I have been asked to add in the value from a year ago today but still
> display the change in value from just Jan 1.
> Date | Core
> --
> Aug 31 2004 | $300
> Jan 1 2005 | $400
> Aug 31 2005 | $500
> --
> Footer +25% (Diff between Jan 1 and Aug 31 2005)
> How would I calc the % change in the footer. My formula will not work as the
> "First" value is a year ago today not Jan 1. Is there a "Middle" function ?
> :) :)
> Thoughts ?
> Thanks in Advance
> Pete Mitchell
>|||Thanks, good suggestion.
"MJ Taft" wrote:
> maybe you could use groups to accomplish the division of the first row from
> the other two. Then you could just hide the group header and footer of
> group1 and hide the header of group 2. I am not sure how the first and last
> will work with groups ... just a thought
> > Date | Core
> > --
> > Aug 31 2004 | $300 ======== > group 1
> > Jan 1 2005 | $400 ========> group 2
> > Aug 31 2005 | $500 ========> group 2
> > --
> > Footer +25% (Diff between Jan 1 and Aug 31 2005)
>
> "PeteMitchell" wrote:
> > I have a Matrix that always displays two rows of data.
> > One row show values from Jan 1 of the current year.
> > The second row shows values for today.
> >
> > In the foot I show the % the values have changed so YTD with the following
> > formula:
> >
> > =(Last(Fields!Core.Value) - First(Fields!Core.Value)) /
> > First(Fields!Core.Value) * 100
> >
> > This works great and looks like this:
> > Date | Core
> > --
> > Jan 1 2005 | $400
> > Aug 31 2005 | $500
> > --
> > Footer +25%
> >
> > I have been asked to add in the value from a year ago today but still
> > display the change in value from just Jan 1.
> >
> > Date | Core
> > --
> > Aug 31 2004 | $300
> > Jan 1 2005 | $400
> > Aug 31 2005 | $500
> > --
> > Footer +25% (Diff between Jan 1 and Aug 31 2005)
> >
> > How would I calc the % change in the footer. My formula will not work as the
> > "First" value is a year ago today not Jan 1. Is there a "Middle" function ?
> > :) :)
> >
> > Thoughts ?
> >
> > Thanks in Advance
> >
> > Pete Mitchell
> >