Grouping Select Statements With Where Clause
Hello
What I need to do is be able to group the results of my select statements in different columns. And end having the result work like this.
campaign Col1 Col2
<<Data>> <<Select counT(*) where field= value>> <<Select counT(*) where field= value>>
View Complete Forum Thread with Replies
Related Forum Messages:
Optimising Select Statements Which Has A ‘LIKE’ Where Clause.
Hi all I have been doing some development work in a large VB6 application. I have updated the search capabilities of the application to allow the user to search on partial addresses as the existing search routine only allowed you to search on the whole line of the address. Simple change to the stored procedure (this is just an example not the real stored proc): From: Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode = ‘W1 ABC’ To: Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode LIKE ‘W1%’ Now this is when things went a bit crazy. I know the implications of using ‘with(nolock)’. But seeing the code is only using the ID field to get the required row, and the database is a live database with hundreds of users at any one time (some updating), I think a dirty read is ok in this routine, as I don’t want SQL to create a shared lock. Anyway my problem is this. After the change, the search now created a Shared Lock which sometimes locks out some of the live users updating the system. The Select is also extremely SLOW. It took about 5 minutes to search just over a million records (locking the database during the search, and giving my manager good reason to shout abuse at me). So I checked the indexes. I had an index set on: strAddressLine1, strAddressLine2, strAddressLine3, strAddressLine4, strPostCode. So I created an index just for the strPostCode (non clustered). This had no change to the ‘Like select’ what so ever. So I am now stuck. 1)Is there another way to search for part of a text field in SQL. 2)Does ‘Like’ comparison use the index in any way? If so how do I set this index up? 3)Can I stop a ‘Shared Lock’ being created when I do a ‘like select’? 4)Do you have any good comebacks I could tell the boss after his next outburst of abuse (please not so bad that he sacks me). Any advice truly appreciated.
View Replies !
Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View Replies !
SELECT Then DELETE Versus Extra Clause In SELECT
Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'
View Replies !
Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples: 1) Rows ordered using textual id rather than numeric id Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id Result set is ordered as: 1, 11, 2 I expect: 1,2,11 if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. 2) SQL server reject query below with next message Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique. Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. It reproducible on Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) and Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) In both cases database collation is SQL_Latin1_General_CP1251_CS_AS If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again. Could someone clarify - is it bug or expected behaviour?
View Replies !
SELECT Grouping
If I have a table which as the following columns/data item boxes quantity ---- ----- -------- CH 20 16 CH 10 20 CH 15 16 how can I get all the CH items so that they are grouped by quantity and the boxes are totalled to give: CH 35 16 10 20 please?
View Replies !
Grouping Select Query
Hi,I have data stored as in below sample :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-14 00:00:0063.00002005-11-14 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-16 00:00:0045.00002005-11-16 00:00:002005-11-17 00:00:0045.00002005-11-17 00:00:002005-11-18 00:00:0045.00002005-11-18 00:00:002005-11-19 00:00:0045.00002005-11-19 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-21 00:00:0063.00002005-11-21 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------I have to group the select query in this way :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------When I run below grouped statement, I get follewed result:SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,Rate FROM X GROUP BY Rate-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-22 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.0000-------------------------------+---------------------------------+--------------How can I do a query like in 2nd sample from top?best regards,rustam bogubaev
View Replies !
Several Select Statements?
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 Replies !
Select Statements
Arnie and All others. Thanks for your help. The previous case became lenghty and then just mixed up a lot. To make it easy I have created two temp tables and wrote to test select statement . You will notice that I tired two select statement but they are giving different set of result however the 2nd Select statement not giving the result as should be looking at the following requirement. --Selected record must RaType='b' -- PlanID='H321' -- Gender='0' -- --And not to include in select if any one of these meets: -- Hosp='1' in other words it has to be 0 -- ESRD='1' or Rafctor Type in ('g','f') in other words ESRD should be 0 and rafctorType in ('h','i') -- Dod is not null in other words Dod has to be null -- --copy from here GO Create table #MyTable ( RowID int IDENTITY, RD varchar(10), RAType varchar(5), History varchar(15) ) INSERT INTO #MyTable VALUES ( '1', 'A', '1111' ) INSERT INTO #MyTable VALUES ( '2', 'S','2222' ) INSERT INTO #MyTable VALUES ( '3', 'D', '2345') INSERT INTO #MyTable VALUES ( '4', 'I2','1234' ) INSERT INTO #MyTable VALUES ( '5', 'C','3333' ) INSERT INTO #MyTable VALUES ( '1', 'B','4444' ) INSERT INTO #MyTable VALUES ( '2', 'X','5555' ) INSERT INTO #MyTable VALUES ( '1', 'D' ,'66666') GO Go Create Table #MYTable2 ( RowID int IDentity, RD varchar(10), RaType varchar(5), History varchar(15), PlanID varchar(6), Hosp varchar(2), ESRD varchar(2), RafctorType varchar(3), gender varchar(5), dod varchar (5) NULL ) INSERT INTO #MyTable2 VALUES ( '1', 'A', '1111', 'H321', '0','0', 'g', '0' ,NULL) INSERT INTO #MyTable2 VALUES ( '2', 'b', '2222', 'H321', '0','0', 'e', '0',NULL ) INSERT INTO #MyTable2 VALUES ( '2', 'b', '3333', 'H321', '0','0', 'f', '0',NULL ) INSERT INTO #MyTable2 VALUES ( '2', 'b', '4444', 'H321', '0', '0','d', '0',NULL ) Go Select #MYtable2.History from #MYTable2 INNER JOIN #mytable on #myTable2.History=#mytable.history Where #MyTable2.RaType='b' And PlanID='H321' And Gender='0' And Hosp<>'1' And ESRD<>'1' AND RafctorType Not in ('g','f') AND Dod is NULL Select #Mytable2.History from #MyTable2 INNER JOIN #mytable on #mytable2.History=#mytable.history where #mytable2.Ratype='b' AND PlanID='H321' AND Gender='0' AND(Hosp<>'1') or ((ESRD<>'1') or (RafctorType Not in ('g','f')) OR DOD is NULL) Go drop table #Mytable2 Drop table #MYtable
View Replies !
How To Use Two Select Statements
Both of these work fine separately; How do I join these two Select Statements? SELECT MPI.CREATE_DT,MPI.MPI_NBR, MPI.LAST_NM,MPI.FIRST_NM, MPI_CURRENT_ADDR.ADDR_NBR, MPI_CURRENT_ADDR.ADDRESS_1, MPI_CURRENT_ADDR.ADDRESS_2, MPI_CURRENT_ADDR.CITY,MPI_CURRENT_ADDR.STATE_CD,MPI_CURRENT_ADDR.ZIP_CD,MPI_CURRENT_ADDR.PHN_NBR, MPI_PERSON.BIRTHDAY,MPI_PERSON.SEX FROM MPI,MPI_CURRENT_ADDR,MPI_PERSON WHERE (MPI.MPI_NBR=MPI_PERSON.MPI_NBR) AND (MPI.ADDR_NBR=MPI_CURRENT_ADDR.ADDR_NBR) AND MPI.CREATE_DT>=20070101 ORDER BY MPI.CREATE_DT SELECT PATIENT.PAT_NBR,PATIENT.PHYS_NBR, PHYSICIAN.FIRST_NM,PHYSICIAN.LAST_NM,PHYSICIAN.DE_NBR, PHYSICIAN.SALUTATION_CD FROM PATIENT, PHYSICIAN WHERE PATIENT.PHYS_NBR=PHYSICIAN.PHYS_NBR Thanks! Lisa
View Replies !
Two Select Statements
I have a table that list Canadian provinces and American States it looks something like this: ID | ProvState Under ID 1-13 lists the Canadian provinces and everything over 13 lists the American states. I want to create 1 query that will list the Canadian provinces first in alphabetical order then the States in alphabetical order. I have tried using UNION but it's not returning what I want and it does not allow me to use order by for the first statement. SELECT * FROM SPProvince WHERE ID < 14 ORDER BY ProvState UNION SELECT * FROM SPProvince WHERE ID > 13 ORDER BY ProvState Anyone have any suggestions to this problem?
View Replies !
SELECT Question, Grouping Values
What is the best way to build a SQL query to extract data from a table in this manner: COLA COLB COLC DATE 01 01 01 04/15/1988 01 01 01 11/23/1997 01 01 01 02/02/2005 01 02 01 6/14/1990 01 02 01 10/30/1999 01 02 02 4/24/2001 How would I select Row 3, 5, and 6 above (In other words, from each set where COL A B & C are equivalent, grab the most recent date?) Thanks!!!
View Replies !
Grouping And Ordering From A Select Function
Hi, I am using MS SQL 7 on windows 2000 platform. I have two fields getting pulled from a table and I want the results to be returned in order by price from lowest price to highest price but grouped by identification number e.g. 1234, 2234 and 3333 are the identification numbers and the column on the left is the price price identification number --- ----- 50 1234 60 1234 70 1234 80 1234 60 2234 70 2234 90 2234 100 2234 90 3333 120 3333 130 3333 140 3333 The main order is by price but I want the identification numbers to stay grouped as well. Thanks alot for any help Regards Mark
View Replies !
SELECT EXEC Statements In SQL
I am writing a stored procedure to select some information from two tables and I would also like to Execute a function using the userid information from the processing in the where clause of the Select statement. Is the syntax below possible?? If yes, could you please help me understand exactly what I may be doing wrong here.. If no, can you please help with better syntax. Thanks in advance SELECT M.UserID, M.FullName, (EXEC calcPoints M.UserID) as 'UserPoints' FROM MissionUsers M LEFT OUTER JOIN MissionUserInfo MU ON M.UserID = MU.UserID WHERE M.EMAIL = @UserEmail
View Replies !
USING MULTIPLE SELECT STATEMENTS
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') thanks in advance
View Replies !
Joining Two Select Statements
I only want to count the titleids that are on loan can I join these two statements or write the stored procedure a different way? I hope this makes sense. select count(libraryrequest.titleid) as [Presently on Loan], libraryrequest.titleid, media.[description]as Media from libraryrequestjoin titles on titles.titleid = libraryrequest.titleidjoin resources on resources.titleid = titles.titleidjoin media on media.mediaid = resources.mediaidgroup by libraryrequest.titleid , media.[description] select distinct requestors.Requestorid, titles.title, resources.quantityowned,requestors.requestorEmail,Requestors.requestdate, fname, lname, phonenum,StreetAddress1, City, State, Zip, libraryrequest.shipdate,libraryrequest.duedate, libraryrequest.returndate from Requestorsjoin Titles on titles.Titleid = requestors.Titleidjoin libraryRequest on LibraryRequest.requestorid = Requestors.requestoridjoin resources on resources.titleid = titles.titleid
View Replies !
Combining Two Select Statements
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
View Replies !
Two Select Statements In Asp.net 2.0 Problem
Hi Everyone, Can you please let me know what is wrong with the following code? I run the following code using path: http://localhost/jimmy/may_30th_2006/vcalendar_cs/sb_PAYMENTS_page.aspx?LoginID=admin public void searchResultsWithClinic() { SqlConnection myConnection; string conString; conString = ConfigurationManager.AppSettings["calendarString"]; myConnection = new SqlConnection(conString); String cmdStr1, cmdStr2, cmdStr3; cmdStr1 = "SELECT sb_clinic_name FROM sb_client_and_clinic WHERE sb_client_id = '" + Request.Params.Get("LoginID") + "'"; cmdStr2 = "SELECT sb_client_id FROM sb_client_and_clinic WHERE sb_clinic_name = '" + cmdStr1 + "'"; SqlDataAdapter myCommand = new SqlDataAdapter(cmdStr2, myConnection); DataSet DS = new DataSet(); myCommand.Fill(DS, "SearchPaymentResults"); repeaterSearchPaymentResults.DataSource = DS; repeaterSearchPaymentResults.DataBind(); myConnection.Close(); } Incorrect syntax near 'admin'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'admin'.Source Error: Line 90: SqlDataAdapter myCommand = new SqlDataAdapter(cmdStr2, myConnection); Line 91: DataSet DS = new DataSet(); Line 92: myCommand.Fill(DS, "SearchPaymentResults"); Line 93: Line 94: repeaterSearchPaymentResults.DataSource = DS;Source File: d:Inetpubwwwrootjimmymay_30th_2006vcalendar_cssb_SearchPaymentResults.ascx.cs Line: 92 --------------------------------------------------------------------------------- Please note that the 'admin' in the error message comes from http://localhost/jimmy/may_30th_2006/vcalendar_cs/sb_PAYMENTS_page.aspx?LoginID=admin thanks,May
View Replies !
Multiple Select Statements
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!
View Replies !
Two Select Statements On One Table
Hi, newbie here. Im trying to perform the following two select statements on the one table. I have been trying innerjoins etc but keep getting errors. The basis of what im trying to do is this. SELECT column1 FROM table1 WHERE column2 = (select column2 from table1 where column3 = 14) Any ideas greatly appreciated.
View Replies !
Using Two Select Statements In IF Exists
I tried this:IF Exists (Select 1 from TESTA..POP10100 where PONumber=@PONumber) and(Select 1 from TESTA..POP30300 where POPType<>2 andPOPRCTNM=@ReceiptNo)BEGINENDIt says Incorrect Syntax error? I tried to cover the whole thing withan extra parantheses. It wont work eitherWhat am i doing wrongThanks, Girish
View Replies !
Using IF In SELECT Statements? Beginner.
I am having problems using IF statements. In fact, I don't even know if it is possible to use "IF" in a SELECT statement. Here's my dilemma: For each employee in my database, I have a numeric rating. I want to assign the rating to a category (green, yellow, red), based on the range the rating falls into. My data currently looks like this: EmpName Rating employeeX 100 EmployeeY 85 EmployeeZ 25 After the magic of SQL, I want the data to end up looking like this (actually, my boss wants it like this): EmpName Green Yellow Red employeeX 100 EmployeeY 85 EmployeeZ 25 The numeric rating would be inserted into the appropriate column, based on these ranges: 95 to 100 = green 80 to 94 = yellow < 80 = red I thought I could use a CASE statement, but I just learned that I can't do comparisons (I'm obviously a beginner). And I can't figure out how to use an If statement (or IIF). Can anyone help? Thanks! Colleen
View Replies !
Combining Select-Statements
Hi ! I have to divide and analyse a bigger table to get a smaller one. My target is to make this division in only one SQL-Statement. My table looks like this: Code: ArtNo Code 16637 C 38827 A 16637 D 44883 C 44883 C 63853 H 24564 D 24564 A (ArtNo is not the Primary Key) My SQL-Statement should now find out how often every "code" belongs to a "ArtNo". The result should be: Code: ArtNr A C D H 16637 1 1 38827 1 34343 1 44883 2 63853 1 24564 1 1 Does anyone have ANY Idea how I could realize this as easy as possible (without View etc.) ?? Regards Gawan
View Replies !
Is It Possible To Combine 2 SELECT Statements??
I am not sure if this is possible, but I was wondering if I can combine 2 SELECT statements so as to aquire a percentage.. I could be overthinking this....I am fairly new to SQL writing. Here is an example of the 2 SELECT statements that I am using: Code: SELECT COUNT([Overall Rating]) FROM S526960.HDPIMaster WHERE Location = 'HBUS' AND [Overall Rating] = 'Good' Code: SELECT COUNT([Overall Rating]) FROM S526960.HDPIMaster WHERE Location = 'HBUS' Within my output I am than taking the the data from the 1st query and dividing it by the 2nd query to get a percentage. I was hoping that I could accomplish the same action within one SQL statement. Thank you for your consideration!
View Replies !
Nested Select Statements
I need help nesting select statements. Here's what I'm trying to do: select (select e.emp_name_lf as employee, e.emp_id from employee e, install_payroll_detail ipd where e.emp_id = ipd.emplno) e.emp_name_lf as username from employee e, install_payroll_master ipm where e.emp_id = ipm.entered_by I just want one row with both the employee and username, however I cannot get the syntax. Any help is greatly appreciated. Thanks!
View Replies !
Combining Two SELECT Statements (How To?)
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? Thanks! DeBug
View Replies !
Three Different Select Statements In SP - Cant Get All Columns
Hi, I have a SP having three differnet select statements like, IF @reporttype ='A' SELECT Column1, column2 FROM table1 IF @reporttype = 'B' SELECT Column3, column4 FROM table2 IF @reporttype = 'C' SELECT column5, Column6 FROM table3 Now I need three different reports for all three reporttypes. If i creating first report for @reporttype A, it is executing fine and giving me the Columns which i need. But while creating reprots for reporttype B and C, I m getting columns column1 and column2 . Its not displaying Column3, Column4, Column5, Column6. Can anybody help me with this?
View Replies !
Storedprocedure Which Contains Two Select Statements
Hi I have a question. I have to write a stored procedure.I have a search page having four fields.Giving any of the field should fetch the whole record and display in the gridview. My trouble starts here I have a button field in gridview1 . when i click on the button there should be another gridview which displays refunds of particular customer i.e from another table.There is only one common colum in the two tables. based on that colum value we have to fetch from second table. now my question is : how to capture the colum value of first select statement and give it as input to second select staement. my code is here : set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[search] (@val1 varchar(225), @val2 varchar(50), @val3 varchar(50), @val4 varchar(50)) AS BEGIN Select*From customer where ((@val1 IS NULL) or (CNo = @val1)) AND ((@val2 IS NULL) or(LastName = @val2)) AND ((@val3 IS NULL) or(FirstName = @val3)) AND ((@val4 IS NULL) or(PhoneNumber = @val4)) Select * From refunds where CNo = @val1 END here if i fill CNo text box in my search page its giving the value but all the time user may not give CNo.He may search customers based firstname ,lastname etc. so what should i do to capture CNo from first select statement and give it as input to second select statements anyone help is appreciated. ramya.
View Replies !
How Do You Use Declarations In Select Statements?
Hello all. I have a fairly simple question (I think) about using the @Declare in a select statement. Essentially what I am trying to do is this DECLARE @SELECT varchar(max) SET @SELECT = '*' SELECT * FROM test I know this doesn't actually work but it conveys what I am trying to do. Is there a way to do this?
View Replies !
Transactions With Select Statements
Hello everyone I need to have a transaction with a select statement like so trans = cn.beginTransaction Insert into order table an order record select unique order Id from order table insert record 1 into orderdetails using the orderid insert record 2 into orderdetails using the orderid commit trans I am of course experiencing record locking after my first insert. I cannot select records from the order table even though I need the inserted OrderId in order to insert into the orderitemst table. I have tried switching the isolation level to readuncomitted on my transaction but sp_who2 still shows a block even with the readUncomitted Isolation level. A foreign key on the orderitems table prevents me from inserting orderitems without a parent order id to prevent orphans. Is this a common transactional issue? Any help is greatly appreciated.
View Replies !
Joining Select Statements
Hi , If any one could help me in Joining these two select statements: How Can I have Order by and Having Clauses in my final select list ? *********** SELECTMIN(DISTINCT x.ProductionDate) as ProductionDay,COUNT(DISTINCT v.Number) as Timelost FROMmaster..spt_values AS v INNER JOIN ( SELECTDATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),StartDate,108)) AS StartMinute, DATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),EndDate,108)) AS EndMinute, Convert(varchar(10),StartDate,102) as ProductionDate --,COUNT(AlarmMessage) As Occurances FROM RPMS.dbo.List_PaintShopAlarms WHERE --Convert(varchar(10),StartDate,102) = @vcProductionDay and Convert(varchar(10),StartDate,102) = Convert(varchar(10),EndDate,102) AND (AlarmMessage LIKE '7FM%' OR AlarmMessage LIKE 'CONV%' OR AlarmMessage LIKE 'EMG%' ) ) AS x ON x.StartMinute <= v.Number AND x.EndMinute > v.Number WHEREv.Type = 'p' AND Number < 1440 and (x.ProductionDate BETWEEN Convert(varchar(10),@dtProductionDayStart,102) AND Convert(varchar(10),@dtProductionDayEnd,102)) group by x.ProductionDate order by x.ProductionDate asc *********** and *********** SELECT Convert(varchar(25),[StartDate],102)as ProductionDay ,COUNT(AlarmMessage) As Occurances FROM [RPMS].[dbo].[List_PaintShopAlarms] WHERE ([AlarmMessage] LIKE '7fm%' or [AlarmMessage] LIKE 'Conve%') AND Convert(varchar(25),[StartDate],102) BETWEEN Convert(varchar(25),@dtStartDate,102) AND Convert(varchar(25),@dtEndDate,102) and DATEPART(dw, [ProductionDay]) <> 1 GROUP BY Convert(varchar(25),[StartDate],102) HAVING SUM([Duration]) < 86400 ORDER BY Convert(varchar(25),[StartDate],102) asc *************
View Replies !
Track Select Statements.
different Users are running select statements under their own login. they have readonly permission to the database. My goal is to track all those queries and send notification which tables were opened and which columns were displayed...Looks like I need trigger on select statement for the table...( Unfortunetely it doesn't exists) I collect all those statements in trace file and in the night time I stop trace, move file data to table and then step by step try to massage sql queries using VB.net... Most of those queries are so complicated, have derived tables inside and it is really hard to parse these sql statements... 1.If anybody has an idea how to say: User B opened table tbl_A col_B twice per today...using different methodology rather then go through SQL.(Other words how to create an event on a table which will be fired in a case of request select any column of that table?) 2.If somebody know where we can take good parser which will help with this job? Thanks
View Replies !
Select With Grouping For Multiple Date Columns
Hi All, Thanks for dropping by my post. I have a table which is of this form. ID MS030_A MS030_F MS036_A MS036_F MS040_A MS040_F ZZ0023 2/16/06 2/16/06 8/10/07 8/10/07 11/21/05 11/21/05 ZZ0031 8/10/07 4/5/07 8/9/07 8/9/07 3/22/07 3/22/07 ZZ0077 8/9/07 9/7/07 8/10/07 8/10/07 8/10/07 9/7/07 ZZ0078 8/10/07 9/7/07 8/9/07 8/9/07 8/9/07 9/7/07 ZZ0079 8/9/07 8/10/07 10/26/05 10/26/05 8/10/07 8/10/07 ZZ1030 3/31/05 8/10/07 9/1/05 9/1/05 8/9/07 ZZ1033 3/24/06 8/9/07 8/9/07 8/9/07 3/31/05 ZZ1034 8/10/07 8/10/07 8/9/07 8/9/07 3/24/06 ZZ1037 8/9/07 8/9/07 9/24/07 9/24/07 ZZ1040 10/26/05 10/26/05 9/24/07 9/24/07 ZZ1041 9/1/05 9/1/05 9/24/07 9/24/07 ZZ1042 8/9/07 8/9/07 9/24/07 9/24/07 11/21/05 The goal is to group all this transactions by Month and Year. Something like this.... MS030_A MS030_F MS036_A MS036_F MS040_A MS040_F Month Year 3 2 2 2 2 2 1 2006 4 4 7 9 8 9 2 2006 10 10 6 8 8 3 2006 4 4 5 5 3 2 4 2006 5 6 8 3 7 1 5 2006 For just one date column it is pretty straight forward i.e., just do a select count and group by DATEPART ( Mm, DateField) but for multiple columns i am in a total fix.... can please someone help me out... appreciate your help
View Replies !
Using String Variables In SQL Select Statements
Hi I am trying to include a string variable in a Select Statement. My problem is that when I code with the user name hard coated in the SQL Statement it works fine (see below:) cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = 'jdoe' " But when I try to use the String variable I get an error (See below): cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = " & StrUserName I know there must be something wrong with my syntax ?? Thanks Jackson
View Replies !
Two Select Statements In SP Return No Value In Web Form (only)
Hello. I a SP with 2 select statements. If the first returns no results it does the second select. This works fine if I test it on the sql server. But when I try it in a webform using a sqldatasource it only returns a result if the first Select returns rows. If not It is an empty set (where I know it should not be. It returned resultrs on the SQL server). This is the code:1 ALTER PROCEDURE [SPName] 2 3 @SearchFor varchar(150) -- search string to compare with 4 5 AS 6 7 SELECT p.UserName, 8 9 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName, 10 11 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount 12 13 FROM Profiles p WHERE UserName = @SearchFor 14 15 IF (@@ROWCOUNT = 0) 16 17 Begin 18 19 SELECT p.UserName, 20 21 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName, 22 23 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount, 24 25 DIFFERENCE(p.UserName, @SearchFor) as Similarity 26 27 FROM profiles p WHERE SOUNDEX(p.UserName) = SOUNDEX( @SearchFor ) 28 29 ORDER BY Similarity 30 31 END 32 33 RETURN Now If i change the SP to test it to the code below, it does work in both the webform and on the sql server: 1 ALTER PROCEDURE [SPName] 2 3 @SearchFor varchar(150) -- search string to compare with 4 5 AS 6 7 DECLARE @RowCount int 8 SET @RowCount = 0 9 10 IF (@ROWCOUNT = 0) 11 12 Begin 13 14 SELECT p.UserName, 15 16 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName, 17 18 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount, 19 20 DIFFERENCE(p.UserName, @SearchFor) as Similarity 21 22 FROM profiles p WHERE SOUNDEX(p.UserName) = SOUNDEX( @SearchFor ) 23 24 ORDER BY Similarity 25 26 END 27 28 RETURNOnce again, Everything works as it should (for both versions), if I am testing it directly on the SQL server. Can anyone Help me on this? I am stumped and cannot find any answers about this.
View Replies !
Combining Multiple Select Statements In A SP
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, titlefrom tableaselect name, age, titlefrom tablebCould you combine these queries into a single result set?
View Replies !
Building Complex Select Where Statements
I am building a web application for tracking jobs in my company using SQLexpress, VWDexpress and ASP.NET 2. I have two joined tables that I must give my user a way to filter on various sets of user entered or selected data. I can build realatively simple one or two column Selects and display in a gridview. They fall apart when I get a null value. Does anyone have a good reference on building complex filters using VWD express. Thanks George
View Replies !
Select Statements To Bind Datagrid
I want to select 2 fields from two tables inorder to bind my Datagrid... How can I do that using SQL statement? Possible to do that with one statement? or i need to create stored procedure? Can anyone show me sample codes?
View Replies !
Output Parameters And Select Statements
Hi, thanks for reading! Here is my problem: I have a strored procedure that inserts some records into one table and then selects some records from another table at the end. The stored procedure takes several parameters, first one of them is marked as OUTPUT. I'm using it to return an id of the inserted record. The procedure is called from asp.net code with first parameter set as ParameterDirection.InputOutput (tried with just Output as well). Now for the problem: if the the select statement at the end returns 0 records everything works and i my first parameter contains the @@IDENTITY value from the insert statement like it is supposed to. If the select statement at the end returns 1 or more records my output parameter is not updated at all and contains the same value as before the procedure was run. All the records are inserted correctly. if i try to return the @@identity as a plain select statement instead of through the parameter i get System.DBNull. I hope you can shed some light on this for me. Here is my stored procedure: CREATE PROCEDURE cwSaveProductInquiry @inquiryId int OUTPUT, @libraryName nvarchar(500), @contactName nvarchar(200), @address nvarchar(100), @city nvarchar(50), @state nvarchar(3), @zip nvarchar(10), @phone nvarchar(50), @email nvarchar(100), @comment nvarchar(3000), @productIds nvarchar(2000) AS INSERT INTO INQUIRY (LibraryName, ContactName, Address, City, State, Zip, Phone, Email, Comment) VALUES(@libraryName, @contactName, @address, @city, @state, @zip, @phone, @email,@comment) --i tried including this statement at the end as well but that did not do the --trick either select @inquiryId=@@IDENTITY FROM INQUIRY set nocount on declare @separator_position int -- This is used to locate each separator character declare @objectId varchar(200) -- this holds each array value as it is returned if(@productIds is not null) begin while patindex('%,%' , @productIds) <> 0 begin select @separator_position = patindex('%,%' , @productIds) select @objectId= left(@productIds, @separator_position - 1) INSERT INTO PRODUCT_INQUIRY_LOOKUP (ProductId,InquiryId) VALUES(@objectId, @inquiryId) select @productIds = stuff(@productIds, 1, @separator_position, '') end end set nocount off Select Distinct Email from vPRODUCT_CONTACT WHERE ProductId in (Select ProductId From Product_Inquiry_Lookup Where InquiryId=@inquiryId) GO
View Replies !
SQL Stopping After Processing Over 300 Select Statements HELP!
I get the following error: Error Number: -2147467259 Line Number: 0 Description: [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied. Now this happens AFTER SQL has queried over 300 select statements. I can't figure why it just STOPS. Is there a limit on how many statements SQL can process? Any ideas?
View Replies !
Exporting Select Statements To Txt File...
Hi All, Having trouble getting this to work. Been using a bunch of code from sites (including this one), but just can't get what I want. It seems so simple, but just can't get it. So, instead of showing what I have, I'd rather see what someone usually uses. I'm using Windows Auth, am going to use a 'SELECT * FROM DB.dbo.Table', and I want overwrite the outputed file each time it runs. And if the file doesn't exist - create it. I would also like it to be TAB delimited, and use quoted identifiers (""). And nulls will output as null (will be imported to another SQL Server by means I don't know of - and can't find out). They'll need to come back in as Null on the other side. Also, I'd like to see errors if there are any. I do appreciate any help. Thanks ahead of time, Denvas
View Replies !
How To Derive Parameters In Ad Hoc SELECT Statements
Hi, If I have ad hoc SQL statements created by users, which could be parameterized, how could I derive the parmeters at runtime. I cannot use CommandBuilder.DeriveParameters() as that is for StoredProcedures only. Just use Split on the SQL string? Or is there a better way, such as a third-party .Net Component? Thanks John
View Replies !
Combine Columns From Two SELECT Statements
I have a database that tracks billing and payment history records against a "relationship" record (the "relationship" maps a many-to-many relationship between employees and cell phone numbers). I have two statements that look like this: SELECT CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed FROM Relationship INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone INNER JOIN BillingHistory ON Relationship.PKRelationship = BillingHistory.FKRelationship GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber SELECT CellPhone.PhoneNumber, SUM(PaymentHistory.AmountPaid) AS TotalPaid FROM Relationship INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone INNER JOIN PaymentHistoryON Relationship.PKRelationship = PaymentHistory.FKRelationship GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber Each statement correctly aggregates the sums, but I need a record that shows me: CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed, SUM(PaymentHistory.AmountPaid) AS TotalPaid I can't figure out how to join or merge the statements together to get all of this information into one record without ruining the sums (I can't seem to correctly join the PaymentHistory table to the BillingHistory table without the sums going haywire). Any help is appreciated.
View Replies !
Embedding Select Statements In Webpages
I did some searching and didnt find anything that answered my question so here goes: When I took my last SQL class they had a piece where we used frontpage to create a small webpage that would issue a select statement against the sql database everytime you refreshed the page. Is there a way to do this without finding a copy of frontpage? we dont seem to have one laying around anywhere and I havent been able to find any other WYSIWYG editors that had this database link feature. I've made some static pages by using Excel to do the query, but I want one that will update dynamically once created. Any help would be greatly appreciated, doesnt have to be anything elaborate, just a something that allows me to issue a select statement. Thanks, T
View Replies !
Distinct Values In Select Statements
Hello, I have a table, which contains well over 30'000 rows. One of the Columns is for ransaction type, of which there are about 20-25 different types. I am trying to pull a list of each type value, using a select distinct TSQL command...but thus far am unsuccessful. Has anyone had a similar situation?? Am I wrong in using a Select Distinct?? Many thanks to all who reply!
View Replies !
Join Two Separate Select Statements Into One
I have two seperate tables that I need to get the data into one select statement. Here is the two separate select statements that I have created. Select AGEGROUP, [YEAR], SUM(CASES) as Cases FROM tbHosp a LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID LEFT JOIN tbSEX g on g.SEXID = a.SEXID where a.AgeGroupID in (1,2,3) and a.YEARID in (1,2,3) Group BY AgeGroup, [YEAR] ORDER BY AgeGroup, [YEAR] Select AGEGROUP, [YEAR], SUM(POPULATION) as [Population] FROM tbPopulation a LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID LEFT JOIN tbSEX g on g.SEXID = a.SEXID where a.AgeGroupID in (1,2,3) and a.YEARID in (1,2,3) Group BY AgeGroup, [YEAR] ORDER BY AgeGroup, [YEAR] Results - First Select with tbHosp AgeGroup YEAR CASES <1 2001 9 <1 2002 32 <1 2003 10 1-4 2001 13 1-4 2002 11 1-4 2003 23 5-9 2001 13 5-9 2002 14 5-9 2003 34 Second Select with tbPopulation AgeGroup YEAR POPULATION <1 2001 40686 <1 2002 39768 <1 2003 40438 1-4 2001 174346 1-4 2002 170191 1-4 2003 167223 5-9 2001 247071 5-9 2002 242548 5-9 2003 237816 What I am trying to do is get one result set with Population and Cases together like the following AgeGroup YEAR CASES POPULATION <1 2001 9 40686 <1 2002 32 39768 <1 2003 10 40438 1-4 2001 13 174346 1-4 2002 11 170191 1-4 2003 23 167223 5-9 2001 13 247071 5-9 2002 14 242548 5-9 2003 34 237816 Any help would be greatly appreciated. Thanks!
View Replies !
|
TRACKER
