SQL Syntax To Sum Fields From Two SQL Statements
Mar 8, 2006
I have two SQL statements that return sum(pcs) and sum(amt) from two differnt conditions. How do I get a total sum of pcs and amt from these two SQL went I union them?
SQL1: return columns value, SUM(pcs), SUM(amt) where amt is value*pcs
$2 , 4pcs, $8
$5, 5pcs, $25
SQL2: return similar columns value, SUM(pcs), SUM(amt) FROM another condition giving:
$2, 3pcs, $6
$5, 1pc, $5
If I use (SQL1 union SQL 2), I get
$2 , 4pcs, $8
$2, 3pcs, $6
$5, 5pcs, $25
$5, 1pc, $5
I want to get the final result as:
$2, 7pcs, $14
$5, 6pcs, $30
How to write the SQL statement to get the result? OR it is not possible at all?
Vincent
View 4 Replies
ADVERTISEMENT
Aug 29, 2013
For large databases is it a good idea to create indexes for fields that are used in Where statements? Does that improve performance and reduce overhead?
View 4 Replies
View Related
Feb 19, 2014
How do I properly write a CASE statement for the following business logic:
I want three of the following fields to be true in order to return 'Y'.
For example:
If field name 'Accepted' = 1 AND field 'StepNo' = '1' and field 'Visit' = 'V1'
Otherwise, I want it to return 'N'.
I have tried the following code below and it is not working.
, CASE WHEN Accepted = '1' AND StepNo ='1' AND Visit ='V1'
THEN 'Y' ELSE 'N' END AS 'StatusQ (Col N)'
View 2 Replies
View Related
Oct 21, 2005
Hi! Good Day!
What is the syntax to format the datefield? The value of my datefield is like this:
10/13/2005 5:15:02 PM
What is the select query to filter the date only. My desired result should be:
10/13/2005
only.
Thanks :)
View 5 Replies
View Related
Feb 20, 2015
I'm looking for the correct syntax to pull back duplicate vendors based on 6 fields from two different tables. I want to actually see the duplicate vendor information (not just a count). I am able to pull this for one of the tables, something like below:
select *
from VendTable1 a
join ( select firstname, lastname
from VendTable1
group by firstname, lastname
having count(*) > 1 ) b
on a.firstname = b.firstname
and a.lastname = b.lastname
I'm running into issues when trying to add the other table with the 4 other fields.
View 5 Replies
View Related
Jan 28, 2008
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
View 3 Replies
View Related
Feb 20, 2008
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.
View 8 Replies
View Related
Jan 26, 2008
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
View 1 Replies
View Related
Jul 23, 2005
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 Related
May 20, 2008
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
View 7 Replies
View Related
Jul 2, 2015
I 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
View 3 Replies
View Related
Dec 14, 2003
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
View 3 Replies
View Related
Jul 20, 2005
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 Related
Mar 31, 2008
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
View 4 Replies
View Related
Sep 23, 2007
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
View 5 Replies
View Related
Jun 30, 2000
Pardon 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.
View 2 Replies
View Related
Jul 31, 2013
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.
View 5 Replies
View Related
Jan 21, 2008
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.
View 1 Replies
View Related
May 27, 2008
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
End Sub
View 2 Replies
View Related
Aug 17, 2006
Hi guys,
Need some help in some query processing...
Here goes,this is the results i have obtained so far
|id |cat | name |
1 .....a... apple
1 .....b .. banana
2 .....a ...austria
2 .....b ...brazil
2 .....c ...china
3 .....a ...abraham
3 .....c ...clinton
Column cat can have at most 3 different types of values..{a,b,c}
I wanna write a query such that the result comes out as such
<(....id ......a .......b......... c..) corresponding to these fields.>
|Col 1| Col 2 | Col 3 | Col 4|
....1 ...apple ...banana
....2 ...austria .brazil... china
....3 ...abraham ..........clinton
Anyone can help...thanks guys
View 1 Replies
View Related
Sep 21, 2006
I use SQL Server 2005.I want to create a SP "Search". With this function a user can serach the member database on several criteria: age minimal, age maximal, name, city..BUT these criteria do not necessarily have to be defined by the user, so it might be that a user searches for all members whose age is between ageMin and ageMax leaving the name and city criteria empty.So in my SP I have to check whether these values are empty or not. If a parameter is not empty I have to add it to my selection query.. BUT, I know that SQL Server makes an execution plan, and understood that when you use if-statements the procedure needs to be recompiled every time?Is this true? If so:how does this work then?Is there another way for me to still be able to create this SP?
View 2 Replies
View Related
Dec 11, 2006
Hello guys. How is this declared in SQL?
Select * FROM my_table
if my_column = "1" UPDATE my_table SET my_other_column= "a" ////my_column and my_other_column belong to my_table
else if my_column="2" UPDATE my_table SET my_other_column= "b" ////my_column and my_other_column belong to my_table
View 4 Replies
View Related
Jan 27, 2008
Hi, I would like to know the number of SQL statements per second being generated by my web site. How can I know this? Best Regards,MeeNge
View 6 Replies
View Related
Apr 8, 2008
Hello I have an SQL Data Source i want to compare 2 dates if one is equal to or less than the other i want to return either a string or a true i have been trying combinations for about an hour and it's getting a little frustrating i;ve tried searching for an example but cant find one so somesthing like Select ValuesWhere Date1 <= Date2 Return "String"(orTrue?) also IF date1 <= Today() Return "String OR True" thanks Chris
View 3 Replies
View Related
Feb 22, 2006
I have some trade data. One colum is tran_status_mtf. within that column is "settled/traded", "cancelled", and "revised". I want to write and SQL statement that says if the trade is "settled" or "traded" display "A" in my output. How do I do this? I am new to SQL. Thanks in advance guys!!!
View 14 Replies
View Related
Jun 3, 2008
I need to create a brief statement in Design View for SQL 2000.
I successfully wrote "isnull(dbo.Data.Parcel,'')in the Column in Design View, which would ensure that a null value would be listed as a blank. Now I need to have dbo.data.status show as 'C&G' in any instance of data, otherwise it will be listed as blank. I tried writing "isnotnull(db0.data.status,'Homestead', '')", but it does not work.
Is there a simple statement I can write in "column" of design view that can get the results that I need?
-Steve
View 4 Replies
View Related
May 21, 2007
I have written an sql statement thats using sum. The problem that I have is that when the value is zero it returns Null. Is there a way that I could return zero instead of null.
View 5 Replies
View Related
Oct 19, 2007
I am totally new to creating IF statements inside stored procedures. I am passing some parameters from a ColdFusion form to a SP and if the form is empty for the small date I get an error. I would like to be able to check in the procedure to see if the parameter is empty and if it is set a default value for it. Here is my code.
@JOB1,
@COLLEGEDATE smalldatetime,
If(@COLLEGEDATE <> ''")
@COLLEGEDATE smalldatetime,
ELSE
@COLLEGEDATE = getDate(),
RETURN
@JOB2
View 3 Replies
View Related
Sep 16, 2006
Hi, i just want to know if the T-SQL statements in SQL server 2005 are different from SQL Server 2000???
If they are different, where can i find the Server 2005 T-SQL Statements?
View 6 Replies
View Related
Apr 16, 2007
Hello,
I have a report with 1 field that I need to sum into 2 different textboxes based on another field's value.
Specifically, Is it simply doing something like this below?
=Iif(Fields!iBB.Value=1, Sum(Fields!Billed.Value)
=Iif(Fields!iBB.Value=0, Sum(Fields!UnBilled.Value)
Which will not get past intellisense checking, -or- do I have to do something different here?
Thanks in advance.
View 10 Replies
View Related
Dec 15, 2006
Hi guys! I have these commands that insert into two tables, if condition 1 is met, it will insert into the first table, if the second condition is met, it will insert into the second table.
Is there a way for the insert statements to be merged so that I won't be executing two statements?
Dim update_phase_before As New SqlCommand("INSERT INTO TE_shounin_todokesho_jizen (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='before'", cnn)
Dim update_phase_after As New SqlCommand("INSERT INTO TE_shounin_todokesho_jigo (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='after'", cnn)
Thanks.
View 1 Replies
View Related
Jan 16, 2007
Hello, how can i merge together several select statements?
I have something like this:
CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data
View 5 Replies
View Related
Feb 21, 2007
I'm running a custom built report on .net page Basically there are a lot categories. I need to run SQL statements for and for each category, the catch is these statements are all in loops for i = 1 to 12. Therefore 108 categories X 12 iterations of SQL calls = a little more that 1200 sql calls from one .net page.
There is obviously a major problem with load time and running these reports are painful to the user and the system.
Is there any easier way to possibly pre-compile all the data into a temp database so I wouldn't need to make as many sql calls??????? If so, would a stored procedure handle something like this.
Sorry for being somewhat vague.
View 7 Replies
View Related