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