The underlying table for my fact contains a Sale field whose data type is decimal.
In the cube editor, I have tried a few options (both for Data Type and Display format) but in my front end I am still getting the results with 2 decimal places.
Can someone kindly help me how I can get the results in a rounded form so that the decimal places are not shown e.g. instead of seeing 12,345,678.98 I want the results rounded as 12,345,679.
If (and i really hope not) it boils down to using MDX in the front end then can u kindly guide me since I can only spell MDX at the moment :(
Hi, I am trying to get my string to 2 decimal places... Here is the code:string sumSQL = "SELECT CONVERT (float,sum(callTime)/count(callTime)) as CAVG FROM leads where agent = '" + agent + "' and " + dates.Text; SqlCommand sumCMD = new SqlCommand(sumSQL, conn2); object null1 = sumCMD.ExecuteScalar();if (null1 != null) {string avgS = sumCMD.ExecuteScalar().ToString();Response.Write(avgS + "<br>" + "</td></tr><tr>"); }
I have many columns of data. They are all using the money datatype. When Ienter a value into a column such as 32.00, it only shows up as 32. Whenoutputted to an ASP page using a query it also only displays 32. If thedata is 32.33, then the display is 32.33. How do I get it to display 32.00?Thanks!DarrenMCP
Hello, I have a datatable with a column of decimal numbers 0 - 1. I want to move the decimal 2 places to the right to make the column a percent column. I don't know how to do that. Thanks Steve
I am new to SQL 7, and am having trouble with the money and smallmoney data-types. When I pull data from these columns, it has four decimal places, and I need only two (four is screwing the perl code up). What do I do now?
I use Visual Basic 6.0, I have notice that sometimes when I add a new record into a table using ADO Recordset, SQL Server places the new record wherever it wants, and I suppose that any new record added to a table is placed at the end.
This situation cause me a lot of problems on the normal perfomance of my aplication.
I tried to include the ORDER BY sentence on the SELECT of my Recordset, but a ADO Recordset with ORDER BY is not dynamic.
The underlying table for my fact contains a Sale field whose data type is decimal.
In the cube editor, I have tried a few options (both for Data Type and Display format) but in my front-end I am still getting the results with 2 decimal places.
Can someone kindly help me how I can get the results in a rounded form so that the decimal places are not shown e.g. instead of seeing 12,345,678.98 I want the results rounded as 12,345,679.
If (and i really hope not) it boils down to using MDX in the front end then can u kindly guide me since I can only spell MDX at the moment :(
I have created a stored procedure in SQL Server. I found it very slow, so i putted "select getDate(), 'testposition 1'" at different places, so I could see what part of the code that takes time.
The problem is: Depending on where I put the select statements, the execution of the stored procedure seems to just stop. And depending on where i put the select statements, it stops at different places.
This is how I do (example): 1. I re-create the stored procedure with some "select getDate()"-statements 2. I run the stored procedure 15:00:00 3. I cancel the stored procedure after 20 seconds and look at the resultsets. All getDate-functions show a time between 15:00:00 and 15:00:02 4. I run the stored procedure 15:01:00 5. I cancel the stored procedure after 5 seconds and look at the resultsets. The same amount of resultsets are showed, so I can make the conclusion that the execution stopped at the same place as last time. All getDate-functions show a time between 15:01:00 and 15:01:02 this time too. 6. I re-create the stored procedure with some new "select getDate()"-statements 7. Now the execution stops at an other position. Somtimes even between two "select getDate()"-statements!
I pasted the whole stored procedure here:
drop PROCEDURE spUpdateASW go
create PROCEDURE spUpdateASW AS
DECLARE @DataBatchID int DECLARE @DataHeaderID int DECLARE @ASWTableID int DECLARE @ASWTableName varchar(25) DECLARE @ASWFieldName varchar(25) DECLARE @AllowASWUpdate tinyint DECLARE @IsPrimaryKey tinyint DECLARE @DataTypeIsNumeric tinyint DECLARE @Data varchar(100)
DECLARE @SQL_Where as varchar(400) DECLARE @SQL_Insert as varchar(1000) DECLARE @SQL_InsertValues as varchar(400) DECLARE @SQL_Update as varchar(1000) DECLARE @updateCounter int DECLARE @whereCounter int DECLARE @SQL_CheckIfAlreadyExist as varchar(1000)
DECLARE @ErrorMessage varchar(500)
DECLARE @RuleWhen as varchar(50) DECLARE @RuleWhenToExec as varchar(500) DECLARE @tempStr as varchar(700)
DECLARE @server varchar(50) DECLARE @shortServer varchar(50) SET @server = 'GIBSON_A3MFGF_T1.S44E5797.A3MFGFT1' SET @shortServer = 'GIBSON_A3MFGF_T1' DECLARE @SQL varchar(5000)
select getdate(), 'testposition 1'
CREATE Table #tmptblUpdateASW( ASWRowAlreadyExists int, RuleWhenIsValid int ) INSERT INTO #tmptblUpdateASW(ASWRowAlreadyExists, RuleWhenIsValid) Values(-1, -1)
DECLARE Batch_Cursor CURSOR LOCAL FOR SELECT DataBatchID from tblDataBatch where DateConverted is not null and ASWUpdateStarted = 0 and DataBatchID not IN( select fkDataBatchID from tblDataHeader where DataHeaderID IN( select fkDataHeaderID from tblASWData where ConversionErrorMessage is not null ) ) OPEN Batch_Cursor
FETCH NEXT FROM Batch_Cursor INTO @DataBatchID WHILE @@FETCH_STATUS = 0 BEGIN Update tblDataBatch set ASWUpdateStarted = 1 where DataBatchID = @DataBatchID
DECLARE Header_Cursor CURSOR LOCAL FOR SELECT DataHeaderID from tblDataHeader inner join tblAgileFieldType on tblDataHeader.fkAgileFieldTypeID = tblAgileFieldType.AgileFieldTypeID where fkDataBatchID = @DataBatchID and isSentToASW = 0 order by tblAgileFieldType.InsertOrder OPEN Header_Cursor FETCH NEXT FROM Header_Cursor INTO @DataHeaderID WHILE @@FETCH_STATUS = 0 BEGIN DECLARE ASWTable_Cursor CURSOR LOCAL FOR SELECT ASWTableID, ASWTableName, RuleWhen from tblASWTable inner join tblASWField on tblASWTable.ASWTableID = tblASWField.fkASWTableID inner join tblASWData on tblASWField.ASWFieldID = tblASWData.fkASWFieldID where fkDataHeaderID = @DataHeaderID group by ASWTableID, ASWTableName, RuleWhen, InsertOrder order by InsertOrder OPEN ASWTable_Cursor FETCH NEXT FROM ASWTable_Cursor INTO @ASWTableID, @ASWTableName, @RuleWhen WHILE @@FETCH_STATUS = 0 BEGIN exec spBuildRuleString @DataHeaderID, @RuleWhen, @RuleWhenToExec output, 0
SET @tempStr = 'IF ' + @RuleWhenToExec + ' UPDATE #tmptblUpdateASW SET RuleWhenIsValid=1 ELSE UPDATE #tmptblUpdateASW SET RuleWhenIsValid=0' EXEC (@tempStr) IF (SELECT RuleWhenIsValid FROM #tmptblUpdateASW) = 1 BEGIN
set @ErrorMessage = null exec spASWDataCheck_hardCoded @DataHeaderID, @ErrorMessage output
SET @SQL_Insert = 'INSERT INTO ' + @server + '.' + @ASWTableName + '(' SET @SQL_InsertValues = 'VALUES(' SET @SQL_Update = 'UPDATE ' + @server + '.' + @ASWTableName + ' set ' SET @updateCounter = 0 SET @SQL_Where = ' WHERE ' SET @whereCounter = 0
DECLARE ASWField_Cursor CURSOR LOCAL FOR SELECT ASWFieldName, AllowASWUpdate, IsPrimaryKey, DataTypeIsNumeric, Data from tblASWField inner join tblASWData on tblASWField.ASWFieldID = tblASWData.fkASWFieldID where fkASWTableID = @ASWTableID and fkDataHeaderID = @DataHeaderID OPEN ASWField_Cursor FETCH NEXT FROM ASWField_Cursor INTO @ASWFieldName, @AllowASWUpdate, @IsPrimaryKey, @DataTypeIsNumeric, @Data select getdate(), 'testposition 2' WHILE @@FETCH_STATUS = 0 BEGIN select getdate(), @ASWFieldName, 'testposition 3' set @Data = replace(@Data, char(39), char(39) + char(39)) if @DataTypeIsNumeric = 0 set @Data = char(39) + @Data + char(39)
set @SQL_Insert = @SQL_Insert + @ASWFieldName + ', ' set @SQL_InsertValues = @SQL_InsertValues + @Data + ', ' IF @AllowASWUpdate = 1 BEGIN set @SQL_Update = @SQL_Update + @ASWFieldName + ' = ' + @Data + ', ' set @updateCounter = @updateCounter + 1 END IF @IsPrimaryKey = 1 BEGIN set @SQL_Where = @SQL_Where + @ASWFieldName + ' = ' + @Data + ' and ' SET @whereCounter = @whereCounter + 1 END
FETCH NEXT FROM ASWField_Cursor INTO @ASWFieldName, @AllowASWUpdate, @IsPrimaryKey, @DataTypeIsNumeric, @Data END select getdate(), 'testposition 4' CLOSE ASWField_Cursor DEALLOCATE ASWField_Cursor
IF @whereCounter = 0 begin insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage) values(getDate(), @DataHeaderID, @ASWTableID, '(allvarligt fel. Inget skickades till ASW)', 'Fel! Inga primary keys var valda för denna tabellen!') end ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) > 1 begin insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage) values(getDate(), @DataHeaderID, @ASWTableID, '(allvarligt fel. Inget skickades till ASW)', 'Fel! Kombinationen av primary keys genererade följande where-sats: ' + @SQL_Where) end ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) = 1 and @updateCounter > 0 begin EXEC(@SQL_Update) insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage) values(getDate(), @DataHeaderID, @ASWTableID, @SQL_Update, @ErrorMessage) update tblDataHeader set isSentToASW = 1 where DataHeaderID = @DataHeaderID end ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) = 0 begin EXEC(@SQL_Insert) insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage) values(getDate(), @DataHeaderID, @ASWTableID, @SQL_Insert, @ErrorMessage) update tblDataHeader set isSentToASW = 1 where DataHeaderID = @DataHeaderID end
END
FETCH NEXT FROM ASWTable_Cursor INTO @ASWTableID, @ASWTableName, @RuleWhen END CLOSE ASWTable_Cursor DEALLOCATE ASWTable_Cursor
FETCH NEXT FROM Header_Cursor INTO @DataHeaderID END CLOSE Header_Cursor DEALLOCATE Header_Cursor
UPDATE tblDataBatch set DateToASW = getDate() where DataBatchID = @DataBatchID
FETCH NEXT FROM Batch_Cursor INTO @DataBatchID END CLOSE Batch_Cursor DEALLOCATE Batch_Cursor
I am trying work out if certain columns in a database table have the "correct" data in them and work out the "percentage completeness" of the data...
So, I have been allocating a 1 if the data is complete and a 0 if it's not. Then adding up the results and dividing them by the number of results and multiplying them by 100 to get a percentage.
BUT - the answer always comes back as a whole number and I cannot work our why!
This is my sql:
SELECT Clients.ClientNumber_Legacy, CASE WHEN Client_Details_Enhanced.ClientType_Code = 'NK' THEN 0 ELSE 1 END, CASE WHEN Client_Details_Enhanced.Title_Code = 'NK' THEN 0 ELSE 1 END, (CASE WHEN Client_Details_Enhanced.ClientType_Code = 'NK' THEN 0 ELSE 1 END) + (CASE WHEN Client_Details_Enhanced.Title_Code = 'NK' THEN 0 ELSE 1 END) as total,
There's been several good posts on using the node description of a model as the end user description for a specific cluster. My model uses a number of continuous input columns defined as currency from a fact table in the source cube. After processing, the node description has elements that look like this:
-0.5799759795 <=Interest Expense <=0.8397462488 ,
Since the source data is currency, this makes the node description look a little strange. The data type in the model is set as double. The precision implied by the description is not what I want the model to consider. In the case above, the difference between the numbers listed is not significant.
It would be great to have a better node desciption that doesn't imply so much precision, but the bigger question is why does the cluster model turn currency types into doubles. Should I set the data type to long in the model so that cents are ignored? I know I should probably use discrete inputs, but I don't want to have to discretize the currency values in the cube since this would require me to set up fact dimensions for each currency column in the fact table.
Hello, is there a way to convert the value to just 2 decimal places, I created the report in Reporting Services and it has quite a few digits to each value. I looked at the table and found that the data type is {Float}. Is there a way to convert the values to just 2 decimal places?..Thank You.
Hi, i need to display my data in 2 decimal places but now i'm getting results after some calculation (for eg. 2.336224). How can i round it off to 2.34?
Dim subtot As Double Dim tax As Double Dim tot As Double subtot = "0.00" Dim sql As String sql = "SELECT items.qty, products.descrip, products.price FROM items INNER JOIN products ON items.productid = products.id WHERE (items.orderid = " & Request.QueryString("oid") & ")"Dim objConn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|AllStar.mdf;Integrated Security=True;User Instance=True") Dim cmdCustomers As New SqlCommand(sql, objConn)Dim dataReader As SqlDataReader objConn.Open() dataReader = cmdCustomers.ExecuteReader(CommandBehavior.CloseConnection)While dataReader.Read subtot = subtot + (dataReader.GetValue(0) * dataReader.GetValue(2)) End While tax = (subtot * 0.07) tot = (subtot + tax) Label1.Text = subtot Label2.Text = tax Label3.Text = tot ---------------------------------------- How to a convert the variable tax to just two decimals? I tried label2.text = CType(tax, Double) but that didn't work either Thanks in advance
Hi. I have a query that returns several averages and percentages. Is there some way to set up the query so the results only go to 2 decimal places? Here is a sample of the query I am using: Code:
$query = "SELECT COUNT(deal_id), SUM(vs), SUM(vs)/COUNT(deal_id)*100, AVG(fin) FROM sales GROUP BY salesperson";
It works great,except the results are several decimal places long, and I need it to be only 2.
I have a table with a money field that had previously been running calculation and storing the data into the database's money field. Since this field supports 4 decimal places, it was storing 4 decimal places worth of data. I have since cleaned up my insert routine to round everything up to two decimal places and it only inserts the rounded values. I now have to go back and update the old data with the two decimal place rule. How would I go about doing this?
Hey - I have a quick question and know that it is probably pretty simple, but I am stumped. I have a query where I need to make a colum a number that looks like a percent with 2 significant digits:
i.e., SELECT tblNumericCovert.number1, tblNumericCovert.number2, [number1]/[number2] AS testDiv FROM tblNumericCovert
where testDiv needs to spit out results like this ###.##
I am totally lost, if anyone can help, I would appreciate it.
I have an SQL search that is converting two values to type money. I want it to show two digits after the decimal point but am getting inconsistent results. The first value is as follows:
tblInventoryItem.itemcost as originalcost (the column is datatype money)
This displays correctly i.e. 2000.00 or 150.70 etc
The second value is this:
tblInventoryItem.itemcost + tblUpgrades.ItemCost as totalcostincupgr (both columns are datatype money)
But this displays as 2000 or 150.7
How can I get the second value to show two decimal places even when the digits are zeros?
I have a field in a database which is a datatype Money. When I run a select query the data is coming back with 4 decimal places like 100.0000 but I only want 2 decimal places like 100.00.
Hello, The issue is to convert a number like 114270 to 114 + (270/320) = 114.84375. The decimal version is the desired result. In other words the last 3 digits of 114270 are in base 320.
Question: I cast my number as char() then use substring() to get the digits. Then I cast back to float and divide by 320. I get 0 when I do this and I'm not sure why.
Code follows: declare @p float set @p = (select top 1 P from [tablename] where Product = 'Z')
declare @pchar as char(6) set @pchar = cast(@price as char(6)) declare @first3 as char(3) declare @second3 as char(3) set @first3 = cast(substring(@pchar, 1,3) as float(25)) set @second3 = cast(substring(@pchar, 4,6) as float(25)) select @pchar -- 114270 select @first3 -- 114 select @second3/320 -- 0
I have tried changing cast(substring(@pchar, 4,6) as float(25)) to cast(substring(@pchar, 4,6) as decimal) and I get the same result. Any suggestions or is there a better way altogether?
I need to format a field to a set number of decimal places which is held as a field within the DB, and also format it as a standard number.
I have been able to set the format of the field to the decimal places field and it all works (the quantity in the row has the right number of decimal places), but i cant now format it as a number...
Heres an example:
The quantity is 1500 with 3 decimal places so it becomes 1500.000
I need to format it as a number with comma's etc such as 1,500.000
How would i go about doing this? Ive already used the format option for the decimal places and dont know how to add another format type (which in this case will be N)
I am creating a website. where the data to be stored in my server. I had my webspace from some service provider. How can i connect the website and thee database. --> website is deployed at webspace given by the provider. --> database is at my server( located in our office).
****************How can i connect these website and database.*******************
I am working with an off the shelf app that controls decimal places through the application. All columns underlying the apps 'numeric' fields are floats. We are writing some scripts that need to compare numbers and some of them look like the second row
I have two fields - both defined as money. When I divide them, SQL Server truncates the result after the 4th decimal point. So SQL Server says: 370.45 / 3,391,517.85 = 0.0001 I want to achieve: 370.45 / 3,391,517.85 = 0.00010922837... etc. The field the result is going into is defined as decimal(20,18)
I've tried using "cast(1stmoneyfield as decimal(20,18)) / cast(2ndmoneyfield as decimal(20,18)) as dividednumber", but SQL Server reports back errors about null values and Arithmetic overflow and terminates.
I'm at a loss as to how to solve the problem. Any suggestions please?
