|
I am developing developing a hostel booking system, and I am trying to come up with a query that allows me to find all the rooms that are availiable on a particular day. I have tried using the except statement but it has not work, and I have also tried using a temporary table, but that has not worked. I have also attempted to select all the rooms that are availiable on a particular day and after that I have conducted a left join on the rooms tables, so as to try identify the rooms that have null values. Does any one out there no how this could be done using SQL 2000 or access
I have included a definition of the database below.
--Stores the database for the hostel--
create database Ymcadatabase
on Primary
(Name = 'ymca_data',
Filename = 'c:\YDbase\ymca_data.mdf',
size = 5mb,
Filegrowth = 10%)
Log ON (Name = 'ymca_log',
FILENAME = 'c:\YDbase\ymca_log.ldf',
SIZE = 5mb,
FILEGROWTH = 10%)
GO
The table definitions
use Ymcadatabase
go
create table rooms
(
roomNumber char(5) Not Null,
block char(50) Not Null,
roomStatus char(10) Null,
rate money Not Null,
Primary Key (roomNumber),
)
create table guestInfo
( idNumber char(15) Not null primary key,
guestsName varchar(25) not null,
guestsurname varchar(25) not null,
gender char(10) not null,
physicaladdress varchar(30),
cellnumber varchar (15) ,
telephone varchar(15),
occupation varchar(15),
workNumber varchar(15),
sponsorsName varchar(25),
sponsorstelephone varchar(15),
)
create table users
(
userName char (15) Not Null Primary Key,
password char(8) Not null,
usertype char (10) Not Null,
)
create table sessions
( sessionNumber int Identity Not null Primary Key,
startdate smalldatetime not null,
enddate smalldatetime not null,
sessionType char(15) not null,
balance money not Null
DEFAULT '0',
roomNumber char(5) not null references Rooms (roomNumber),
idNumber char(15) Not null references guestinfo (idnumber),
)
create table accounts
(
receiptNumber int Identity Not Null primary key,
amountpaid money not Null,
datepaid smalldatetime not null,
roomNumber char(5) not null references rooms(roomNumber),
sessionNumber int Not null references sessions(sessionNumber),
paymentType char(10),
userName char(15) Not Null references users (username),
)
I have also provided some sample data below
use Ymcadatabase
go
insert into rooms(roomNumber,rate,block,roomstatus)
values('d1','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d2','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d3','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d4','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d5','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d6','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d7','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d8','$2000','d','blocked')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d9','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d10','$2000','d','blocked')
-- insert new guests--
use Ymcadatabase
go
insert into guestinfo(idNumber,guestsName,guestsurname,gender)
values('1234','chiko','shumba','male')
insert into guestinfo(idNumber,guestsName,guestsurname,gender)
values('12ex','tatenda','shumba','female')
insert into guestinfo(idNumber,guestsName,guestsurname,gender)
values('1gg234','tanaka','shumba','female')
insert into guestinfo(idNumber,guestsName,guestsurname,gender)
values('12ss34','Roxert','Hive','male')
insert into guestinfo(idNumber,guestsName,guestsurname,gender)
values('1234ds','Pooch','Adams','male')
insert into guestinfo(idNumber,guestsName,guestsurname,gender)
values('1234dsds','Bangolina','Alberts','male')
--inserts new sessions--
use Ymcadatabase
go
insert into sessions(roomNumber,startdate,enddate,sessiontype,balance,idNumber)
values('d1','9/12/1999','9/17/1999','checkdin','2700','1234dsds')
insert into sessions(roomNumber,startdate,enddate,sessiontype,balance,idNumber)
values('d2','9/2/1999','9/9/1999','booking','1500','1234ds')
insert into sessions(roomNumber,startdate,enddate,sessiontype,balance,idNumber)
values('d3','9/1/1999','9/2/1999','reservation','0','12ss34')
insert into sessions(roomNumber,startdate,enddate,sessiontype,balance,idNumber)
values('d4','9/2/1999','9/6/1999','booking','500','1234')
|
|
|
|
|
select * from rooms
where roomNumber not in (
select roomNumber from sessions
where '01092007' between startdate and enddate)
and roomstatus <> 'blocked'
order by roomNumber
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Good morning.
I converted a report from Access2k to SQL Reporting and one of the sql strings that were part of a control is giving me problems and I cannot seem to run it down. I was hoping someone on here could point out the error to me.
The error message I recieve is:
Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals boolean AND, type equals varchar.
The select statement is:
SELECT tblPatients.*, <br />
tlkpProcedures.ProcedureDescription, tblPatients.PatientNumber, tblPatients.PatientHospitalNumber, tblPatients.LastName, tblPatients.FirstName, tblPatients.MiddleInitial, tblPatients.FamiliarName, tblPatients.AddressLine1, tblPatients.AddressLine2, tblPatients.City, tblPatients.State, tblPatients.Country,tblPatients.ZipCode, tblPatients.HomePhone, tblPatients.Employed, tblPatients.WorkTitle, tblPatients.EmployerName, tblPatients.WorkPhone, tblPatients.WorkPhoneExtension, tblPatients.OKToCallWork, tblPatients.PrimaryPolicyHolder, tblPatients.DateOfBirth, tblPatients.Gender, <br />
tblPatients.RaceCode, tblPatients.SocialSecurityNumber, tblPatients.EducationLevel, tblPatients.HeightEstimateInches, <br />
tblPatients.WeightEstimatePounds, tblPatients.OverweightTimeframeID, tblPatients.MaritalStatus, tblPatients.HaveChildren, <br />
tblPatients.NumberofChildren, tblPatients.DateInfoReceived, tblPatients.CompletedBy, tblPatients.HowHeardAbout, tblPatients.SurgeonReputation, <br />
tblPatients.SurgeonOfChoice, tblPatients.ProgramReputation, tblPatients.ReputationSource, tblPatients.PreferredProcedure, <br />
tblPatients.ReasonSurgeonChoiceChanged, tblPatients.CheckForSurgeonChange, tblPatients.PatientPhysician, tblPatients.PatientPsychologist, <br />
tblPatients.CPapBiPap, tblPatients.CurrentPsychologicalCare, tblPatients.CurrentCareExplanation, tblPatients.DenialDate, tblPatients.DenialID, tblPatients.ReferToDate, tblPatients.ReferToID, tblPatients.Notes, tlkpProcedures.ProcedureDescription, tlkpReferralSources.ReferralSource, tlkpSurgeonChangeReasons.SurgeonChangeReasonDescription, tlkpOverweightTimeframe.OverweightTimeframe AS HowLongOverweight, <br />
tlkpMaritalStatus.StatusName, tlkpOutPhysicians.Title, tlkpOutPhysicians.LastName AS PhysicianLastName, <br />
tlkpOutPhysicians.FirstName AS PhysicianFirstName, tlkpOutPhysicians.MiddleInitialName AS PhysicianMiddleName, <br />
tlkpOutPhysicians.OfficeAddressLine1, tlkpOutPhysicians.OfficeAddressLine2, tlkpOutPhysicians.OfficeCity, tlkpOutPhysicians.OfficeState, <br />
tlkpOutPhysicians.OfficeCountry, tlkpOutPhysicians.OfficeZipPostalCode, tlkpOutPhysicians.OfficePhone, tlkpOutPhysicians.OfficeFax, <br />
ISNULL(tlkpBWLSStaff.FirstName, NULL) & ISNULL(tlkpBWLSStaff.MiddleInitialName, NULL) & ISNULL(tlkpBWLSStaff.LastName, NULL) AS PreferredSurgeon<br />
<br />
FROM <br />
<br />
tlkpOutPhysicians RIGHT OUTER JOIN tblPatients LEFT OUTER JOIN<br />
tlkpMaritalStatus ON tblPatients.MaritalStatus = tlkpMaritalStatus.StatusNumber ON tlkpOutPhysicians.PatientPhysicianNumber = tblPatients.PatientPhysician LEFT OUTER JOIN tlkpOverweightTimeframe ON tblPatients.OverweightTimeframeID =tlkpOverweightTimeframe.OverweightTimeframeID<br />
LEFT OUTER JOIN tlkpProcedures ON tblPatients.PreferredProcedure = tlkpProcedures.ProcedureNumber <br />
LEFT OUTER JOIN tlkpBWLSStaff ON tblPatients.SurgeonOfChoice = tlkpBWLSStaff.BWLSStaffNumber LEFT OUTER JOIN tlkpReferralSources ON tblPatients.ReputationSource = tlkpReferralSources.ReferralSourceNumber LEFT OUTER JOIN tlkpSurgeonChangeReasons ON tblPatients.ReasonSurgeonChoiceChanged = tlkpSurgeonChangeReasons.SurgeonChangeReasonID<br />
ORDER BY tblPatients.LastName, tblPatients.FirstName, tblPatients.MiddleInitial
I apologize for the mess code. I tried formatting it better with no luck.
Before the error above I was getting complaints about the table name tlkpOutPhysicians saying there was a syntax error around there, never could figure that out. The table is there along with the rest.
Thanks, hope someone can shed some light on it.
Steve Welborn
Software Engineer
Inrange Consulting
|
|
|
|
|
Try this in the FROM clause:
FROM
tblPatients
LEFT OUTER JOIN tlkpOutPhysicians
ON tblPatients.PatientPhysician = tlkpOutPhysicians.PatientPhysicianNumber
LEFT OUTER JOIN tlkpMaritalStatus
ON tblPatients.MaritalStatus = tlkpMaritalStatus.StatusNumber
LEFT OUTER JOIN tlkpOverweightTimeframe
ON tblPatients.OverweightTimeframeID = tlkpOverweightTimeframe.OverweightTimeframeID
LEFT OUTER JOIN tlkpProcedures
ON tblPatients.PreferredProcedure = tlkpProcedures.ProcedureNumber
LEFT OUTER JOIN tlkpBWLSStaff
ON tblPatients.SurgeonOfChoice = tlkpBWLSStaff.BWLSStaffNumber
LEFT OUTER JOIN tlkpReferralSources
ON tblPatients.ReputationSource = tlkpReferralSources.ReferralSourceNumber
LEFT OUTER JOIN tlkpSurgeonChangeReasons
ON tblPatients.ReasonSurgeonChoiceChanged = tlkpSurgeonChangeReasons.SurgeonChangeReasonID
P.S. You cannot format with TABS on this site. You must use spaces.
|
|
|
|
|
Thank you Michael for the reply and help. However, it's still complaining about line 1 and giving me the same error: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals boolean AND, type equals varchar.
I did replace my FROM section with what you have above. Thanks.
Steve Welborn
Software Engineer
Inrange Consulting
|
|
|
|
|
Hi Steve
The & characters are worrying me a bit. Which database product are you using? Under SQL-Server, you should be using "+" signs. Under Oracle you would normally use the pipe symbol.
Try replacing the entire list of columns in your select with "*". That will tell you whether the error comes from the column-list or from the from-clause. Once you know this, you should be able to narrow the problem down further.
Regards
Andy
|
|
|
|
|
Thanks Andy for the information. I'll give that a try.
Steve Welborn
Software Engineer
Inrange Consulting
|
|
|
|
|
I'll be darn..Andy it was the & that was giving it the problem. I changed it to + and it worked fine.
Thanks for the help guys. I appreciate it.
Steve Welborn
Software Engineer
Inrange Consulting
|
|
|
|
|
ISNULL(tlkpBWLSStaff.FirstName, NULL) &
ISNULL(tlkpBWLSStaff.MiddleInitialName, NULL) &
ISNULL(tlkpBWLSStaff.LastName, NULL) AS PreferredSurgeon
Not sure what you are trying to accomplish with this line. & is a bitwise operator, ISNULL([whatever],NULL) doesn't do anything productive that I can think of. Can you do a bitwise & with character data?
Are you trying to build a full name?
ISNULL(tlkpBWLSStaff.FirstName + ' ', '') +
ISNULL(tlkpBWLSStaff.MiddleInitialName + ' ', '') +
ISNULL(tlkpBWLSStaff.LastName, '') AS PreferredSurgeon
To debug the rest of you statement eliminate all tables/columns except tblPatients. Run the query and check results. Add in the next table and run again. In this way you will isolate the error in its most simplistic form.
Be aware that you are listing some columns more than once (a waste of network resources). tblPatients.* gets you all the columns in tblPatients. You then go and relist some columns individually (tblPatients.PatientHospitalNumber, etc.).
|
|
|
|
|
Great points. I can definitly see the wasted resources in this statement and will take your suggestion to shorten it.
I believe with the Name issue you mentioned, it was just putting the first, middle and last name together. Bare in mind that this came from an Access 97 (then converted to 2k) database report. I converted it to SQL Reporting and trying to redo all the bad sql statements, including this one.
Steve Welborn
Software Engineer
Inrange Consulting
|
|
|
|
|
Hi,
i have a parent and child tables. when i am deleting a row from a parent table i want to check wheter that value in Primarykey column exits in childtables or not(it is a forien key in child tables) without mentioning the child table names.ie; i should be generic..i am using sqlserver 2005.
Anu
|
|
|
|
|
Then you will have to write some SQL that examines the dependencies of a table, find the relationships then traverses it and examine the child table. You might be able to wrap this up into a UDF (User Defined Function) but I can't imagine that it would be very quick.
|
|
|
|
|
If you want it to prevent deletes if there are child rows, why not just make sure that cascading deletes is switched off on your tables?
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
How can i switched of cascading deletes. actually i am not deleting the full record from parent table.just i want to check checking whether it contains child rows or not
Anu
|
|
|
|
|
OK - the cascading ability is maintained per FK relationship. As you aren't actually performing a delete - you are only doing an update, you are going to have to do this the way that Colin suggested.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
hello ,
i having a problem in SQL.
i will explain my problem with a simple example.
lets say i have a table called "MyTable" with the following headers:
SKU , TITLE , USER , PRICE , QUANTITY , TOTAL_PRICE
the values are:
avs123 ,some title , udikantz , 2.0 , 1 , 2.0
avs123 ,some title , codeproject , 2.0 , 10 , 20.0
avs123 ,some title , baboon , 2.0 , 5 , 10.0
lol22 , some title2 , hehe , 5.0 , 2 , 10.0
lol22 , some title2 , hoho , 5.0 , 1 , 5.0
i am trying to write a query that will output the following table:
SKU TITLE PRICE QUANTITY TOTAL_PRICE
------------------------------------------------------
avs123 some title 2.0 16 32
lol22 some title2 5.0 3 15
so what im trying to do exactly is to group all the SKU items with the same SKU string to one row and Sum the quantity of all the grouped SKUS
I was trying to manage that by using the distinct statement...
Net
|
|
|
|
|
You could try a GROUP BY and some aggregate functions.
SELECT SKU, TITLE, AVG(PRICE), SUM(QUANTITY), SUM(TOTAL_PRICE)<br />
FROM MyTable<br />
ORDER BY SKU, TITLE
|
|
|
|
|
if i understand what u are saying you probably ment:
SELECT SKU, TITLE, AVG(PRICE), SUM(QUANTITY), SUM(TOTAL_PRICE)
FROM MyTable
Group BY SKU, TITLE
and not
...
...
...
ORDER BY SKU, TITLE
well that is not working for me , Cause my table contain a few more fields i didnt mention in the example ,
more headers like : COLOR , SIZE , COMMENTS ,...
which may be diferent in any ROW
so when im doing
...
...
...
...
Group BY COLOR,SIZE,TITLE,SKU....ect
im getting the same table as "MyTable"
any other idea?
Net
|
|
|
|
|
udikantz wrote: Group BY SKU, TITLE
and not
ORDER BY SKU, TITLE
Oops! My bad.
udikantz wrote: well that is not working for me , Cause my table contain a few more fields i didnt mention in the example
Perhaps you should have done.
|
|
|
|
|
hey ,
is it possible to set the cells of a DataGridView so if the content
of a cell is longer than a certain amount of chars ,
the cell will split the text into few lines as needed...
as for now i get cells with long lines and i didnt find any way
to adjust the cells as multiline cells , im sure there is a way of doing that.
what properties , methods would solve my problem?
thanks.
Net
|
|
|
|
|
Have you set the cells to wrap = true
|
|
|
|
|
Hi all,
When i use order by clause in my query, it retrieves data like.....
NULL
NULL
NULL
4/10/2007
4/28/2007
5/17/2007
7/20/2007
9/10/2007
But i need it in another order, like..........
4/10/2007
4/28/2007
5/17/2007
7/20/2007
9/10/2007
NULL
NULL
NULL
Can anyone help me with this...............
thanks in advance.!
Sebastian
|
|
|
|
|
use order by desc
I Love SQL
|
|
|
|
|
Hi
thanks for your reply....but that will not work, If i use that i will get result like.....
9/10/2007
7/20/2007
5/17/2007
4/28/2007
4/10/2007
NULL
NULL
NULL
But i need result in the following order.......
4/10/2007
4/28/2007
5/17/2007
7/20/2007
9/10/2007
NULL
NULL
NULL
I hope you understood..... awaiting for your comments
Thanks once again
Sebastian
|
|
|
|
|
You could try using COALESCE (sp?) in your ORDER BY clause to replace the nulls with a far off date
ORDER BY COALESCE(MyDateColumn, '9999-12-31')
You might want to check what the maximum date permitted is in SQL Server as I don't know off the top of my head. It changes between the DATETIME and SMALLDATETIME types also.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
