Is It Possible To Combine 2 SELECT Statements??
Jan 28, 2005
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 4 Replies
ADVERTISEMENT
Sep 17, 2007
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 13 Replies
View Related
Jun 2, 2004
SELECT bms_id,email_address,COUNT(*)
INTO #temp
FROM emp_db
WHERE email_address IS NOT NULL
GROUP BY bms_id,email_address
ORDER BY bms_id DESC,COUNT(*) DESC
SELECT bms_id COUNT(*)
FROM #TEMP
GROUP BY bms_id
ORDER BY COUNT(*) DESC
How can i put these two statements into a single sql statement.
Thanks.
View 3 Replies
View Related
Oct 18, 2006
I have the following 3 SQL statements that need to be combined, ifpossible. The output of one feeds the input of the next. I need to viewall of the defined output fields (the output needs to be used in aCrystal Report).The SQL Follows:/* Input is ISBN (vendor_part_number) */QUERY_1 - returns 1 recordselect p.product_id, v.name, m.description, p.author, p.title,p.revision_number, p.copyright_edition, p.vendor_part_number,p.conforming_flag,m.code, mp.unit_price_product, mm.quota_pricefrom T_PRODUCT p, T_VENDOR v, T_PRODUCT_VENDOR pv,T_MULTILIST_PRODUCT mp, T_MULTILIST m,T_MULTILIST_MEMBERSHIP mm where/* p.vendor_part_number == input */p.vendor_part_number = '0153364475' and p.medium_type ='TEXTBOOK' andp.product_id = pv.product_id and pv.type = 'CONTRACT' andpv.vendor_id = v.id andp.product_id = mp.product_id andm.code = mp.multilist_code and m.proclamation_year =mp.proclamation_yearand m.proclamation_seq_id = mp.proclamation_seq_id andm.code = mm.multilist_code and m.proclamation_year =mm.proclamation_yearand m.proclamation_seq_id = mm.proclamation_seq_id/* The above should return a single record */QUERY_2 - returns 2 recordsselect p.product_id, p.consumable, p.title, p.copyright_edition,p.vendor_part_number, p.product_type,p.item_type, p.hardware_requiredfrom T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type ='AID'or pr.relationship_type = 'KIT') andp.product_id = pr.child_product_id and pr.parent_product_id =90321/* 90321 = result from above: pr.parent_product_id = p.product_id*/QUERY_3 - returns 18 recordsselect p.product_id, p.consumable, p.title, p.copyright_edition,p.vendor_part_number, p.product_type,p.item_type, p.hardware_requiredfrom T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type ='AID'or pr.relationship_type = 'KIT') andp.product_id = pr.child_product_id and pr.parent_product_id in(90322, 90323)/* 90322, 90323 = result from QUERY_2: pr.parent_product_id =p.product_id */Only 21 records are returned from these combined queries. I need accessto all of them even though there are 3 different resultsets, 2 of whichcontain the same fields. Is there a way to simplify this into a storedprocedure or a view that can take 1 input parameter? It needs to beused in a Crystal Report, which is limited in its handling of thesetypes of complex queries.
View 1 Replies
View Related
Jan 10, 2007
Hi guys! Is there a way to combine these update statements?
Dim update_phase As New SqlCommand("INSERT INTO TE_shounin_zangyou (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_zangyou WHERE [syain_No] = @syain_No", cnn)
Dim update_phase2 As New SqlCommand(" UPDATE TE_shounin_zangyou SET " & " phase=2, phase_states2=06,syounin2_sysd=CONVERT(VARCHAR(10),GETDATE(),101) WHERE [syain_No] = @syain_No", cnn)
The same table is updated so I think it would be better to have just one update statement. But the problem is that, the first update statement retrieves values from another table, whereas the update values of the second statement is fixed. Is there a way to combine these two statements. I tried to do so but it does not update. Here's my code...
Dim update_phase As New SqlCommand("UPDATE TE_shounin_zangyou SET TE_shounin_zangyou.syain_No=TE_zangyou.syain_No, TE_shounin_zangyou.date_kyou=TE_zangyou.date_kyou, TE_shounin_zangyou.time_kyou=TE_zangyou.time_kyou FROM TE_zangyou WHERE TE_zangyou.syain_No = TE_shounin_zangyou.syain_No", cnn)
Please help me. Thanks.
Audrey
View 1 Replies
View Related
Mar 13, 2008
hello gang, Is it possible to combine sql update statements? something like:
UPDATE table_nameSET column_name = new_valueWHERE column_name = some_valueANDSET column_name = new_valueWHERE column_name = some_other_value
View 4 Replies
View Related
Jun 18, 2008
I have two SQL queries that I would like to combine. Each query is dependent on the same table, and the same rows, but they each have their own WHERE statements. I've thought about using some JOIN statements (left outer join in particular) but then I run into the problem of not having two separate tables, and don't see where I can put in two separate WHERE statements into the final query. I've read into aliasing tables, but I'm not quite sure how that works (how to put it into code or a JOIN statement) , or if it would solve my question. Do you have any ideas or examples of how to solve this scenario?
View 9 Replies
View Related
May 13, 2004
SELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp
SELECT 1 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id
View 9 Replies
View Related
Mar 27, 2002
I am looking for the correct T-sql statement. I want to put parameters in a SP from a select statement. And make the SP exec for each records that the select statement returns. The following is the SP that I want to pass the parameters.
CREATE PROCEDURE sp_sendSMTPmail (@To varchar(8000),
@Subject varchar(255),
@Body text = null,
@Importance int = 1, -- 0=low, 1=normal, 2=high
@Cc varchar(8000) = null,
@Bcc varchar(8000) = null,
@Attachments varchar(8000) = null, -- delimeter is ;
@HTMLFormat int = 0,
@From varchar(255) = null)
/* Name: sp_sendSMTPmail
Purpose: Send an SMTP mail using CDONTS object.
Requirements: SMTP server (comes with IIS but doesn't require IIS) must be loaded.
Returns: 0 if successful, 1 if any errors
Sample Usage:
sp_sendSMTPmail 'vince.iacoboni@db.com', 'Testing', 'testing sp_sendSMTPmail, please reply if you receive this',
@cc='irmsqlmail@db.com',
@Importance=1,
@Attachments='c:oot.ini;c:autoexec.bat'
History:
02/07/2001 VRI Created.
*/
AS
SET NOCOUNT ON
DECLARE @object int,
@hr int,
@StrEnd int,
@Attachment varchar(255),
@return int,
@Msg varchar(255)
SELECT @From = isnull(@From, @@SERVERNAME)
-- Create the CDONTS NewMail object.
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
IF @hr <> 0 GOTO ObjectError
-- Add the optional properties if they are specified
IF @Body IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Body', @Body
IF @hr <> 0 GOTO ObjectError
END
IF @Cc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Cc', @Cc
IF @hr <> 0 GOTO ObjectError
END
IF @Bcc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc
IF @hr <> 0 GOTO ObjectError
END
IF @HTMLFormat <> 0
BEGIN
EXEC @hr = sp_OASetProperty @object, 'MailFormat', 0
IF @hr <> 0 GOTO ObjectError
END
-- Loop through the ; delimited files to attach
CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)
WHILE isnull(len(@Attachments),0) > 0
BEGIN
SELECT @StrEnd = CASE charindex(';', @Attachments)
WHEN 0 THEN len(@Attachments)
ELSE charindex(';', @Attachments) - 1
END
SELECT @Attachment = substring(@Attachments, 1, @StrEnd)
SELECT @Attachments = substring(@Attachments, @StrEnd+2, len(@Attachments))
-- Ensure we can find the file we want to send.
DELETE #FileExists
INSERT #FileExists
EXEC master..xp_fileexist @Attachment
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
RAISERROR ('File %s does not exist. Message not sent.', 16, 1, @Attachment)
RETURN 1
END
EXEC @hr = sp_OAMethod @object, 'AttachFile', NULL, @Attachment
IF @hr <> 0 GOTO ObjectError
SELECT @Msg = 'File ' + @Attachment + ' attached.'
PRINT @Msg
END
-- Call the Send method with parms for standard properties
EXEC @hr = sp_OAMethod @object, 'Send', NULL, @From, @To, @Subject, @Importance=@Importance
IF @hr <> 0 GOTO ObjectError
-- Destroy the NewMail object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0 GOTO ObjectError
PRINT 'Message sent.'
RETURN 0
ObjectError:
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN 1
END
GO
View 1 Replies
View Related
Aug 20, 2007
I would appreciate any help with my following problem... lets say
i have...
select A.firstname + '' + B.lastname as fullname, 'Their Home is ' + A.City + ' ' + (select top 1 C.State from States C where C.City = A.City) as Location
from tableA A, TableB B
Where A.id = b.id
This is not the actual statement but follows the same kinda logic... the problem that i get is that some of the rows in both my fullname column and in my location column show up as null... how would i fix it so for instance even if the state is missing it would still show: their home is LA or if just the last name is available it would show the lastname?
Thank you
View 2 Replies
View Related
Apr 28, 2008
Hi
I have a staff table and it has columns like firstname, lastname etc
I did the query and it works. but firstname and lastname are too close
SELECT (FirstName + Lastname) as fullname
FROM StaffList
I need the format "firstname , lastname " so I write the second query
SELECT (FirstName + " , " + Lastname) as fullname
FROM StaffList
But it doesn't work.Please help me and let me know how to make the second query work.
Thanks a lot
Mark
View 2 Replies
View Related
Jun 21, 2006
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 ')
View 3 Replies
View Related
Jan 1, 2007
Is it possible to combine fields and text in a select statement?
In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:
SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe
FROM GE_Data
WHERE (DIST = @DIST)
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.
If this is possible, or there's a better way to do it, I'd love some more info.
Thanks!
-Mathminded
View 7 Replies
View Related
Jan 16, 2007
Hello, how can i merge together several select statements?
I have something like this:
CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data
View 5 Replies
View Related
May 12, 2008
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 1 Replies
View Related
Sep 13, 2007
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 4 Replies
View Related
May 13, 2008
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 5 Replies
View Related
Jul 26, 2006
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 7 Replies
View Related
Apr 2, 2007
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 2 Replies
View Related
Feb 4, 2008
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 5 Replies
View Related
Apr 23, 2008
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 10 Replies
View Related
Jun 6, 2008
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 5 Replies
View Related
Apr 1, 2004
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 1 Replies
View Related
Dec 22, 2005
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 4 Replies
View Related
Mar 31, 2003
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 2 Replies
View Related
Sep 5, 2006
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 1 Replies
View Related
Jan 26, 2005
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 3 Replies
View Related
Aug 19, 2004
Hi All,
I want to find difference of two Select statement in SQL Server 2000. As in Oracle we have "MINUS". Do we have the same thing in SQL Server also.
Thanks with Regards.
-Mohit.
View 2 Replies
View Related
Apr 23, 2008
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 2 Replies
View Related
Dec 22, 2014
how can i join these 2 queries to produce 1 result
Query 1:
select R.Name, T.Branchid, t.TradingDate,t. TransactionDate,
convert(varchar,T.Tillid)+'-'+convert(varchar,t.Saleid) as DocketNumber,
t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount as CommissionAmt
from TransactionHeader T
join SalesRep R on
R.SalesRepid = T.SalesRepid
where T.SalesRepid is not null
Query 2 :
select C.TradingName,C.AccountNo
From Sale S
Join ClMast c on
C.Clientid = s.CustomerAccountID
The result should be R.Name,T.Branchid, t.TradingDate,t. TransactionDate,DocketNumber,t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount, TradingName,Accountno..Field Saleid is present in Transactionheader Table and Sale table
View 5 Replies
View Related
Aug 29, 2007
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 14 Replies
View Related
Jul 20, 2005
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 2 Replies
View Related
Mar 5, 2008
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 10 Replies
View Related
