Transact SQL :: Two Decimals Multiply Each Other Become Zero
May 18, 2015
Script as follows :
select cast( 0.0050000000 as decimal(38,23)) * cast(0.0000010000 as decimal(38,23))
select cast( 0.0050000000 as decimal(28,23)) * cast(0.0000010000 as decimal(28,23))
select cast( 0.01 as decimal(28,15)) * cast(0.0000000001 as decimal(28,15))
select cast( 0.01 as decimal(28,16)) * cast(0.0000000001 as decimal(28,16))
The result was following:
0.0000000--was zero
0.000000005000000000000000000
0.00000000000--was zero
0.0000000000010
View 3 Replies
ADVERTISEMENT
Jun 11, 2015
what am I doing wrong here? I can multipley * 1.25, but not -1.25. Every google entry I find is how to convert positive to negative numbers... I want to use update to multiply and lower the price by 25% (I used the function a bit to liberally to multiply by 125% and now want to bring the values down to earth)
update production.product set listprice = (listprice * -1.25)
select max(listprice) from production.product
View 8 Replies
View Related
Nov 23, 2015
I have a table with the following format
I am looking for a way to get the PRODUCT of all columns and group by M_DOMA, [FROM] - Basically multiply all columns on the row that are not M_DOMA or [FROM].
Is this possible using T-SQL 2012?
View 5 Replies
View Related
Mar 15, 2007
Hi all. I have 2 tables to join with.
table1 - leavecredits
empdcno type dateassigned earnedDay availedDay
222 SL 3/4/2007 8.0 2.0
222 PL 4/4/2007 8.0 2.0
222 ML 5/4/2007 8.0 0.0
333 PL 6/4/2007 8.0 3.0
444 SL 4/6/2007 8.0 1.0
table2 - leaveledger
empdcno leavedate hrsleave hrswork type
222 6/4/2007 2.0 8.0 SL
222 6/5/2007 8.0 8.0 SL
222 6/6/2007 8.0 8.0 SL
222 6/7/2007 8.0 8.0 SL
222 6/8/2007 8.0 8.0 Sl
222 6/9/2007 8.0 8.0 SL
333 6/7/2007 8.0 8.0 PL
I am creating a report for leaveledger of all employees.
Expected Result:
empdcno type dateassigned earnedDay earnedHrs availedDay availedHrs balDay balHours
222 SL 3/4/2007 8.0 42.0 2.0 2.0 16.0 6.0 48.0
333 PL 6/4/2007 8.0 8.0 1.0 3.0 24.0 5.0 40.0
formula:
earnedHrs = in leaveleadger table 8 x 5 + 2 = 42.0
availedHrs = availedDay x hrswork
balday = earnedDay - availedDay
balHours = (earnedDay - availedDay) x hrswork
thanks
-Ron-
View 3 Replies
View Related
May 15, 2007
hi all
i want to multiply each row, does anyone has more simple solution other than using cursor???
-- Prepare sample data
DECLARE@tbl1 TABLE (num1 float )
INSERT@tbl1
SELECT'1' UNION ALL
SELECT'2' UNION ALL
SELECT'3'
select * from @tbl1
declare @no1 as float, @total as float
set @total=1
DECLARE c1 CURSOR FOR
select * from @tbl1
OPEN c1
FETCH NEXT FROM c1
INTO @no1
WHILE @@FETCH_STATUS = 0
BEGIN
set @total=@total*@no1
FETCH NEXT FROM c1
INTO @no1
END
CLOSE c1
DEALLOCATE c1
select @total AS SubMultiply
~~~Focus on problem, not solution ¯(º_o)/¯ ~~~
View 12 Replies
View Related
Nov 1, 2007
Hello, I have a quick question. I'm currently using MS SQL 2005.
I have a main table which has many fields. I created a view#1 which imports all the rows
from the main table but I only select the "City" field, "PurchaseDescription" field and a
"Cost" field. Only the city name is unique. There are a total of 50 different city names,
and each name can have more than one "PurchaseDescription" each with an associated "Cost". I
would like to create a second view#2 with an added field called "Cost2". I want "Cost2" to
contain the same value as the "Cost" value. However if the "PurchaseDescription" equals to
"USB" then "Cost2" should be assigned the value of "Cost" multiplied by -1. If the
"PurchaseDescription" content isn't equal to "USB" then "Cost2" will have the save value as
"Cost".
For example,
The view#1 will have the following rows & fields (I had to pad the field with dots just to
make the output look viewable on this thread)
City..............PurchaseDescription.........Cost
--------------------------------------------
LA.................desk........................... ..4.5
LA.................USB............................ ..5.0
LA.................USB............................ ..6.0
SD................chair........................... ...4.0
SD................door............................ ..10.0
The view#2 should have the following rows and fields
City..............PurchaseDescription.........Cost .......Cost2
---------------------------------------------------------
LA.................desk........................... ..4.5....... 4.5
LA.................USB............................ ..5.0....... -5.0
LA.................USB............................ ..6.0....... -6.0
SD................chair........................... ...4.0....... 4.0
SD................door............................ ..10.0....... 10.0
I don't mind if I have to use functions or more than 2 views to solve my problem. I jus need
a final view that would look like view#2.
Could you please help if you can?
Thank you very much for your time and have a safe Halloween!
View 3 Replies
View Related
Apr 11, 2006
I have the following fields in table A:
GL_ID|GL_Name_VC | Amount |Period_TI|Year_SI
===================================================
1000| Inventory| 8,000.00 | 01 | 2005
===================================================
1000| Inventory| -3,000.00 | 02 | 2005
===================================================
1000| Inventory| 5,000.00 | 02 | 2005
===================================================
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
GL_ID|GL_Name_VC |Period_01|Period_02 |Total_YTD
================================================
1000 | Inventory | 8,000 | 2,000 |10,000
Percentage| 10% | 20% | 0
================================================
Total | 800 | 400 | 1,200
The Total row is calculated by multiplying the percentage row by the Inventory amount in
each Period.
Guys, hope someone out there can help me with the sql command for the above report?
View 1 Replies
View Related
Mar 12, 2007
Hi. Just a question. Did I use it proper to multiply fields and assign to a variable?
select l.* , e.paytype, e.sectioncode, e.empno,dc.fullname,
earnedDays = lc.earned, availedDays = lc.availed,
earnedHours = (lc.earned * e.hrswork), availedHours = (lc.availed * hrswork),
balHours = (earnedHours - availedHours), balDays = (earnedDays - availedDays)
from hrempleaveledger as l
inner join......
inner join......
.
.
.
I can't surf through the net because i have no permission from the administrator.
Thanks.
-Ron-
View 6 Replies
View Related
Nov 1, 2007
Hello, I have a quick question. I'm currently using MS SQL 2005.
I have a main table which has many fields. I created a view#1 which imports all the rows from the main table but I only select the "City" field, "PurchaseDescription" field and a "Cost" field. Only the city name is unique. There are a total of 50 different city names, and each name can have more than one "PurchaseDescription" each with an associated "Cost". I would like to create a second view#2 with an added field called "Cost2". I want "Cost2" to contain the same value as the "Cost" value. However if the "PurchaseDescription" equals to "USB" then "Cost2" should be assigned the value of "Cost" multiplied by -1. If the "PurchaseDescription" content isn't equal to "USB" then "Cost2" will have the save value as "Cost".
For example,
The view#1 will have the following rows & fields (I had to pad the field with dots just to make the output look viewable on this thread)
City..............PurchaseDescription..........Cost
-----------------------------------------------------
LA.................desk.............................4.5
LA.................USB..............................5.0
LA.................USB..............................6.0
SD................chair.............................4.0
SD................door.............................10.0
The view#2 should have the following rows and fields
City..............PurchaseDescription........Cost.......Cost2
---------------------------------------------------------
LA.................desk..........................4.5........ 4.5
LA.................USB...........................5.0....... -5.0
LA.................USB...........................6.0....... -6.0
SD................chair..........................4.0........ 4.0
SD................door..........................10.0....... 10.0
I don't mind if I have to use functions or more than 2 views to solve my problem. I jus need a final view that would look like view#2.
Could you please help if you can?
Thank you very much for your time and have a safe Halloween!
View 9 Replies
View Related
Jan 31, 2008
In derived fields, I need to multiply numbers from 2 fields. Assume that I need to multiply numbers from field B and field C. Although, B is found by COUNT (field A). So, I do the following: B*AltName of A. It gives me an error. Thank you for any help!
MG
View 4 Replies
View Related
Apr 18, 2006
I need to multiply rows of a table with each other. I found a function like this
exp(sum(ln(floatfield1))).
But I can not find the ln function. Is there any other possibility ?
Thanks for any help.
Uwe
View 4 Replies
View Related
Mar 21, 2002
Hi
I'm trying to find a decent way of multiplying a set of numbers in a column without using a cursor in T-SQL.
There is no 'Product' aggregate function that I'm aware of in SQL 7 or 2000. The workaround I'm currently using is this :
SELECT EXP(SUM(LOG(ColumnName))) FROM tblName
This works fine, except when negative numbers are introduced. The LOG function does not allow negative numbers and therefore returns a domain error and the negative number is eliminated from the aggregate.
I could use a cursor to do the multiplication, however, this is proving too slow for the bulk calculations involved.
If anyone has any ideas or suggestions, then that would be much appreciated.
thanks....Tom
View 1 Replies
View Related
Dec 20, 2013
1.Create the tables with insert queries
2. provide the result as required in an temp table
3. Display the expected result
======================================================================
CREATE TABLE and Insert Data
======================================================================
use master
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Has_Nodes] [nchar](3) NULL
) ON [PRIMARY]
[Code] .....
View 14 Replies
View Related
Mar 6, 2008
I have this case where I need to multiply the value of my dataset and finally deduce one to it. For instance if I have a dataset with three values, 2, 5, 15, I need to end up with a value of 150 (2*5*15). How can I achieve this?
View 3 Replies
View Related
Sep 5, 2012
SELECT EmployeeId, PiecemealType,SUM(PiecemealQty) /100 AS TotalTrays, ActivityId
FROM SR.dbo.PayTrays
WHERE WorkDate > '2012-01-01'
AND WorkDate < '2012-12-31'
GROUP BY EmployeeId, PiecemealType, ActivityId
ORDER BY EmployeeId, PiecemealType
PiecemealType is the type of tray
1= 15 Items per tray
2= 12 Items per tray
3= 8 Items per tray
4= 6 Items per tray
I'm trying to work out a bonus for 1 cent per item worked. The divide by 100 moves the total from cent to dollar and the SUM is adding all weekly values together.I just need to work out how to multiply the the SUM value for trays by the 4 different PiecemealType values. If it was static i could just add *15 to my SUM.
View 2 Replies
View Related
Dec 19, 2013
Product IdIsPrimaryQuantity
P0011
P001.102
P001.204
P001.305
P0021
P002.106
P002.207
P002.309
P002.4010
P002.5011
Need the query for result each group shows multiplied value of group quantity and last row of the group is shown with NULL
Product IdSubProductQuantity
P001 40
P001 P001.3NULL
P002 41580
P002 P002.5NULL
This is same as [URL] ....
View 5 Replies
View Related
Jul 30, 2015
I've got measure a in one table and measure b in another table and calculation a*b.
a b calc
2 4 8
5 2 10
SUM 7 6 18
How to get 18 as total instead of 7*6=42. I'd like to get row level calculation to sum up for the total level...
View 3 Replies
View Related
Sep 3, 2014
Need to multiply the following by 12 then divide this by 52.
,COALESCE(CTAX.[NET-AMT],0)+COALESCE(HEAT.[NET-AMT],0)+COALESCE(FURNIT.[NET-AMT],0)+COALESCE(SERVCH.[NET-AMT],0)+COALESCE(SEWER.[NET-AMT],0)+COALESCE(WATER.[NET-AMT],0) as 'weekly rent'Everywhere
I try and put the *12/52, I get errors.
View 9 Replies
View Related
Dec 10, 2007
Dear Readers,
Is it possible to create a query that access multiple sql express databases? Or is it possible to link a table in one database from another?
View 1 Replies
View Related
Oct 25, 2000
I am trying to use bcp to load a text file into SQL Server 7. The decimal numbers in the text file are formatted as 123455 and when I load the values to SQL I want the last two digits to be the decimal 1234.55. My current process is loading the value as 123455.00. What do I need to do in the format file to get this to work???
View 2 Replies
View Related
Feb 22, 2004
Hi all,
This is what I have:
DECLARE @OnTime int
DECLARE @UnControlled int
DECLARE @Volume int
DECLARE @GrossEffect decimal(10,2)
DECLARE @NetEffect decimal(10,2)
DECLARE @WeekEndDate datetime
SET @OnTime = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE OnTimeFlag = 1 AND ARSScanType = 'D' AND ARSType='AN')
SET @UnControlled = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE ControlFlag = 'U' AND ARSScanType = 'D' AND ARSType='AN')
SET @Volume = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE ARSScanType = 'D' AND ARSType='AN')
SET @GrossEffect = (@OnTime/@Volume * 100)
SET @NetEffect = ((@OnTime + @UnControlled)/@Volume * 100)
SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblEDITempARS)
INSERT INTO tblSummaryData2(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate)
VALUES ('AN',
@Volume,
@NetEffect,
@GrossEffect,
@WeekEndDate)
My numbers are:
OnTime=8089
Uncontrolled=6
Volume=8095
The GrossEffect comes as 0 and the NetEffect=100
AN809501002/21/2004
Why is my GrossEffect = 0??? it should be 99.9
View 1 Replies
View Related
Feb 19, 2008
Hello, I have a table with a field with the format of 8 decimals. When I make a select to that table instead is given me (for example) 25,00 it gaves me 25,00000000. How can I fix the with the correct format? Thanks in advance.
View 2 Replies
View Related
Jul 7, 2005
I need to display decimal results from SQL Server queries, but the decimals keep getting truncated. For example, 9 divided by 4, keeps getting truncated to 2. Does anyone know how to display the whole result, including the decimals?
View 2 Replies
View Related
Aug 17, 2005
I have a field in my SQL table that is defined Decimal(10,8), which
provides me with the ability to have up to 8 digits after the decimal
place, however, if I store a value such as 3.14, it is stored as
3.14000000. This is fine in the database, but it is the same when it's
returned. I have tried using Convert.ToDecimal on my returned row but
it doesn't work. The value at runtime appears as 3.14D in the locals
window but displays with all the insignifcant digits.
Any ideas what's happening or if there is an easy way to trim the trailing zeros?
View 2 Replies
View Related
Aug 24, 2004
Hello,
I want a view to always present my numeric fields with 2 decimals. In my table I have the following values in field "amount" (numeric(18,2))
181.25
176.5
170
I want the view to show
181.25
176.50
170.00
I have tried Cast but that doesn't seem to do the job.
Help!
Rolf
View 1 Replies
View Related
Jun 24, 2008
in quantity column - i have
100.00000
but i want only 100
no decimals..
how to get that?
thanks
View 2 Replies
View Related
May 31, 2007
I have two fields whose format in the table is decimal (14,5).
I now have to divide one field by the other, and I want the result
to be in the format decimal (21,16).
Whatever I do I'm not able to get this result with sixteen decimals.
I tried with dec(Field1,21,16)/Field2 but it yields six decimals.
I tried with dec(Field1,21,16)/Field2 but it yields an integer.
I tried other ways but I always get a result which is made of maximum
six decimals.
What can I do?
Thank you.
Anna - Verona (Italy)
View 5 Replies
View Related
Jan 16, 2006
I am relatively new to SQL server. I am tring to send some decimalvalues to the database using a stored procedure with parameters of typeDECIMAL. Every time it inserts the values into the database thedecimals are truncated. I saw on the MSDN library that you have to setthe precision and scale values b/f you run the stored procedure. So Iset the precision to 8 and the scale to 4 and it still didn't help. Cananyone help me?
View 6 Replies
View Related
Feb 23, 2001
I'm using the sum() agregate function inside a select with a decimal field with an scale of 2.
I want to get the result always with 2 decimals, but if the result is 100.10 I get 100.1 . I've tried
to convert and cast the result but I can't find how to get 2 decimals. Please help. Thanks
View 1 Replies
View Related
Aug 18, 2004
Hello,
I have encountered a problem doing an update on a Field defined as DECIMAL with dbcursor().
I have opened a cursor with dbcursoropen() with option LOCK_CC set. Then I have bound some char variables to CHAR Fields with STRINGBIND and some double Variables to the DECIMAL fields with FLT8BIND.
When I read the cursor, the data is correct in the bound variables. But when I do an update, all data for the numeric fields are gone and the database contains only NULL Values. The Strings are updated correctly.
I have tried to set the length, I am using the poutlen-variables with a value other than 0, but nothing helps.
Does anyone have an idea about what can I do?
Thank You in advance.
Frank
View 3 Replies
View Related
May 18, 2008
In my select query for field ordernumber want to add two trailing zeros in the resultset, how can i add.
Thanks for the info.
View 1 Replies
View Related
May 19, 2008
How can i add two trailing decimals if there is no decimals:
for example if it is just 1, then make it 1.00
if it is 1.12 then leave it as it is.
can you please help.
select cast(ordernumber as varchar(10)) + '00' from ordertable
Thanks for the info.
View 1 Replies
View Related
May 22, 2008
Hello
I need a help ..
I need to convert 45.4593251 to 45.46
How to achieve it.
Can any one help me please..
Thanks
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
View 3 Replies
View Related