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

Transact SQL :: How To Multiply Against A Negative Value

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

Transact SQL :: Multiply Multiple Columns From One Row

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

Sum() Multiply()

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

Multiply

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

How To Multiply Value In A Field

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

How To Multiply Two Value From Two Fields

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

Multiply Fields

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

How To Multiply Value In A Field By -1

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

Multiply Fields

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

Multiply Rows With T - SQL

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

Multiply Numbers In A Column

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

Get Group And Multiply Records

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

How Can I Multiply Values Within A Dataset?

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

SUM Multiply Based On Column Entry

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

Multiply Of Columns Based On New Group

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

Analysis :: DAX Row Level Multiply First Then Sum Up In Total

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

SQL Server 2012 :: Using COALESCE And Also Trying To Multiply And Divide

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

Creating A Query That Access Multiply Databases

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

Bcp And Decimals

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

Help With Decimals

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

Help With Decimals

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

Decimals In SQL Server

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

Trimming Decimals

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

Decimals In View

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

No Decimals In Query

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

DECIMALS IN QUERY

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

Truncated Decimals

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

Decimals With Sum Agregate Function

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

Updateing DECIMALs With DB-Library

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

How To Add Trailing Zeros 2 Decimals

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

How To Add Trailing Zeros If There Is No Decimals

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

Doubt In Truncating Decimals..

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







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