I am querying my dBase "Reservations" to obtain a range of dates for rooms in our hotel that are available for a vacationer to reserve...
this is the my present SQL structure (two tables(roomReserve & roomDescribe inner joined) ********************** SELECT roomReserve.availibility, roomDescribe.numRooms
FROM roomDescribe INNER JOIN roomReserve ON roomDescribe.code = roomReserve.code
WHERE (((roomReserve.availibility) Between 8/20/99 And 8/23/99) AND ((roomDescribe.numRooms)>0));
**************************** The problem is that I do not want any room open dates (roomReserve.Availibility) to come up if any of the days in between the date range have 0 rooms available (in other words no room available on that date) (roomReserve.numRooms)>0
But they do because the query is not correctly written to be an all or none recordset for the date range time peroid…
For example, if I query for the date range of 8/20/99 to 8/23/99 using a criteria that the room must be available (>0), and let's say the date 8/21/99 has 0 rooms available for the King Fireplace room, then the query's results set gives me a list of the other 3 dates (8/20, 8/22, 8/23) for when the King Fireplace room is available...
I don't want anything thing for that date range to come out if any of the dates (within that range 8/20-8/23) have a room status = 0 during that time period...
I need a way to receive an all or none recordset result... with only one query statement…
I do not want to have to query a query… if that's at all possible…
I just started working with MS SQL. I'm attempting to find the equivalent of the MySQL commands STATUS and SHOW STATUS.
Essentially, I want to connect to the database from a .net app, check the status, such as how many connections, table locks, errors, etc... and display that information on my application.
Ok in Replication for sql 2000,' what happens when a query such as 'update tableA set a=1 where id=5' affects 1 row. What will happen when it does not affect any row.
Somebody can give some tips or hints for speed up my querys and procedures from sql and get more performance, what is best to use, joins o cursors, using cursors can give me more performance?
I've got a trouble with my query and left outer joins. I've got 2 tables, table A and B, both have the same record called ID. I used this query: SELECT * FROM A LEFT OUTER JOIN B ON A.ID = B.ID
This is OK and works fine, my trouble comes when i have a duplicated ID on both A and B. Instead of return 2 fields, it returns me 4.
Is there some way to force SQl server to return only the first founded on B, but the 2 duplicated IDs on the A table?
I have two databases with table structures exactly the same. The query in one database works and returns correctly. The same query (copied and pasted) in another database returns the correct records but completely ignores the order by. Even stranger, when you are creating the query under modify and run it, it is fine. But when I right-click on the query and go to 'Open View' then the query does not return correctly. Below is the query:
SELECT TOP (100) PERCENT pf.description AS incentive, CAST(i.targetc AS FLOAT) AS targetc, i.targetp, b.basis, d.description, i.iSelect, i.direction, pf.pValue, pf.pFigure FROM dbo.PerfFactorWeights AS pfw INNER JOIN dbo.PerfFactors AS pf ON pfw.incentiveID = pf.incentiveID INNER JOIN dbo.Incentive AS i ON pfw.incentiveID = i.incentiveID AND pfw.basis = i.basisID INNER JOIN dbo.Basis AS b ON i.basisID = b.basisID INNER JOIN dbo.Division AS d ON pfw.division = d.uniqueID ORDER BY incentive, d.uniqueID, b.basis
Hi there!I'm programming in delphi and new to querieng MS SQL Server. Is there a wayto monitor the queries sent to the server and something of the returns. Atleast execution time would be interesting ...Thx in advance,Fritz
Someone asked me a curious question.Can I execute a query to sql server from a bat file? how?If not is there a simple scripting laguage that this person might use todrive his process that is similar to a dos bat file?
Hello,Could some clarify for me? I’ve been told that I should add lock statements to my database write querys (update, delete, insert). I’m not sure If it’s necessary though. The only documentation I’ve found so far is how to implement a lock statement with threading but I’m not using the threading namespace. What best practice and how would I implement this without threading or should I be using threading?Thanks in advance!
I'm looking for a solution to run querys (create databases ed) after a silent installation of SQL 2005 Express edition (example with a .sql file). So users can run a installation unattended with all databases installed and so on.
What I got is a document about how to run a silent installation (document " Using Command Prompt Options to install SQL Server Express") but this docs there's no solution how to run querys after the installation.
This is driving me a little nuts, I have been testing locally with SQL Server Express 2005 and this has not been a problem, however now that I have restored the databases to SQL Server 2005 (not express) When I go into SQL Server Management Studio open a table the option for changing a query type is greyed out has anyone seen this or have any ideas how I can get this working. Thanks, Terry
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB 123 colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly..Below is what I have:
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.
declare @start smalldatetime declare @end smalldatetime set @start = '2015-03-22 00:00' -- this should be a Sunday set @end = '2015-03-28 23:59' -- this should be the following Saturday
In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.
Hi All, I need to For Each loop tied back to a SQL result set. This is what I have so far, I know its not much but I'm stuck. Thanks soooo much!TimDim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionstring As String = ConfigurationManager.ConnectionStrings("Magic").ConnectionStringconn = New SqlConnection(connectionstring)comm = New SqlCommand("Select Distinct([_Group_]) from _Smdba_._Groupdet_ Where [_InActive_:] = 0", conn) Tryconn.Open()reader = comm.ExecuteReader() For Each (FOR EACH SQL RECORD) 'I'm going to put some code in here.... reader.Close()Catch dberrorlabel.Text = "Error Loading Admin Panel"Finally conn.Close()End Try
In 6.5 I ran a SELECT query with a specific WHERE clause. It gave me a result set of 430 rows. But when I use the same conditions in WHERE clause in UPDATE, it updated only 420.
YOU ALL REQUESTED TO HELP ME TO COMEOUT FROM PROBLEM : I HAVE A TABLE NAME PLUCK IN WHICH DATA STORE: MONYEAR EMPCODE PDATE(ddmmyyy) JOBCODE HCD kg 022006 A01608P 01/02/2006 LA N 0 022006 A01608P 02/02/2006 LA N 0 022006 A01608P 03/02/2006 LA N 0 022006 A01608P 04/02/2006 LA N 0 022006 A01608P 05/02/2006 LH N 0 022006 A01693P 01/02/2006 PL N 21 022006 A01693P 02/02/2006 PL N 25 022006 A01693P 03/02/2006 PL N 15 022006 A01693P 04/02/2006 PL N 25 022006 A01693P 05/02/2006 LH N 0
BUT I WANT REPORT LIKE : 022006 A01608P LA N LA N LA N LA N LH N 022006 A01693P 21 N 25 N 15 N 25 N LH N
(IF JOBCODE = PL THAN INSTEAD OF JOBCODE KGPLUCK IS SHOWN..)
Pls guide how to write the SQL to achive the report...
Thanks
ASM
---sample data insert USE NORTHWIND CREATE TABLE PLUCK (MONYEAR CHAR(6), EMPCODE CHAR(7), PDATE SMALLDATETIME,JOBCODE CHAR(2), HCD CHAR(1), KGPLUCK INT)
INSERT INTO PLUCK VALUES('022006', 'A01608P','01/02/2006','LA','N',0); INSERT INTO PLUCK VALUES('022006', 'A01608P','02/02/2006','LA','N',0); INSERT INTO PLUCK VALUES('022006', 'A01608P','03/02/2006','LA','N',0); INSERT INTO PLUCK VALUES('022006', 'A01608P','04/02/2006','LA','N',0); INSERT INTO PLUCK VALUES('022006', 'A01608P','05/02/2006','LH','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01693P','01/02/2006','PL','N',21); INSERT INTO PLUCK VALUES('022006', 'A01693P','02/02/2006','PL','N',25); INSERT INTO PLUCK VALUES('022006', 'A01693P','03/02/2006','PL','N',15); INSERT INTO PLUCK VALUES('022006', 'A01693P','04/02/2006','PL','N',25); INSERT INTO PLUCK VALUES ('022006', 'A01693P','05/02/2006','LH','N',0);
Dear All,Can we do multiple result row as a single row while selection ..?Request you to provide the SQL if we can achieve this...For Eg,Table Name = Employeeid - EmployeeName - addresstype - address1 - address2 - City1 - AAA - permanent - 231 - First Street - XYA2 - AAA - Temporary - 343 - Second Street - XYAExpecting Result as a Single row as below,id - EmployeeName - Address1 - Address21 - AAA - 231/First Street/XYA - 343/Second Street/XYAThanks in advanceRgds,Ganapathi sundaram.G
Hello,Is there a way to merge the result of a query into one row?For example, let say I want the a list of name of people of 20 year soldMy result will look like that: NAME1 john2 Mike3 Craig I would like to have the result in one row: NAME1 John, Mike, CraigIs there a way to do that?Thanks
Dear Experts Here is my code: SqlConnectioncn = new SqlConnection(ConfigurationManager.ConnectionStrings["SimvipConnectionString"].ConnectionString); protected void Page_Load(object sender, EventArgs e) { SqlCommand cmd = new SqlCommand("SP_SEARCH", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@TypeOfSim", Request.QueryString["NID"])); cmd.Parameters.Add(new SqlParameter("@CategoryID", Request.QueryString["CID"])); cmd.Parameters.Add(new SqlParameter("@Price", Request.QueryString["L"])); cmd.Parameters.Add(new SqlParameter("@Price2", Request.QueryString["S"])); cmd.Parameters.Add(new SqlParameter("@Title", Request.QueryString["keyword"])); cmd.Connection.Open(); cmd.ExecuteNonQuery(); SqlDataAdapter sqlAdapter1 = new SqlDataAdapter(); sqlAdapter1.SelectCommand = cmd; DataSet ds = new DataSet(); sqlAdapter1.Fill(ds, "ds");
CollectionPager1.DataSource = ds.Tables["ds"].DefaultView; CollectionPager1.BindToControl = DataList1; DataList1.DataSource = CollectionPager1.DataSourcePaged; } My problem is when there is no result(no item in database), the DataList1 will display the text "some text will display here" instead of empty page. In FormView I know the option <EmptyDataTemplate>. But in the DataList I don't know how to do this. I am a beginner. Can anybody show me how....Thanks a lots
I have an account field which has datatype string. I want to only get those values which are in between 0-199. I used select sum(t.a_trans_amt) Credit from a_account a, a_transaction t where a.a_account_num between '0' and '199'and t.a_account_id=a.a_account_id and t.a_debit_credit_ind='C'but this query also including those values which have starting 3 digit in between 0-199. I don't know how to fix this problem . Can anybody help me on this issue. Thanks in Advance.
Hi y'all, I have a problem in my query I think maybe you guys could help.I have this query:SELECT dbo.BOS_GL_Workplace.WpszState, Emp2.szName, Emp1.szName AS szEmpName, Emp1.szEmployeeId, dbo.BOS_PI_Division.szName AS Divisi, dbo.BOS_PI_Team.szDescription,dbo.BOS_SD_Route.szRouteId, dbo.BOS_SD_Route.szDescription, dbo.BOS_SD_Route.szScheduleId, dbo.BOS_SD_RouteItem.szCustId, dbo.BOS_SD_Route.szOpUserId,dbo.BOS_AR_Customer.szCustId ,dbo.BOS_AR_Customer.szName, dbo.BOS_AR_Customer.CustszAddress_1 AS SendAddres, dbo.BOS_AR_Customer.szTaxId, dbo.BOS_AR_Customer.CustszAddress_1 AS CollectAddress, dbo.BOS_AR_Customer.CustszCity, dbo.BOS_AR_Customer.CustszZipCode, dbo.BOS_AR_Customer.szStatus, dbo.BOS_AR_CustSales.szPaymentTermId, dbo.BOS_AR_CustSales.decCreditLimit,BOS_AR_Customer.dtmStart,BOS_AR_Customer.dtmLastUpdated FROM dbo.BOS_PI_Employee Emp1 LEFT OUTER JOIN BOS_PI_Employee Emp2 on Emp1.szSupervisorID = Emp2.szEmployeeID Left outer join dbo.BOS_GL_Workplace ON Emp1.szWorkplaceId = dbo.BOS_GL_Workplace.szWorkplaceId LEFT OUTER JOIN dbo.BOS_PI_Division ON Emp1.szDivisionId = dbo.BOS_PI_Division.szDivisionId LEFT OUTER JOIN dbo.BOS_PI_Team ON Emp1.szTeamId = dbo.BOS_PI_Team.szTeamId LEFT OUTER JOIN BOS_SD_Route on Emp1.szEmployeeId = BOS_SD_route.szOPuserId LEFT OUTER JOIN BOS_SD_RouteItem on BOS_SD_Route.szRouteId = BOS_SD_RouteItem.szRouteID LEFT OUTER JOIN BOS_AR_Customer on BOS_SD_RouteItem.szCustID = BOS_AR_Customer.szCUstID LEFT OUTER JOIN BOS_AR_CustSales on BOS_AR_Customer.szCustId = BOS_AR_CustSales.szCustID --order by BOS_PI_Employee_1.szNameWhen I query it's working fine the results is the way as I expected. But when I add where--and--clause like this :WHERE Emp2.szName >= 'JACK ANDREWS' AND Emp2.szName <='RICK COLLINS'it's only showing the header and no result at all, why ??? can you guys help me please, I appreciate any kinds of help. Thanks. FYI: I use SQL Server 2000 Best Regards.
