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
[Code] .....
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
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
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
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 2 Replies View RelatedHello,
I was wondering what is the best way to have multiple joins?
Here are the two statements I've been trying to combine
--------------------------------------------------------------------
SELECT CASE when
t1.Cust_DB_Shipment_Key = 'Used:' Then Description_1
Else (stuff(t1.Cust_DB_Shipment_Key,1,5,''))
End,
t1.Airway_Bill_No,
t1.Shipper_Reference,
t1.External_Product_Cd,
t1.Chargeable_Weight,
dt.CountTrackingNumber,
dt.SumProductCharge,
t1.Consignee_Company_Name,
CONVERT(CHAR(8),Ship_DT,112)
FROM Shipping t1
Inner Join
(
SELECT Cust_DB_Shipment_Key,
sum(PRODUCT_CHARGE_AMOUNT) as [SumProductCharge],
count(Airway_Bill_No) as [CountTrackingNumber]
FROM Shipping
Where Ship_DT = '2008-05-12' and status != 'voided'
GROUP BY Cust_DB_Shipment_Key
) dt
ON (stuff(t1.Cust_DB_Shipment_Key,1,5,'')) =
(stuff(dt.Cust_DB_Shipment_Key,1,5,''))
Where Ship_DT = '2008-05-12' and status != 'voided'
----------------------------------------------------------------------
SELECT CASE when
Cust_DB_Shipment_Key = 'Used:' Then Description_1
Else (stuff(Cust_DB_Shipment_Key,1,5,''))
End,
Airway_Bill_No,
Shipper_Reference,
Service_Name,
Chargeable_Weight,
Consignee_Company_Name,
CONVERT(CHAR(8),Ship_DT,112)
FROM Shipping
s JOIN Shipping..Distinct_Service_by_SAS_Code d ON s.External_Product_CD = d.SAS_Code
Where Ship_DT = '2008-05-12' and status != 'voided'
Thanks,
Stephen
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
View 5 Replies View RelatedIn 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?
Gerrit
Hi can someone help me?
I'm using Access database and Microsoft VS2005 (VB)
I need to take data from 2 different tables in 1 database. But i can't seems to get it right!
I have: - dbWarehouse.mdb- tblReadDetails and tblStock
I need to use "Barcode" from tblReadDetails, compare it with "Barcode" in tblStock then retrieve all the column in tblStock and put it in a webpage.aspx list box.
I tried the following code...cn.ConnectionString = strConncn.Open()cmd = New OleDbCommand("SELECT tblStock.Barcode, tblStock.Item, tblStock.Dept, tblReadDetails.Barcode FROM tblStock JOIN tblReadDetils WHERE tblReadDetails.Barcode = tblStock.Barcode", cn)dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)While dr.Read()lstBarcode.Items.Add(dr2.Item("tblStock.Barcode"))lstItem.Items.Add(dr2.Item("tblStock.Item"))lstDept.Items.Add(dr2.Item("tblStock.Dept"))End While
but it prompt me the following error...
OleDbException was unhandled by user codeSyntax error in FROM clause
What's wrong with this code?
Regards and thanks!
I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:
Jobs Complete Jobs completed within 5 days
10 5
-------------------------------------------------------------------------------------------------
SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)
---------------------------------------------------------------------------------------
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 ')
Folks
I have two select statements which gives two counts ie two numbers.
select 'count' = count(*) from account
select 'count1' = count(*) from employee
I want to combine these two select statements and write one select statement where I can get two columns 'count' and 'count1' with the respective values.
The result should be EX:
count count1
3 5
Thanks
Folks
I have three select statements. I want to display q_text based on
the respective where condition. How do i combine these three and write
as one select statement.
select q_text Questions from question
where new_account_flag = '1'
select q_text Questions from question
where disc_account_flag = '1'
select q_text Questions from question
where disc_account_flag = '0'
Remember that all the queries returns more than 1 value.
I tried to use
select (query1),
(query2),
(query3)
but because it is returning more than one value, there is error.
Can any suggest me any other syntax??
Thanks
I have:
Dim queryString As String = "SELECT distinct [CSULOG5].[status] + [CSULOG5].[lmca_nbr] FROM [CSULOG5]" I even tried & instead of +.
but it doesn't like it. Is it legit syntax for mssqlserver?
This is a vb project aspnet
I need some help with a query . I have two tables "config" and "item".
I have table config as follows:
ConfigID
ItemID
With Values
ConfigID ItemID
14583 2776
14583 2798
14583 3112
And table item as follows:
ItemID
ItemTypeID
ItemValue
With Values
ItemID ItermTypeID ItemValue
2776 1 123
2798 2 ABC
3112 3 789
So the query:
SELECT ConfigID,
(SELECT ItemValue WHERE ItemTypeID = '1') AS Model,
(SELECT ItemValue WHERE ItemTypeID = '3') AS Minor,
(SELECT ItemValue WHERE ItemTypeID = '2') AS Customer
FROM config c, item i
WHERE ConfigID = '14583'
AND c.ItemID = i.ItemID
Produces the result:
ConfigID Model Minor Customer
14583 123 NULL NULL
14583 NULL NULL ABC
14583 NULL 789 NULL
How do I change the above query to get one row:
ConfigID Model Minor Customer
14583 123 789 ABC
Thanks for your help
Hi,
I got a really simple question here. Say I have a table with
ID, Name
1, A1
2, A2
3, A3
....
10, A10
Now I want to combine the names into another table grouped by their ID (say 1-5, 6-10), so this new table has two names instead of 10:
A1 A2 A3 A4 A5
A6 A7 A8 A9 A10
Is there a function that allows me to 'combine' the names from a select statement?
Thanks in advance,
Steven
Hi,
I am using a SP which one has lot of Joins(More than 10 tables).
For every request from user SP executed, and the DB performance got slow.
For this i planned to write a schedule which runs once in an hour to put all the results of SP in to One single table.
I thought if the user selects from single table means the performance will increase.
I don't know whether this one is a right solution.
I am very new to SQL server. Help me for this Problem.
Thanks in advance.
Hi,
I am using a SP which one has lot of Joins(More than 10 tables).
For every request from user SP executed, and the DB performance got slow.
For this i planned to write a schedule which runs once in an hour to put all the results of SP in to One single table.
I thought if the user selects from single table means the performance will increase.
I don't know whether this one is a right solution.
I am very new to SQL server. Help me for this Problem.
Thanks in advance.
I am trying to do a query like this...
UPDATE g
SET g.GroupID = gp.GroupID, g.Contact1 = members.FirstName, g.BusPhone1 = members.BusPhone, g.HomePhone1 = members.HomePhone, g.Internet1 = members.Email
FROM statelst AS g INNER JOIN
grpcon AS gp ON g.GroupID = gp.GroupID INNER JOIN
members ON gp.MemberID = members.MemberID CROSS JOIN
I have my table statelst that I want to update certain columns from the values returned by a select on the grpcon table joined to the members table.I am getting an error "Incorrect syntex near 'JOIN'.
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
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
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?
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
I have 3 table as below:
DSF
dsf_id
company_name
incorporation_date
secretary_name
ShareholdersDetails
shareholder_id
dsf_id
shareholder_name
DirectorsDetails
directors_id
dsf_id
directors_name
one company can have multiple shareholders and directors records.
i create a search query where users might search by company name, secretary name , shareholder name or directors name.
My select query is like below:
Code:
SELECT dsf.dsf_id, dsf.company_name,
dsf.incorporation_date, dsf.secretary_name,
s.shareholders_name, d.directors_name
FROM tbl_dsf dsf
LEFT OUTER JOIN tbl_directors d on dsf.dsf_id = d.dsf_id
LEFT OUTER JOIN tbl_shareholders s on dsf.dsf_id = s.dsf_id
[WHERE CONDITION]
The result for above query would be like:
Code:
abc | 1/2/1999 | william | marry | donna
abc | 1/2/1999 | william | jenna | donna
abc | 1/2/1999 | william | jolly | donna
abc | 1/2/1999 | william | marry | dolly
abc | 1/2/1999 | william | jenna | dolly
abc | 1/2/1999 | william | jolly | dolly
Is it possible to achive result as below:
Code:
abc | 1/2/1999 | william | marry,jenna,jolly | donna,dolly
Thanks
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
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
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
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
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.
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 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
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!
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!