T-SQL (SS2K8) :: Get Script To Output Only 2 Decimal Places For Amount Column From Select Statement?
Jul 22, 2015
I have a routine that generates an HTML email and sends it just fine, but one of the columns ends up with 4 decimal places for a column datatype of money. How can I get the script to output only 2 decimal places for the amount column from the select statement?
Here's the script:
declare @tableHTML nvarchar(max) ;
set @tableHTML =
N'<h1>Revenue Report</h1>' +
N'<table border="1">' +
N'<tr><th>Amount</th><th>Index</th><th>CompObj</th><th>Rev Type</th><th>Program</th>'+
CAST ((SELECT td=SUM(dbo.tblAllocations.Amount),'',
[code]....
View 2 Replies
ADVERTISEMENT
Apr 2, 2015
I need to find the ones ending in a decimal. So if you look at the list below, I need a script that will return the value 001. ,002., 004. ..
001
001.
002.
003
004
004.
View 5 Replies
View Related
Jul 3, 2014
I have a table with three columns: UniqID, Latitude, and Longitude.
I need to write a query to identify when the latitude has more than 6 decimal places past the decimal. Same with Longitude. Values in these attributes can be a negative number. These fields are FLOAT.
View 7 Replies
View Related
May 15, 2008
I want to replace the contents of a value column with itself but rounded to 2 decimal places.
The current column is a double and I have tried to perform this using the following expression but it fails to work.
Code Snippet
Round(cc_vl,2)
How should I achieve this?
View 7 Replies
View Related
Feb 22, 2005
I'm executing the following...
select COL1, min(COL2) from TABLE group by COL1
the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.
I was hoping a simple
"delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"
would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?
this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.
tia
a
View 2 Replies
View Related
Apr 1, 2013
Along with the data that I am pulling back from query, I also need to provide an ImportID column with the following requirements:
YYMMDDGP0001, YYMMDDGP0002, YYMMDDGP0003, and so on. The 0001, 0002, and 0003 part could get big into the hundreds of thousands.
I have the YYMMDDGP part down with the following expression:
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) + 'GP' AS [ImportID]
Now I need to get the Auto Incrementing number part of this. I have been researching this trying SELECT INTO a temp table using the Identity Function and declaring different variables all with no luck.
View 7 Replies
View Related
Sep 5, 2014
I have a temp table that hosts data from a MDX query, I want to select a column from that table by passing in a parameter and then insert to a new table.
Here's the table structure, let's call it #temp
[Product].[Product Hierarchy].[level 03].[Member_CAPTION], [Measures].[Gross Sales]
Bike, 200
Accessory , 100
I have a table in the DB created as [ProductSales]
Here's what works for me:
insert into [ProductSales](Product, Sales)
Select convert(varchar(20), "[Product].[Product Hierarchy].[level 03].[Member_CAPTION]") as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'
From #temp
But in reality the product level is not always on level 03, I want to define a parameter @Product and pass it to the select list.
Here's what I tried to do:
Declare @Product varchar(500) = convert(varchar(20), "[Product].[Product Hierarchy].[level 01].[Member_CAPTION]")
Declare @SQL varchar(MAX)
SET @SQL='insert into [ProductSales](Product, Sales)
Select '+@Product+' as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'
From #temp'
Exec @SQL
But it threw me error, what is the right way to do it, is it because the double quote or single quote thing?
View 1 Replies
View Related
Apr 27, 2008
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:
rowtype| ID | value
A | 123 | alpha
B | 123 | beta
C | 123 | delta
A | 124 | some val
B | 124 | some val 2
C | 124 | some val 3
etc...
where for each ID, I have 3 rows that are associated with it and with different corresponding values.
I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.
i.e. each ID iteration will do something like:
insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha')
insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta')
insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta')
etc..
After my loop, I will just do a select * from #someTempTable
Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
View 2 Replies
View Related
Jan 7, 2015
I am trying to figure out how to pivot a temporary table. I have a table which starts with a date but the number of columns and columns names will vary but will be type INT (Data, col2,col3,col4………….n)
So it could look like
Date , TS-Sales, Budget , Toms sales
01-Jan-14,100,120,300
02-Jan-14,80,150,300
03-Jan-14,100,20,180
Turned to this
01-jan-14, 02-jan-14, 03-jan-14
100,80,100
120,150,20
300,300,180
Or even just the date and a SUM
What I want is to be able to sum al the columns but without knowing the name and the amount columns to start with this is a manually processes. How could I automate this?
View 2 Replies
View Related
Jan 17, 2007
I need to convert a date like 08/1/2009 to 0809
I also need to show currency as 100.00 and not 100.0000
How can I do these in a select statement?
SELECT CONVERT(Varchar(20),ExpirationDate,10) AS ExpirationDate, Amount FROM tblPayment
I appreciate any help!
View 5 Replies
View Related
Feb 2, 2015
I have a table with fields:
Id int
Pay numeric(19,3)
value records:
id pay
1 1.000
2 2.250
3 3.445
4 6.000
I want select of table to form:
id pay
1 1
2 2.25
3 3.445
4 6
if value decimal pay field Greater of zero then
value select= value field
else
delete value decimal and show
View 3 Replies
View Related
May 31, 2007
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>");
}
Any Help appreciated...
Doug
View 1 Replies
View Related
Nov 30, 2006
is there a way with a float datatype to choose the number of deicmal places to use
View 2 Replies
View Related
Mar 5, 2008
How do i get these decimal working?
select
cast(2/5 as decimal(5,2))
,2/5
,2*5
,cast(5/2 as decimal (4,2))
thank you
View 6 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
Apr 30, 2008
I am running the following query and getting result 150.76000 but I am desired 150.76. How can i get it.
SELECT ROUND(150.75600,2)
Results:
--150.76000
But I need the following result
150.76
View 2 Replies
View Related
Apr 3, 2007
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
View 2 Replies
View Related
Aug 12, 2000
Hello all...
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?
All help much appreciated!
View 2 Replies
View Related
Aug 24, 2005
In MS SQL Server 2000 T-SQL, how can I always display 2 decimal places?
e.g.
I wish to display
25 as 25.00
or
43.6789 as 43.68.
Any help would be greatly appeciated.
View 2 Replies
View Related
Jun 28, 2004
Hi,
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 :(
Many thanks in advance for your help.
View 4 Replies
View Related
Mar 21, 2007
Hi,
I'm running the following query against a SQL Server 2003 database to receive the results below:
SELECT PayPeriod AS [Pay Period], SUM(PayHours) AS [Pay Hours]
FROM EmployeePayHours
GROUP BY PayPeriod
Pay Period Pay Hours713 80
714 120
717 59.5
718 80
719 80
A colleague of mine, however, is running the same query against the same database (using a different machine) and gets the following results.
Pay Period Pay Hours
713 80
714 120
717 59.500000000000021
718 79.999999999999972
719 79.999999999999972
Is there a setting somewhere that needs to be changed? Thanks.
View 1 Replies
View Related
Nov 28, 2015
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,
[Code] ....
View 3 Replies
View Related
Feb 26, 2007
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.
View 1 Replies
View Related
Sep 6, 2007
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.
View 4 Replies
View Related
May 25, 2007
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?
View 16 Replies
View Related
May 28, 2008
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
View 3 Replies
View Related
Mar 1, 2001
What is the best way to force a 2 digit decimal place without rounding?
For example select price*UOM returns
47.1294
3.255
.5
8
.49
What i want to be returned is
47.12
3.25
8.00
.50
.49
Thanks,
Jim
View 3 Replies
View Related
Mar 3, 2005
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.
Any help appreciated.
Cori
Never mind, I found it muhself....
FORMAT(AVG(fin),2) works perfectly!!
View 5 Replies
View Related
Oct 26, 2004
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?
OLD---------------------------NEW
15.1456 ================ 15.15
4.1328 ================== 4.13
5.16 =================== 5.16
View 7 Replies
View Related
Jan 14, 2004
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.
View 5 Replies
View Related
Apr 1, 2004
SELECT 1/2 returns 0 in transact sql instead of 0.500000
How do I get it to return 0.5000 ?
View 7 Replies
View Related
May 16, 2008
Hi All,
I have a small question to ask. How to round a numeric field upto 2 decimal places, and also show it with 2 decimal places only
For example the following would return 255.88000000000
select round(255.87908765444,2)
How to get 255.88 only?
Pleas help.
Thanks,
Zee
View 3 Replies
View Related
Sep 6, 2007
Hi
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?
Thanks!
View 3 Replies
View Related