Query help with multiple variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query help with multiple variables

Author

Topic

Matt.701
Starting Member

4 Posts

Posted - 2011-02-02 : 03:21:50

Hi Guys.

I have two tables. Visit and Doctor.

Doctor - Id, Name, Address
Visit - PatientId, DoctorId, DateofDischarge*

*DateofDischarge is null if Patient is still visiting
Doctors can only have 3 Patients at once

There's more variables but that's all we need for the query.
I need a query that will give me a table of all the available doctors.
I'm OK with real basic SQL but get confused with stuff like this.
Here's what I have. I know its wrong but thought it would give you an idea of what I'm trying to do.

SELECT A1.Name, A1.Address
FROM Doctor A1
WHERE 3 >
(SELECT COUNT(DISTINCT(DoctorId))
FROM Visit A2
WHERE DateOfDischarge = NULL
AND A1.Id = A2.DoctorId)

Any help much appreciated. :)
Matt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-02 : 03:25:40

SELECT d.Name, d.Address
FROM dbo.Doctor AS d
INNER JOIN (
SELECT DoctorID FROM dbo.Visit GROUP BY DoctorID
HAVING SUM(CASE WHEN DateOfDischarge IS NULL THEN 1 ELSE 0 END) < 3
) AS v ON v.DoctorID = d.ID

N 56°04'39.26"
E 12°55'05.63"

Matt.701
Starting Member

4 Posts

Posted - 2011-02-02 : 03:44:50

Thanks for the quick reply.
But for some reason its not returning any tables although it should be for me. I can see any holes in your logic though. Is it definitely right?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-02 : 05:57:17

It should. Things would have been easier of you provide some proper sample data and expected output.
The HAVING part in my suggestion is calculating the presence of patience for each doctor.

If patience is still with doctor, increase by 1. If no patience or patience is discharged, set to increase by 0.
If the total is less than 3 (more slots available for doctor and the doctor can accept more patiences), display the doctor in the result.

N 56°04'39.26"
E 12°55'05.63"

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-02 : 14:14:24

>> I have two tables. Visit and Doctor. <<

"A problem well stated is a problem half solved." -- Charles F. Kettering

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. For exmaple, do you really have only one Doctor, as you said? Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

CREATE TABLE Doctors
(doctor_id INTEGER NOT NULL PRIMARY KEY,
doctor_name VARCHAR(35) NOT NULL,
doctor_address VARCHAR(100) NOT NULL);

Your Visits table did not have a key or proper constraints. Your bad DDL leads to bad or impossible DML.

CREATE TABLE Visits
(patient_id INTEGER NOT NULL
REFERENCES Patients (patient_id),
doctor_id INTEGER NOT NULL
REFERENCES Doctors (doctor_id),
appointment_slot SMALLINT DEFAULT 1 NOT NULL
CHECK (appointment_slot IN (1,2,3)),
appointment_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (doctor_id, appointment_date, appointment_slot),
discharge_date DATE,
CHECK (appointment_date <= discharge_date));

/* discharge_date is NULL if Patient is still visiting. Doctors can only have 3 Patients at once */

>> I need a query that will give me a table of all the available doctors. <<

Do it as a VIEW that is always current

CREATE VIEW Open_Appointment_Slots_by_Doctor
(today, doctor_id, doctor_name, open_slot_cnt)
AS
SELECT CAST (CURRENT_TIMESTAMP AS DATE) AS today,
D.doctor_id, D.doctor_name,
(3- COUNT(*)) AS open_slot_cnt
FROM Visits AS V, Docotors AS D
WHERE V.doctor_id = D.doctor_id
AND CAST (CURRENT_TIMESTAMP AS DATE)
BETWEEN appointment_date
AND COALESCE (discharge_date, CAST (CURRENT_TIMESTAMP AS DATE))
GROUP BY doctor_id, D.doctor_name;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Matt.701
Starting Member

4 Posts

Posted - 2011-02-02 : 20:40:03

Sorry guys I thought I provided enough information.
I'm actually studying asp.net and doing an assignment for it but we need a little sql to access the database. The Database schema was provided for us.

These are the scripts I used to create the tables.

CREATE TABLE Doctor
(Id int PRIMARY KEY,
Name varchar(50) NOT NULL,
Address varchar(255),
Phone varchar(20) NOT NULL)

CREATE TABLE Visit
(Id int references Patient(Id) NOT NULL,
PatientType int,
DoctorId int references Doctor(Id),
BedId int references Bed(Id),
DateOfVisit DateTime,
DateOfDischarge DateTime,
Symptoms varchar(1000),
Disease varchar(1000),
Treatment varchar(1000))

Test Data for Doctor:

Id Name Address Phone
1 Dr Ham Burger 99 Expensive Lane, RichTown 0400999888
2 Dr Hot Dog 12 Posh Ave, RichTown 0400888999

Test Data for Visit:

Id - PatientType - DoctorId - BedId - DateOfVisit - DateOfDischarge
1 0 1 NULL NULL NULL
2 0 1 NULL NULL NULL
3 0 1 NULL NULL NULL
4 0 2 NULL NULL NULL

The rest of the fields in visit are all null because it is unimportant for the moment.

Anyway so the expected output for the query would be:
Name Address
Dr Hot Dog 12 Posh Ave, RichTown

for he only has one current patient, whereas doctor 1 has 3.

Hope this helps.
Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-02 : 23:23:46

So what's wrong with my solution then? You dismissed it before, but as far I can see it returns the correct result...

CREATE TABLE Doctor
(Id int PRIMARY KEY, 
Name varchar(50) NOT NULL, 
Address varchar(255), 
Phone varchar(20) NOT NULL)

CREATE TABLE Visit
(Id int, 
PatientType int, 
DoctorId int references Doctor(Id),
BedId int,
DateOfVisit DateTime,
DateOfDischarge DateTime
)

insert doctor(Id,Name,Address,Phone)
select	1, 'Dr Ham Burger', '99 Expensive Lane', '0400999888' union all
select	2, 'Dr Hot Dog', '12 Posh Ave', '0400888999'

insert Visit(Id, PatientType, DoctorId, BedId, DateOfVisit, DateOfDischarge)
select	1, 0, 1, NULL, NULL, NULL union all
select	2, 0, 1, NULL, NULL, NULL union all 
select	3, 0, 1, NULL, NULL, NULL union all
select	4, 0, 2, NULL, NULL, NULL 


-- Solution by Peso
SELECT d.Name, d.Address
FROM dbo.Doctor AS d
INNER JOIN (
SELECT DoctorID FROM dbo.Visit GROUP BY DoctorID
HAVING SUM(CASE WHEN DateOfDischarge IS NULL THEN 1 ELSE 0 END) < 3
) AS v ON v.DoctorID = d.ID

drop table visit, Doctor

N 56°04'39.26"
E 12°55'05.63"

Matt.701
Starting Member

4 Posts

Posted - 2011-02-03 : 00:36:01

Well that's odd. It working now. I have no idea why it wasn't working yesterday. I must have done something wrong. Thanks for all your help :)

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-03 : 13:59:02

>> The Database schema was provided for us. <<

And it was awful! There is a magical universal "id" that identifies a doctor, a patient, a bed, and probably everything else. It has more null-able columns than the entire payroll system of Nissan motors. The data element names are all wrong. There are no constraints or DRI.

Since you are just learning RDBMS, please don't use this mess as a guide to your own work.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources