Should BEGIN TRAN ...COMMIT be used in a procedure body if I have only select statements??
CREATE PROCEDURE [dbo].[procname]
@param1int
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
[code]....
I am running an Execute SQL task that does a Begin Tran, then the next task in the sequence is a data task which imports a XML file into two tables. If i doo a Rollback Tran only one of the two tables is rolled back.
Is it possible to have both tables rolled back from one Begin tran command or do i need to split the datatasl into two and treat each import as a seperate issue ?
The connection is set to retainsameconnection
thanks
Hi,
I want to rollback my t-sql if it encounters an error. I wrote this code:
begin tran mytrans;
insert into table1 values (1, 'test');
insert into table1 values (1, 'jsaureouwrolsjflseorwurw'); -- it will encounter error here since max value to be inputted is 10
commit tran mytrans;
I forced my insert to have an error by putting a value that exceeds the data size. However, I didn't do any rollback. Anything i missed out?
cherriesh
I'm all of a sudden getting this error on a Stored Procedure that has not been touched since it was created.
Msg 266, Level 16, State 2, Procedure usp_ArchivexactControlPoint, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
CREATE PROCEDURE [dbo].[usp_ArchivexactControlPoint]
AS
DECLARE @TableName VARCHAR (50)
[Code] ...
Hallo All,
Can somebody explain why the same function works different with MS SQL 2000 and MS SQL 2005?
On both systems 2000 and 2005 I have 2x databases named ACCT and PROD (actually only a test environment).
On both systems I try to execute the following statements:
BEGIN TRAN TRAN_01 WITH MARK 'My TRAN_01'
USE PROD
INSERT INTO [PROD].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
USE ACCT
INSERT INTO [ACCT].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
COMMIT TRAN TRAN_01
After executing the statements I start the following query:
SELECT * FROM [msdb].[dbo].[logmarkhistory]
On MS SQL 2000 I get as results:
PROD TRAN_01 My TRAN_01 SUPPORTAdministrator 3944000000107600001 2006-03-29 17:15:13.930
ACCT TRAN_01 My TRAN_01 SUPPORTAdministrator 8000000009200001 2006-03-29 17:15:13.930
Seems to be correct. I think it is the way it should work according to the documentation.
On MS SQL 2005 I only get the following results:
PROD TRAN_01 My TRAN_01 SU 29000000107800001 2006-03-29 17:31:32.283
There are no entries in the table for the ACCT database and the account / user_name is shown incorrectly.
It seems to be an ERROR in the processing of such marked transactions in MS SQL 2005.
we have an update sp that must call an insert sp after the update. The update and insert must act like a transaction, ie all or none.
We believe that wrapping most of the update (including call to insert sp) sp in a begin tran block would guarantee the all or none behavior.
However, we're not sure what would happen if one of our developers calls the update sp from within a transaction scope that expects yet something additional to be included in the transaction. Would the begin tran block (assuming no errors in that block) in the sp commit both the update and insert regardless of what happens in the rest of the .net tran scope?
I ran the below 2 select statements and ended up seeing multiple cached instances of the same stored procedure. The majority have only one cached instance but more than a handful have multiple cached instances. When there are multiple cached instances of the same sproc, which one will sql server reuse when the sproc is called?
SELECT o.name, o.object_id,
ps.last_execution_time ,
ps.last_elapsed_time * 0.000001 as last_elapsed_timeINSeconds,
ps.min_elapsed_time * 0.000001 as min_elapsed_timeINSeconds,
ps.max_elapsed_time * 0.000001 as max_elapsed_timeINSeconds
[code]...
I am getting an exception. And i don't know whether this exception is from the C# coding or SQL SERVER."Cannot roll back InsertRxs. No transaction or savepoint of that name was found.Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back."
View 2 Replies View RelatedI created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
I am looking for a plugin or way of detecting an open tran in SSMS that is visible indicator,
I know of DBCC OPENTRAN
Select @@trancount
SSMS know you have an open tran when you attempt to close your SQL query window. Just looking for that to show or alert before that.
The context behind this is you given a script and its run on SQL and for some reason the tran is not committed. or rolled back.
Hi
I have a problem executing a SELECT inside a TRAN against other computer
For example:
IN THE SQL Query Analizer of the COMPUTER2
1) this runs OK
BEGIN TRAN
SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE
COMMIT TRAN
2) this runs OK
SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE
3) this runs OK
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
4) this runs bad
BEGIN TRAN
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
COMMIT TRAN
The problem is that TABLE locks and it does not finish.
I've been looking for similar ERRORS in Microsoft Support but I found nothing
I've uninstall and install de SQL server 2000 SP4 and the problems continues the same
Please, someone could help me, thanks
App developers are complaining that a blocking report I sent them today does not have enough info to troubleshoot and fix the root cause of the blocking.(I used SQL Sentry to get the report and send to the users, but I think my question is more fundamental than just using one 3rd-party tool.)The reason for the complaints is that the top blocker is a SELECT stmt on TableA that is not the Wait Resource of the blocking (TableB).
I explained that this is likely because the "blocking" SELECT stmt on TableA is part of a larger explicit tran that includes TableB, and that they need to look through their code to identify the SQL stmt that is the root cause of the blocking.They would like that root statement identified in the blocking report, but I don't think it is possible to get to that through DMVs.
We have a rather large environment and have just a couple of boxes out there that we are getting cannot begin distributed transaction on inserts and updates but works fine on selects. Inserts and updates work fine outside the begin tran / commit so it's definitely DTC
We have checked the configuration on and the source box is set to No authentication required same for destination.
We have: Verified credentials running the service, changed them, same problem. Uninstalled and re-installed MSDTC per Microsoft instructions.
Have run all the tools for checking DTC DTCPing etc and followed those procedures which typically in the past has resolved any DTC issues. Other than swapping out the offending pc for a new one we are at a loss.
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 an interesting problem. A number of spids are being blocked by a single select statement. The select statement is the same as returned from sp_who2, sysprocesses, sp_whoisactive of dbcc inputbuffer. It is not waiting on anything and has status as sleeping.
Clearly it is not 'just' a sleeping select statement as I can see over a thousand locks held by the spid on 2 user databases and tempdb. I'm working on the theory there is a begin transaction with a bunch of statements and no closing commit. But I want to be able to prove that. How can I show what statements were previously executed as part of this transaction?
Additional Info: SQL 2012 Enterprise Edition. This is a test server but is a reproduction of a live issue. At this point the application team cannot isolate the code causing the problem, only the set of processes the code resides in.
I get the error:
(0 row(s) affected)
Msg 208, Level 16, State 1, Line 41
Invalid object name 'X_SET_PREOP'.
FOR THE FOLLOWING CODE SEGMENT.. I am trying to do 2 updates with just one WITH BLOCk.Create table #temp( MPOG_CASE_ID uniqueidentifier, lab_name varchar(100), lab_date datetime, lab_value decimal(19,2) );
with X_SET_PREOP as
(
SELECT
xx= ROW_NUMBER() OVER ( PARTITION BY lab.MPOG_Case_ID, lab.lab_name ORDER BY lab.lab_date DESC ),
lab.MPOG_Case_ID,
lab.lab_name,
lab.lab_value,lab.lab_date
FROM
MPOG_Research..ACRC_427_lab_data lab
[code]....
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 have a SP returning the following result The select statement for this is
Code:
SELECT dbo.TEST1.[OFFICE NAME], COUNT(dbo.TEST1.[ACCOUNT ID]) AS AccountCount
FROM dbo.Test2 INNER JOIN
dbo.test3 INNER JOIN
dbo.Test4 ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code] INNER JOIN
dbo.TEST1 ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID] ON dbo.Test2.[Model ID] = dbo.test3.ID INNER JOIN
dbo.[Inquiry Details] ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID
WHERE (dbo.Test2.InquiryDate BETWEEN CONVERT(DATETIME, @startDate, 102) AND CONVERT(DATETIME, @endDate, 102)) AND dbo.Test1.[Account ID] IN(SELECT [account id] FROM test5 WHERE [Contact ID] = @contactId)
GROUP BY dbo.TEST1.[OFFICE NAME]
ORDER BY COUNT(dbo.TEST1.[ACCOUNT ID]) DESC name id count case1 226 320 case2 219 288 case3 203 163 case4 223 90 case5 224 73 i have another select stnat which returns like this The select statement is
Code:
Select test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code] Group by test1.[Office Name] order by count(test1.[office name]) DESCname count case6 10 case2 56 case4 66 case1 74 case3 88 case7 100 case5 177 How can i combine this select stament with the SP, so that, i get a fourth column with case1 226 320 74 case2 219 288 56 .......................... ........................... Hope i am not confusing you all Please help me, if someone knows how to combine this? Thanks
I 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
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 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!
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.
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
*************
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