MS SQL Server :: How To Multiply Value In A Field

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

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 Two Value From Two Fields

Apr 11, 2006

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

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

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 :: 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 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

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

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

Transact SQL :: Return Field When A Field Contains Text From Another Field

Aug 25, 2015

I'm new to SQL and I'm trying to write a statement to satisfy the following:

If [Field1] contains text from [Field2] then return [Field3] as [Field4].

I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.

View 8 Replies View Related

How To Add Date Field And Time Field (not Datetime Field )

May 4, 2006

Good morning...

I begin with SQL, I would like to add a field that will be date like 21/01/2000.

Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.

How to do for having date and time in two different field.

Sorry for my english....

Cordially

A newbie

View 3 Replies View Related

Problem Importing Data From An Access Memo Field Into A SQL Server Ntext Field.

Jul 11, 2005

I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!

View 4 Replies View Related

Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert

Dec 30, 2003

I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please help

View 6 Replies View Related

How To Convert Datetime Field To A Date Field So Excel Recognize It As Data Type

May 17, 2015

I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .

How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?

I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)

**all of the above returned text objectes in excel and not date objects.

View 3 Replies View Related

Informix Date Type Field To SQL Server Datetime Field Error

Oct 17, 2007

I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part

I tried a couple of things:

Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.

Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.

What options do I have that will work?

View 1 Replies View Related

Odbc - Binding Sql Server Binary Field To A Wide Char Field Only Returns 1/2 The Daat

Jul 23, 2005

Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn

View 2 Replies View Related

Converting Datetimeoffset Field To Datetime Field / Why Milliseconds Value Is Incorrect

Nov 17, 2012

DECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________

The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect

View 2 Replies View Related

Function To Return Remaining Of Field After It Finds A Character In The Field.

Feb 20, 2007

Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen

View 1 Replies View Related

Transact SQL :: NVARCHAR Field Getting Truncated After Increasing Field Length?

Aug 28, 2015

I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.

It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:

Inserted Value -> Result in table

123456-8 -> 123456-8      *Correct
1234567-9 -> 1234567-     *Wrong
123456789 -> 123456789    *Correct
1234567#9 -> 1234567#     *Wrong
1234567a9 -> 1234567a9    *Correct

Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?

I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.

View 3 Replies View Related

How To Matrix: Conditional Data-field Bg-color When Field Is Subtotal

Mar 25, 2008

How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???

View 3 Replies View Related

How To Define Field Attribute For A Numeric Field In SQL Table?

Jan 19, 2005

I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?

View 12 Replies View Related

Access Memo Field To SQL Server Text Field

Nov 19, 2006

Hi,

I'm importing an Access database to SQL Server 2000.
The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).

I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.

Is this some sort of an encoding problem that arose during database import?
I would appreciate any pointers.

View 14 Replies View Related

SQL Server 2008 :: Selecting Field That Contains Data From Another Field

May 28, 2015

We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.

ie.
Code Description Brand
ABC1 BLANK DVD SONY
ABC2 SONY BLANK DVD SONY

what I need to do is identify where the Brand is in the Description field ...

I have tried ;

select * from Table
where Description Like Brand

not very successful.

View 3 Replies View Related