SQL POWER And Decimal Pricision Help

Apr 8, 2008

Hello all, I am having a lot of trouble maintaining decimal precision in the script below.  Specifically, the statement:  SELECT @SKIPPROB = 1-(POWER(.999,@CURRENT - @LASTHIT)).  This should be returning a large decimal value, but some of the values that are being updated into the Straights table keep showing up incorrectly rounded - sometimes as whole numbers. In one particular example, the statement above would become...
 1-(POWER(.999,10201)).  Note: 10201 is the result of @CURRENT - @LASTHIT. This should then equate to 0.99996306 as the answer to the formula. For some reason, when I open the Straights table, the result shown is 1.00000000.  Please note that the result is loaded into the variable @SKIPPROB which is declared to be a Decimal(10,8). Also, the column in the straights table that this value is updated into is also set as Decimal(10,8). I am still very new to this, so am I overlooking something. I don't understand why the result is being rounded up to 1.00000000. Any help is appreciated.

DECLARE @EXACT INTEGER, @CURRENT INTEGER, @LASTHIT DECIMAL(10,5), @AVGSKIP decimal(10,2), @MAXSKIP INTEGER, @1000AGO DECIMAL(10,4), @HITS1000 DECIMAL(10,4), @HITSLIFE DECIMAL(10,4), @SKIPPROB Decimal(10,8)

SET @EXACT = 1000

WHILE @EXACT > 0

BEGIN

SET @EXACT = @EXACT -1

SELECT @CURRENT = (SELECT MAX(GAME) FROM History)

SELECT @LASTHIT = (SELECT MAX(GAME) FROM History Where EXACT = @EXACT)

SELECT @AVGSKIP = (SELECT AVG(cast(exactskip as decimal(10,2))) FROM HISTORY WHERE EXACT = @EXACT)

SELECT @MAXSKIP = (SELECT MAX(EXACTSKIP) FROM History WHERE EXACT = @EXACT)

SELECT @1000AGO = (SELECT MAX(GAME) FROM History)-1000

SELECT @HITS1000 = (SELECT COUNT(EXACT) FROM History WHERE EXACT = @EXACT AND GAME > @1000AGO)

SELECT @HITSLIFE = (SELECT COUNT(EXACT) FROM History WHERE EXACT = @EXACT)

SELECT @SKIPPROB = 1-(POWER(.999,@CURRENT - @LASTHIT))

UPDATE Straights

SET [CURRENT SKIP] = @CURRENT - @LASTHIT,

[AVG SKIP] = @AVGSKIP,

[MAX SKIP] = @MAXSKIP,

[TIMES DUE] = (@CURRENT - @LASTHIT)/1000,

[FLIPS] = (@CURRENT - @LASTHIT)/692.8005,

[HITS 1000] = @HITS1000,

[PERCENT 1000] = (@HITS1000 / 1000)*100,

[TOTAL HITS] = @HITSLIFE,

[PERCENT LIFETIME] = (@HITSLIFE / @CURRENT)*100,

[SKIP PROB] = @SKIPPROB

WHERE EXACT = @EXACT;

END

View 2 Replies


ADVERTISEMENT

Pricision Of Float

Jul 20, 2005

Hi,Just wonder whyPRINT CAST(0.0573542567654 AS float)will give the rounded reult0.0573543rather than the original number?"float" should be 'big' enough to hold numbers that have even moredecimal places. How come it round up at the 7 decimal place?since I need to do some calculations with accumulated values. Therounded figure will cause significant error after a number of opertions.Are there any way to work around it?thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

Power Pivot :: Show Images In Power View With Power Query On Excel Desktop Version / Office 365

Aug 2, 2015

I am trying to show images in a product listing in power view.I work with an excel 2013 desktop version based on an office 365 pro account.I did the following steps:

import of an excel file with an article list via power query and loading the data to the data model import jpg images from a folder via power query, setting content as binary type and loading the data to the data modellinking both tables in power pivot--> manage via the image namesetting the table behavior for the images table under power pivot --> manage --> Advanced (e.g. Default Image: Content)opening power view and building article cards with article number and imageProblem: only a camera icon shows up in power view

Is there a solution with a desktop version?Can I use my Office 365 Pro account to make it work? How?Why is there no solution showing images in a pivot table?Link to Dropbox with power pivot files 

View 9 Replies View Related

Power Pivot :: Create Power View Report - Button Does Not Show Up For Some Reports

Nov 3, 2015

I have several reports in a Power View Gallery. In Gallery view, most of the reports show the "Open New Excel Workbook", the "Create Power View Report", and the "Manage Data Refresh" buttons on the right side of the report list. Why would some reports not have these buttons available? In the attached image you can see one report with the buttons and one without the buttons.

View 5 Replies View Related

Power Pivot :: Power Query Error Expression / Cannot Convert The Value To Type Text

Jul 20, 2015

I've imported a number of excel sheets into a Power Query Table. All seems to appear ok until I load the data. Of the 15k rows around 2k have a similar error where it cannot convert an integer to type string as below example

Expression.Error: We cannot convert the value 40 to type Text.
Details:
    Value=40
    Type=Type

The columns in question are all of integer type, I've looked through the M query and there is no conversion to string taking placeThe values where we don't get the error are also integers hence the intriguing question is why does the error occur on a subset and not the others. I suspect there is a limit to the number of errors also somewhere internally M query is converting the column to text for some reason.

View 2 Replies View Related

Power Pivot :: IF In Power Query Statement - Converting Text To Numbers

Nov 18, 2015

Slow loading issue with an if statement. In the raw data the field [Location] is a text field e.g. 0010. I have a parameterised query that get a Location_Value from Excel and passes it to the PQ query using:

#"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))

This works fine if you chose a single location.  However I wanted to be able to select all locations and text is horrible to work with so in PQ I used the change type function to change the location column into whole numbers. I changed excel to also pass a number as Location_Value.   I was therefore surprised when the same query took 2.5 times longer to refresh????

My PQ now looks like this

  #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Location", Int64.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))

I'm wondering if I need to do something to the ([Location] = Location_Value) bit as maybe it still thinks [Loation] is text and it is trying to compare it to a number. I st assumed the step above meant that [Location] was now a number, but maybe you still have wrap it with some kind of VALUES or TEXT function?

View 9 Replies View Related

Power Point :: How To Generate Rank Based On Some Group In Power Pivot

Apr 10, 2015

I have data like below

Country     State            Rank
India         Kerala            1
India         Kerala            2
India         Kerala            3
India         Tamil Nadu     1
India         Tamil Nadu     2
India         Orissa            1
India         Orissa            2
US            Florida            1
US            Florida             2
US            NewYork         1

I have to generate rank like this in power pivot. How can I achieve it?

View 3 Replies View Related

Power Pivot :: Error When Updating From Power Query Source

Jun 11, 2015

When updating a Power Query Source in Power Pivot, I'm getting the following error message:

Basically saying that OLE DB or ODBC-error occured when:

- Connections have been imported from a different workbook or
- the workbook has been created in a newer Version of Excel

None of which is the case here. What can cause this?

System: Virtual machine (VMware) on Windows 2008 R2, Office 2010, 64bit, using temporary profile.

View 5 Replies View Related

Power Pivot :: How To Set Refresh For Excel File Uploaded In Power Bi

Sep 9, 2015

I have opened an account in [URL] and taken the 60 days trial for power bi pro. I've developed power pivot and generated power views in share point 2013. But, I'm new to Power BI desktop. I have created a report in power BI desktop and published to [URL]. Also, I have uploaded an excel file directly to [URL] and created the report from the workspace available there itself and pinned the report to dashboard also. Everything is fine till this. But, I need to refresh the file which I have uploaded. I have some dummy data in excel sheet.

ZipCode State ZipName

2345 AA AA
456 BB BB
6787 CC CC

 This has been created as a table and then added to data model. So, power pivot has been created for the same. Then I have uploaded this file to [URL] site. But, I'm getting an error message while trying to schedule refresh for the same.

"You cannot schedule refresh for this dataset because it does not contain data model connections. You cannot schedule refresh on worksheet connections or linked tables. To schedule refresh the data must be loaded into the data model."

How can I create a data model connection? How can I schedule refresh for an excel file like this?

View 3 Replies View Related

Power Pivot :: Power View - Mix Series Types On Same Chart

Jul 25, 2015

I'm a relative newcomer to Power View. I've been playing with charts and have been struggling to combine both line and bar on the same chart. It would appear this functionality is not available. Considering this is basic functionality when it comes to charting, how to achieve this....

View 3 Replies View Related

Power Pivot :: Power Query Conditional Text Replacement

Jul 16, 2015

I'm looking to replace text in a given column given a set of conditions in the other columns. Please see below the M query in the advance editor and in particular the bold text. Here I've created a new entry that would appear in the query applied steps window in the power query editor that I have called "Replace Values". The logic is if Data.Column4 column equals "London" then replace null values in Data.Column5 with London. However when I save the query below I get the error 

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

I plan to change the expression to test for multiple conditions however I need to get the basic expression working first. The other frustration i had with the "if" statement is it had to have an else even though I didn't require it, am i doing something wrong here?

let
FullFilePath = "C:PermanentDwellings.xlsx",
Source = Excel.Workbook(File.Contents(FullFilePath)),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16",

[Code] ....

View 8 Replies View Related

Power Pivot :: How To Customize X-axis Of Power BI Dashboard

Aug 26, 2015

I want to show on Power BI Dashboard a moving average - for example, I want to always show the last 30 measurement of body temperature but it looks like Power BI dashboard shows all measurements I have and compress them - which makes the dashboard ugly.

I tried to customize the X-axis properties but I dont know what I should change the default start/stop properties to (where the default property value is automatic).

[URL]

View 3 Replies View Related

Legacy Database Uses Decimal Data Types.--&> AutomobileTypeId (PK, Decimal(10,0), Not Null) Why Not Integers Instead ?

Sep 26, 2007

I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
 
Example: AutomobileTypeId (PK, decimal(10,0), not null)

View 5 Replies View Related

Power Pivot :: Power BI Desktop As Backend

Sep 29, 2015

We are trying to build dashboards using Power BI desktop version with Power Pivot data model as a back end. To import the data to Power BI,we used Import ->Excel workbook contents option and successfully imported the model to Power BI .When I tried to refresh the Power BI,It's hitting the backend tables available in the power Pivot model. But my requirement is I need to refresh the Power BI from the power Pivot Datamodel(I'm expecting an option like Import from Power Pivot in Tableau).

View 10 Replies View Related

Power Pivot :: Slicers Won't Print In Power BI

Apr 18, 2015

One big reason why we have turned to Power BI for Office 365 is to share Power Pivot models with our senior executives who all use Macs.

While they have been able to successfully view the Power Pivot models and interact with the Slicers in Power BI, they have not been able to print the reports. We thought it was a Mac issue but it's happening on PCs too. There doesn’t seem to be a way to resize the report so that all columns print. So we then tried to click on the tiny button located in the bottom right corner that says “View Full-Size Workbook.” The file then opens up in Excel Online. Then when we click on the printer icon, we see the report but the slicer becomes invisible. When printing, all columns do show up however the slicer is invisible.

How to successfully print Power Pivot reports with slicers in Power BI?

View 2 Replies View Related

Data Type With Decimal Point For Decimal Values But Not For Whole Integers

Dec 8, 2013

I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?

View 2 Replies View Related

Cast Or Convert Nvarchar With Comma As Decimal Separator To Decimal

Apr 29, 2008

Hello.

My database stores the decimals in Spanish format; "," (comma) as decimal separator.

I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.


Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:

Error converting data type varchar to numeric



Any knows how to resolve.

Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.

View 5 Replies View Related

Converting (casting) From Decimal(24,4) To Decimal(21,4) Data Type Problem

Jul 24, 2006

Hello!



I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?

Thanks for giving me any advice,

Ziga

View 6 Replies View Related

How Can I Use The Decimal Comma Instead Of Decimal Point For Numbers In Jet Engine?

Sep 19, 2007



I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl

View 4 Replies View Related

Converting Decimal To String W/O Decimal Point

Jul 23, 2005

I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?

View 6 Replies View Related

Converting Decimal Point To Decimal Comma

Nov 30, 2007

Hi all,

I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.

e.g.
1,500,123.00 to 1.500.123,00

Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.

Any help would be much appreciated

Thanks!

View 5 Replies View Related

Decimal Limited To 4 Digits To Right Of Decimal?

Jun 18, 2007





I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.

I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.



It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).

Is the decimal limited to scale 4 ???



Thank you for your help!

Anna





View 3 Replies View Related

Packed Decimal To Decimal Conversion

Jun 4, 2007

Hi,




I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.

File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.

Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 œ&
20060715 0001010100010101




Please help!



Thanks

Mirudhu

View 4 Replies View Related

T-SQL ROUND(decimal, Int) Vs C# Round(Decimal, Int32)

Jan 30, 2006

Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt

View 3 Replies View Related

POWER Function

Dec 1, 2006

Hello,
I'm trying to follow a specification here:
 W1 = (W0^0.333 + G * D/1000)^3  (the ^ symbol means 'to the power')
In my stored procedure I have used the following test data with unexpected results. Can anyone tell me if I'm using POWER properly?
@Value  = POWER(((POWER(150,(1/3)) + 2) * 10/1000),3 )
Thanks

View 8 Replies View Related

Power Outage

Aug 31, 2000

I have a SQL server that's partly managed by another team for their vendored application. Anyway, they're having some weird issues with performance. Last weekend a guy tripped over the power cord (duh!). I looked at the logs and there were no recovery errors on restart and it seems to be running fine..

I'm looking for help on any diagnostics I can run to check anything else out?

Thanks,
ann

View 1 Replies View Related

Power Point

Oct 26, 2005

Hi there.

Is it possible to convert the Power Point file to SQL Server Version 7 or 2000? My PowerPoint files have no images or pictures, only lyrics for songs that are used in the church during sunday services.

I was appointed to make a system in VB6 but my first job is to convert the PowerPoint files to SQL Server. There are 2000 songs in the powerpoint right now.

Please help.

Thanks.

View 2 Replies View Related

Problem With POWER(x,y) In Sql

May 8, 2006

hi,

i can't see why the following field is not working correctly, ie: it's always coming up with 0:

((POWER((1+((((((POWER((1+(dbo_table1.APR/100)),(1/12)))-1))*12))/12)), dbo_table1.FREQUENCY)-1)*(12/dbo_table1.FREQUENCY))*100 AS YIELD

breaking the code down to a smaller formula it does work.

it looks like i can't have more calculations/references inside a POWER(x,y) function.

any comments would be appreciated.

thank you

View 4 Replies View Related

Power Regression

May 30, 2006

I need to write some SQL to do a power regression for a trendline. I have 2 columns of data which represent my X, Y data and all I'm after is the a and the b for the function y=ax^b. Has anyone ran into this before?? I know SSAS has a linear regression function but my data really only fits the power model.

View 4 Replies View Related

Power/Factorial Not Working

May 4, 2006

I am trying to get the value, 3 to the 2.2 power (3^2.222).

Can someone tell me how to do it? The literature says to use:

POWER(3,2.222)

however ths is rounding my values for some reason.

View 1 Replies View Related

Power Pivot :: Sum With Or Conditional

Jul 2, 2015

I have 2 table below:

Table 1:

Product No Quantity
A

Table 2:

Product No Receiving Quantity Type
A 10 NR
A 5 CN
A 6 NR
A 7 DC
A 6 CN

I want to sum Receiving Quantity with Type "CN" OR "DC". The result of Quantity in Table 1 should be like this:

Product No Quantity
A 12

View 3 Replies View Related

Script Problem With POWER(x,y)

May 8, 2006

hi,

i can't see why the following field is not working correctly, ie: it's always coming up with 0:

((POWER((1+((((((POWER((1+(dbo_table1.APR/100)),(1/12)))-1))*12))/12)), dbo_table1.FREQUENCY)-1)*(12/dbo_table1.FREQUENCY))*100 AS YIELD

breaking the code down to a smaller formula it does work.

it looks like i can't have more calculations/references inside a POWER(x,y) function.

any comments would be appreciated.

thank you

View 3 Replies View Related

Exporting To Power Point

Feb 28, 2007

How to Export SQL Report to Power Point.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved