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
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?
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-
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-
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)/¯ ~~~
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!
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!
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
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
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] .....
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
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
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 RelatedSELECT 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.
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] ....
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...
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?
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.
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?
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies View RelatedI am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View 1 Replies View RelatedPardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
regards.
Julio Diaz.
What is the command to see how many lines were returned with a Select statement?
View 1 Replies View Relatedwhat is the max number of fields allowed in a table in sql server 7.0?
thanks
Hi!
I used MS Access database! I need to know what are the replications of MS Access fields in MS SQL Server?!
1) What is TEXT field of MS Access in MS SQL Server?!
2) What is MEMO field of MS Access in MS SQL Server?!
3) How to set AutoNumber in MS SQL Server?!
I get confused because my online web based sql admin of my host had many options like varchar...
3) Please tell me the size of the fields too as i will be storing a large amount of data!
4) In MS Access there was field size like...60...! But in MS SQL Server there was 16 written next to the TEXT field! What is it? some Bytes?!
Thanks!
Hi,
I'm very rusty on SQL, if someone could help I'd appreciate it! It's probably very easy to solve!
Ok, I have a table called results which contains results of a survey where users rated features on a scale of 1 to 7. For example I have:
results(id, feature1, feature2, feature3, feature4, feature5)
sample data
1, 5, 4, 3, 2, 6
So, I want to create a view which will summarise total points given i.e. sum of feature1 -feature5. How do I do this? Do I need a function or is there a built-in function? All I can find is info on aggregate functions... but I want it per row...
Thanks,
Lilalfyalien
Edit: the question is more clear in http://www.dbforums.com/showthread.php?p=4477661&posted=1#post4477661
OK this question is not really SQL server related but since we will build the database in MS SQL Server (2000) i think this is the proper forum to ask this question.
It's actually a quite simple question i gues, but my experience with databases is very limited (buiding my first serious database actually).
when i have two tables Organization and Samples coming from this organization with the following fields:
Organization:
InstituteID (identification)
Name
Adress
...
Sample:
SampleID (sample identification)
InstituteID (references to the Institute table)
SampleDate
Location
...
In general is it better to use the sampleID and InstituteID combined as primary key in the second (sample) table (and build an index on the combined key) or to generate a new unique key for each record? And why should i choose either one of these options?
One advantage of the first option is you dont have to generate keys yourself (or use some autonumbering system), however the disadvantage is the key grows bigger and bigger when adding more tables (fraction taken from samples, analysis performed on fractions, Analysis giving results).