I am using data flow. source is ole db and target is flat file (csv). I run sql server stored procedure in source and mapped all columns to target file.
Value "-5.0000000000000003E-2" is giving me hard time. It's coming in target file how can remove exponential before writing to target file. In source table that value is coming from float type column. I would like to use some function in select sql if I can.
Thank you - Ashok
Exponential Moving avg is calculated using the formula.EMA = (Today's Price)* K + (EMA yesterday) * (1-K)where K = 2 / (N+1)The user is going to Input the K.It is something likeF(N) = Price * K + F(N-1) * (1-K)How can I reference, the previously calculated value in the Next rowcalculation. I need to implement this in SQL Server.I created a Stored procedure to do this and I used a Temp tbale withIdentity.Create Table #TempMovAvg(MID int identity(1,1) Primary key, tempDate DateTime, tValue float)I Populate the data for that temp table using the below query.Insert Into #TempMovAvg (tempDate, tValue)Select Date, Price From DataTableI tried Diff options to calculate the exponential Moving Avg using theabove formula, but none of them are giving the correct answers. I amnot able to reference the Prev Calculated value in the Next rowcalculation.Some queries I used.Select a.TempDate, a.tValue,0.9*A.tValue+0.1*(Select 0.9*t1.tValue+0.1*t2.tValue From #TempMovAvg t1, #TempMovAvgt2 Where t1.MID=A.MID and t2.MID=t1.MID-1)FROM #TempMovAvg AWhere A.MID>=2order by a.TempDateSELECT A.MId,SUM(CASE WHEN B.MID=A.MID THEN 0.9*B.tValueELSE 0.1*A.tValue END) exponential_averageFROM #TempMovAvg A, #TempMovAvg BWHERE A.MID>=2 AND A.MID BETWEEN B.MID AND B.MID+1GROUP BY A.MIDAny help will be greatly appreciated.thanksGanesh
View 1 Replies View RelatedWhenever we restart the services on this one SQL server 2005 instance, the database autogrowth changes to grow by 2500%. We have to manually change the autogrowth of data file to some sane number. Has anyone faced this issue? We tried to put SP2 on but even that doesn't help. Any help would be appreciated
View 1 Replies View RelatedHi,
I am values in scientific notation when i am rendering the report into excel.
I wanted the number to be displayed as it is without any scientific notation(exponential format)
Thanks in advance
Nalini
For whatever reason i'm unable to cast anything more thtan e-4 to a float which makes no sence. Am i missing something?
select cast( '1.550e-6' as float)
?????????
returns 1.55E-06
????????
select cast( '1.550e-5' as float)
?????????
returns 1.55E-05
????????
select cast( '1.550e-4' as float)
returns 0.000155
select cast( '1.550e10' as float)
returns 15500000000
I want to save 999999999 as real data in sql.but it saved 1+E09.
how can I save 999999999?
I am using SSIS to populate a star schema.
The issue is in the data flow for loading and setting the Fact table dimension keys (the dimensions are all loaded fine). After 16 rather pedestrian Lookup Transformations, I have an escalating problem adding additional Lookup transforms to the Data Flow. The problem is not in execution; the problem is adding more transforms in design mode.
Lookup # Fields in Data Flow Time to validate that lookup
<17 47 Sub-second
17 48 2 sec
18 49 4 sec
19 50 8 sec
20 51 16 sec
21 52 32 sec
22 53 64 sec
While I€™m intrigued by the mathematical progression that is forming here, the issue is that I have at least 6 more Lookups to perform. I hope you can see my dilemma.
I have gone to where it takes a little over 4 minutes each to validate the lookup transform and its associated Derived Column transform and Union transform (Total 12 Minutes). Not only does this add up to many idle minutes to each design step, BUT it breaks the debugger as it pre-validates the ENTIRE data flow before it ever switches into debugging mode.
Some notes:
1. It doesn€™t matter what order the Lookup transforms occur in, the timings are exactly the same.
2. I tried many Data Flow execution optimizations, but they don€™t improve the validation times (or even get a chance to improve the execution times!)
I realize this may be somewhat of a unique problem.
Thanks for any help you are able to lend.
-Dave
Am converting varchar field to float and summing using group by and next inserting to varchar field(table).
while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726
My question is why it is converting while inserting ? and how to avoid it.
select query : SUM(CONVERT(float,(rtrim(REPLACE(REPLACE( column1, CHAR(13), ' '), CHAR(10), ' '))))) as AggregateValue
I am importing a text file with a column (serial numbers) with alphanumeric data, some mixed and some only numeric. The very large values that are all numeric are being converted to exponential when I run it thru an import package in SQL Server Integration Services (2005)
Ex. 4110041233214321 --> 4110040000000000 (displays as 4.11E+15)
In the past I dealt with this by importing the text file into Excel and changing the format of the column to number. This works even when many of the values contain alpha characters. I am not sure how to accomplish this same thing without going thru Excel. If you have any ideas on this I would be happy to hear from you.
I am importing the text file into a sql table.
Hello,
I use OPENROWSET to read values from Excel and store them in a SQL Server table. In the Excel file I have a row having format 'Number' with two decimal places.
Example: 1225000.00
When I select this value using SSMS I get the correct value:
1225000
Strange enough, I cannot see the decimals anymore. However, when I now store this value into my table and then select it from there I get: (the datatype in the table is VARCHAR(max))
1.225e+006
I would not care if I could convert this back to a numeric datatype but this seems not to work: CAST('1.225e+006' as INT) throws an exception. Obviously OPENROWSET sends the data strictly as a character string. Storing this into varchar(max) works for small figures but it starts to use exp values for big figures.
Does anybody has an idea how to bring huge Excel based figures safely into a MS SQL Table ?
Thanks: Peter