I was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set. For instance:
select name, age, title
from tablea
select name, age, title
from tableb
Could you combine these queries into a single result set?
I have a SP returning the following result The select statement for this is
Code:
SELECT dbo.TEST1.[OFFICE NAME], COUNT(dbo.TEST1.[ACCOUNT ID]) AS AccountCount FROM dbo.Test2 INNER JOIN dbo.test3 INNER JOIN dbo.Test4 ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code] INNER JOIN dbo.TEST1 ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID] ON dbo.Test2.[Model ID] = dbo.test3.ID INNER JOIN dbo.[Inquiry Details] ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID WHERE (dbo.Test2.InquiryDate BETWEEN CONVERT(DATETIME, @startDate, 102) AND CONVERT(DATETIME, @endDate, 102)) AND dbo.Test1.[Account ID] IN(SELECT [account id] FROM test5 WHERE [Contact ID] = @contactId) GROUP BY dbo.TEST1.[OFFICE NAME] ORDER BY COUNT(dbo.TEST1.[ACCOUNT ID]) DESC name id count case1 226 320 case2 219 288 case3 203 163 case4 223 90 case5 224 73 i have another select stnat which returns like this The select statement is
Code:
Select test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code] Group by test1.[Office Name] order by count(test1.[office name]) DESCname count case6 10 case2 56 case4 66 case1 74 case3 88 case7 100 case5 177 How can i combine this select stament with the SP, so that, i get a fourth column with case1 226 320 74 case2 219 288 56 .......................... ........................... Hope i am not confusing you all Please help me, if someone knows how to combine this? Thanks
I have two select statements on a single table as follows:
SELECT * FROM DOCUMENTS
WHERE FILEDATE LIKE '%1987'
SELECT DOCNUM, COUNT(*) AS TOTALS FROM DOCUMENTS
GROUP BY DOCNUM
HAVING (COUNT(*)>1)
I want to combine the them to provide a list of rows in the table that have duplicate "DocNum" but only within the subset of rows LIKE %1987. When I first looked into how this is done I was sure that you would use a subquery. I tried to combine them using the first one as the outer query and the COUNT as the subquery. However, this gave an error that the subquery was returning a value that did not match what the outer query was expecting. So my questions are: Do I really need a subquery in the first place since this is all within one table? And, if not, how can this be done?
I'm looking for a way to combine the following 2 sets of code into one select statement. They're similar in that they reference the same tables, but they have different conditionals:
Code: SELECT TABLE_01.Date , TABLE_01.State , TABLE_01.City , ISNULL((SUM(A.Bandwidth)),0) AS SD_Bandwidth INTO TABLE_FINAL_01 FROM TABLE_01 LEFT OUTER JOIN TABLE_02 A
how can take codes below and put them into one store procedure to supplie a gridview. also i will like to define the row name on the left like i did to the column on the top using the 'AS' Code1.... SELECT SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February, SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April, SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June, SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August, SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October, SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December, SUM(CASE WHEN site_descr = 'SITE1' THEN 1 ELSE 0 END) AS AllTotal FROM dbo.V_results WHERE (site_descr = 'SITE1')
Code2.....
SELECT SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February, SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April, SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June, SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August, SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October, SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December, SUM(CASE WHEN site_descr = 'SITE2' THEN 1 ELSE 0 END) AS AllTotal FROM dbo.V_results WHERE (site_descr = 'SITE2')
Hi guys and gals, I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says: 'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.' My Code is below: //Start of sql CREATE PROCEDURE ADMIN_GetSingleUsers( @userID int) AS DECLARE @userSQL intSET @userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE, Cast(Users.Active as varchar(50)) as Active, Cast(Users.Approved as varchar(50)) as Approved, Users.Unit_ID As usersUnitID, * From TITLE, Users WHERE User_ID = @userID AND TITLE.TITLE_ID = Users.Title_ID ) Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @userSQL //End of sql Can you point to what I am doing wrong? Thanks in advance!
I know I should know the answer to this, but I just can't quite get the syntax down
Code: Select case when zipCode = '10185' Then 'Deliver' Else when zipCode = '2309' And paid = 'Yes' Then 'Deliver' Else When zipCode = '1291' And paid = 'Yes' Then 'Deliver' Else When zipCode = '88221' And paid = 'No' Then 'Hold' Else when zipCode = '34123' Then 'Deliver' End From postalDeliveryDatabase
Hi,We are discussing possible implementation for sql2005 database(s). This database will serve one web portal. Part of data will get into it by hand, and part will be replicated from internal system.Some of us are for creating two separate databases, since there are two separate datasources. One, automatic, will change very little over time and requires almost no maintenance. Other datasource will be manual input. Tables and procedures related to this part will change over time.Some of us are for creating single database, since it will serve one web site. More important this group is concerned about performance issues since almost every select will require join between tables that would be stored in two separate databases. Do these issues exist? Can you share some insights, comments, links about this?
Hi, OK, trying to return the results from two SQL statements into a DataSet using SqlDataAdapter. The SELECT statements query the same table but are looking for different records based on the date that the records were inserted - the 1st query looks for records fro the current month and the 2nd one looks at the same records but for the previous month. The goal is to be able to do some math within a repeater and get the difference between the two records. Sounds easy enough and it has worked for me in different variations of the same idea but not this time - here's the code: Protected Sub buildPartsReport(ByVal varHC) objConn.Open() sSQL = "SELECT ap.id,item_model,item_sn,aircraft_id,item_loc=item_type+ ' on ' +(SELECT tnum FROM T_Aircraft WHERE id=aircraft_id),apt.tot_time As endTimes,apt.tot_cycles as endCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & rD & "' AND item_type LIKE 'engine%';SELECT ap.id,apt.part_id,apt.tot_time as startTimes,apt.tot_cycles as startCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & oldRD & "' AND item_type LIKE 'engine%'" Dim objCommand As New SqlDataAdapter(sSQL, objConn) DS = New DataSet() objCommand.Fill(DS) Repeater1.DataSource = DS Repeater1.DataBind() DS.Dispose() objCommand.Dispose() objConn.Close() End Sub Sorry if it wrapped a bit. The "rD" and "oldRD" are the variables for the date ranges (currently set to static numbers for testing). I'm getting the following error when I run this on an ASP.Net page: System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'startTimes'. The code works fine when run via the Query Tool on the SQL server (SQL 2005 Std) though it produces two distinct "tables" which I'm guessing is the problem. I've tried variations on the code including creating a 2nd dataset and then attempting a merge (no joy) and I've tried the ".TableName" route but it complains about trying to add the tablename twice. Thoughts? I need to get this to work - it is part of a reporting component for an application that I'm developing and I'm stuck. Thanks as always...
first of all is it possible? if so, what am i doing wrong with this
INSERT into TB2
(
ClientCode, EngagementCode, EngagementDescription
)
SELECT (SELECT dbo.tarCustomer.CustID FROM dbo.tPA00175 INNER JOIN dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),
NULL,
SELECT (SELECT dbo.tPA00175.chrJobNumber FROM dbo.tPA00175 INNER JOIN dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)
the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.
this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks
I have created a stored procedure with multiple select statements using MSSQL 2000. When I connect to this using a SQLDataSource it only seems to get the first SELECT. I need to access the multiple tables returned by the stoped procedure. Can anyone point me in the dirrection of how to do this.ThanksClearz
I have used several sql queris to generate a report. This queries pull out data from different tables. But sometimes at the same table too. Basically those are SELECT statements. I have created stored proc for each SELECT statement. now I'm wondering can I include all SELECT statements in one stored proc and run the report. If possible, can anyone show me the format?
Hey Guys. I’m having a little trouble and was wondering if you could help me out. I’m trying to create a custom paging control, so I create a stored procedure that returns the appropriate records as well as the total amount of records. And that works fine. What I’m having problems with is reading the data from the second select statement within the code. Anyone have any idea on how to do this? Also.. how can I check how many tables were returned? Here's my code. I'm trying to keep it very generic so I can send it any sql statement:public DataTable connect(string sql) { DataTable dt = new DataTable();
SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ToString()); SqlDataAdapter SqlCmd = new SqlDataAdapter(sql, SqlCon); System.Data.DataSet ds = new System.Data.DataSet(); SqlCmd.Fill(ds);
dt = ds.Tables[0];
//Here's where I don't know how to access the second select statement
return dt; } Here's my stored procedure: ALTER PROCEDURE dbo.MyStoredProcedure ( @Page int, @AmountPerPage int, @TotalRecords int output )
AS
WITH MyTable AS (
Select *, ROW_NUMBER() OVER(ORDER BY ID Desc) as RowNum From Table where Deleted <> 1 )
select * from MyTable WHERE RowNum > (((@Page-1)*@AmountPerPage)) and RowNum < ((@Page*@AmountPerPage)+1);
Select @TotalRecords = COUNT(*) from Table where Deleted <> 1 RETURN
My goal is to run a bunch of select statements from different tables in one database and have them all insert to the same columns/table in the new database. Do I need a new data source for each statement, or is there a way to run all the statements in one set seeing as they all have the same destination. I keep receiving the SQL statement improperly ended error when trying.
I ran the below 2 select statements and ended up seeing multiple cached instances of the same stored procedure. The majority have only one cached instance but more than a handful have multiple cached instances. When there are multiple cached instances of the same sproc, which one will sql server reuse when the sproc is called?
SELECT o.name, o.object_id, ps.last_execution_time , ps.last_elapsed_time * 0.000001 as last_elapsed_timeINSeconds, ps.min_elapsed_time * 0.000001 as min_elapsed_timeINSeconds, ps.max_elapsed_time * 0.000001 as max_elapsed_timeINSeconds
I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two). insertString = "Insert comments (uID) Select uID FROM users WHERE uName = @uName" insertString2 = "INSERT comments (eventID, text) VALUES ( @eventID, @comment)" I have tried: Insert comments (uID, eventID, text) SELECT uID FROM users WHERE uName = @uName VALUES (uID, @eventID, @comment) Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated! Thanks in advance.
Hey all. Ive got a big problem with an sql statement Im working on.There are 2 tables with a master/detail relationship. The Header Tableis the master, the Line Table is the detail. So for each Header, thereare many Lines, but a Line can only reference one Header.There is a Line Total and Line Cost in each Line Record. Each LineRecord has a type.What I want to be able to do is, for each Header, I want to Sum eachcorresponding Line's Total and Cost where the type is either one valueor another. If the type is, for example, 10, only sum the Total, if itstype 2, only sum the Cost.Therefore, after the query is executed, you should have a result setsomething like thisJob : Job1 (header id)Desc : Job0001 (header desc)Cost : (sum of Line Costs where Line Type is 2 and header id is Job1)Total : (sum of Line Totals where Line Type is 10 and header id isJob1)-----------------------------------------------------------------------------------------------------------Job : Job2 (header id)Desc : Job0002 (header desc)Cost : (sum of Line Costs where Line Type is 2 and header id is Job2)Total : (sum of Line Totals where Line Type is 10 and header id isJob2)-----------------------------------------------------------------------------------------------------------etc.Hope this makes sense. Thanks
In SQL Server 2000/2005 (not CE) I can use the following T-SQL statement to delete orphaned rows from a table:
DELETE GroupsMembers FROM GroupsMembers LEFT OUTER JOIN Groups ON GroupsMembers.GroupID = Groups.ID WHERE Groups.ID IS NULL
SQL Server CE does not seem to support combining the JOIN statement with the DELETE statement. Is this correct? If yes, is there any alternative statement that could be used to accomplish the same thing?
Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed' From Project WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')
As you can tell, the 'custom_address' column is going to house the results of the combination.
I am trying to INSERT (combine) using this statement:
INSERT #Custom_Address select cm.patient_id, cm.episode_id, (cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip) as custom_address FROM #ClaimMaster cm join Coverage_Custom cc on cm.patient_id = cc.patient_id and cm.episode_id = cc.episode_id WHEREcc.coverage_plan_add1 > 0 OR cc.coverage_plan_add2 > 0 OR cc.coverage_plan_city > 0 OR cc.coverage_plan_st > 0 OR cc.coverage_plan_zip > 0 GROUP BY cm.patient_id, cm.episode_id
I want to insert the patient_id, episode_id, and then combine the 'cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip' data to represent my "custom_address" column and only when there is data in those columns.
Then I do my update:
UPDATE#ClaimMaster SET #ClaimMaster.custom_address = ca.custom_address FROM #ClaimMaster JOIN#Custom_Address ca on #ClaimMaster.patient_id = ca.patient_id and #ClaimMaster.episode_id = ca.episode_id
However, when I do this, it says my 'cc.coverage_plan_add1 + etc' columns are invalid because they are not contained in either an aggregate function or a GROUP By clause.
If i'm combining all the data to represent a single column, how do I format my group by clause? Or is my entire INSERT statement wrong?
As a side note, my #ClaimMaster table is another temp table, but the problem is not in there.
I've tried something like this but since I'm referencing trans_type I get a message that trans_type must be in Group by. doing that give me multiple records.
select cus_no, Inv_no, Sum(Amount_Invoiced_DC) AS InvAmt, case trans_type when 'Sales Inv' then sum(Amount_Paid_DC) else 0 end as AmtPaid, case when trans_type = 'Discount' and sum(Amount_Paid_DC)<0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt FROM BI50_BankTransactions_AR_InvcDt_H where cus_no is not null group by cus_no, Inv_no
quite urgent... I have 3 matix tables.. all the same row headings. I need to be able to make these visible/invisible depending on the user parameters. The reason for this, if a user hides table on the left.. the middle table needs to show the row headings. and last table must not.
The problem: When making the row headings invisible it doesnt exactly chop off the textboxes... so the tables look disjoint.. and a big gap appears between both tables...
Is there a way to join these tables without leavings gaps ?
OK I have 4 differant Data Sources... One being a count of one DB, another count of another DB, another count of another and then another process from a script component. Each source returns 1 row of data with 1 column each except the Script Component. It returns 3 columns... Now I need to take each of the row's returned and combine them to a single row (line) and inset them into another table just as one single eatry. I am using a Union All and when it runs I see the 4 Data Sources say 1 Row... But after it hits the Union All it does 4 rows... What am I doing wrong or am I using the wrong component? Please if anyone can help that would be wonderful.
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this: ID - DayNr - Transportation - TransOrder - Route 25 - 1 - Car - 1 - Text A 25 - 1 - Train - 1 - Text B 25 - 1 - Train - 2 - Text C 25 - 7 - Train - 1 - Text D 25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2 25/1/Car - Text A 25/1/Train - TextB;TextC 25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. Ideas anyone?
Thanks. RMG
P.S. ID is not my primary key and doesn't have to be unique
I have 3 queries pulling from the same table, trying to define a count on each criteria. I have the data pulling, but the data is in multiple rows. I want the data in one row with all the counts in each separate columns. Also I need to setup a flag if a client purchased and order within 30 days from their last purchase.
I am doing this select for each credit card, check and cash purchases. I do not know how to setup a flag where the client may have ordered and paid by check or cash after 30 days from a credit card purchase. Is this something that can be done?
select clientnumber,count(distinct clientnumber) as cccnt, 0 as ckcnt, 0 as cacnt from dbo.purchases where orderdate >= 20120101 and orderdate <= 20121231 and payment_type = 'CC' group by clientnumber;
OUTPUT currently looks like this: 1234 2 0 0 1234 0 1 0 1234 0 0 4
Is it possible to result in this, along with a flag with the criteria above?: 1234 2 1 4 Y
I'm trying to develop a query that joins one record from a table with multiple matching records from another table all in one record, Table1 has the primary key id -- 1 2 3 4 Table2 has the follwing records id year subject ----------------- 1 2000 English 1 2002 French 2 2004 English 2 2005 English 2 2006 English 3 2007 French I want the result to be like this id 2000 2001 2002 2003 2004 2005 2006 2007 ----------------------------------------------------------- 1 English null French null null null null null 2 null null null null English English English null 3 null null null null null null null English
I have a table employee: that contains one column and three rows. How can I transform it using SELECT to display only one row and one column, with comma delimited strings: John, Mike, Dale?
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this: ID - DayNr - Transportation - TransOrder - Route 25 - 1 - Car - 1 - Text A 25 - 1 - Train - 1 - Text B 25 - 1 - Train - 2 - Text C 25 - 7 - Train - 1 - Text D 25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2 25/1/Car - Text A 25/1/Train - TextB;TextC 25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. The big issue here is that I have to base my concatenation on 3 columns. Ideas anyone?
Thanks. RMG
P.S. ID is not my primary key and doesn't have to be unique