Appointment tracking query - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Appointment tracking query

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-09-23 : 12:31:35

I have an appointments table that tracks appointments. I would like a query that returns the last appointment the customer had with a representative in a specific department, and what the appointment was for.

CREATE TABLE Appointments (CustomerID INT, AppointType CHAR(25), AppointDate DATETIME, RepID INT)
CREATE TABLE Customers (ID INT IDENTITY(1,1), Account INT,FName CHAR(20),LName CHAR(25))
CREATE TABLE Representatives(ID INT IDENTITY(1,1),FName CHAR(20), LName CHAR(25),DeptID INT)
CREATE TABLE Departments(ID INT IDENTITY(1,1), Name CHAR(30))

INSERT Departments VALUES('Sales')
INSERT Departments VALUES('Marketing')
INSERT Departments VALUES('Tech')

INSERT Representatives VALUES('John','Doe',1)
INSERT Representatives VALUES('Jane','Doe',2)
INSERT Representatives VALUES('Bob','Smith',3)
INSERT Representatives VALUES('Ann','Jackson',2)
INSERT Representatives VALUES('Mark','Drew',1)

INSERT Customers VALUES(100,'Joe','White')
INSERT Customers VALUES(200,'Joe','Green')
INSERT Customers VALUES(300,'Joe','Blue')
INSERT Customers VALUES(400,'Joe','Black')
INSERT Customers VALUES(500,'Joe','Brown')

INSERT Appointments VALUES(1,'Meeting','7/1/2003',1)
INSERT Appointments VALUES(1,'Presentation','9/1/2003',5)
INSERT Appointments VALUES(1,'IT','7/15/2003',3)
INSERT Appointments VALUES(2,'Meeting','7/31/2003',2)
INSERT Appointments VALUES(2,'Meeting','8/30/2003',4)
INSERT Appointments VALUES(3,'IT','7/10/2003',3)
INSERT Appointments VALUES(3,'IT','9/15/2003',3)
INSERT Appointments VALUES(4,'Presentation','7/31/2003',5)
INSERT Appointments VALUES(4,'Meeting','8/31/2003',1)
INSERT Appointments VALUES(5,'Meeting','8/05/2003',1)
INSERT Appointments VALUES(5,'Meeting','9/1/2003',4)

CustomerID and RepID and Department are foreign keys to Customers,Representatives, and Departments.
So far I have this query:


select rtrim(c.fname) + ' ' + rtrim(c.lname) as custname, Appointtype,appointdate,rtrim(r.fname) + ' ' + rtrim(r.lname) as repname,d.name as deptname from
appointments inner join customers c on customerid = c.id inner join representatives r on repid = r.id inner join departments 
d on d.id = r.deptid where appointdate between '8/30/2003' and getdate()

Expected Results:
Customer AppointType Date Rep Dept LastDate LastType
-------- ----------- ----- ------- ---- --------- --------
Joe White Presentation 9/1/2003 Mark Drew Sales 7/1/2003 Meeting
Joe Black Meeting 8/31/2003 John Doe Sales 7/31/2003 Present
Joe Green Meeting 8/30/2003 Ann Jack Market 7/31/2003 Meeting
Joe Brown Meeting 9/1/2003 Ann Jack Market NULL NULL
Joe Blue IT 9/15/2003 Bob Smith Tech 7/10/2003 IT

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-09-23 : 12:52:23

I think you're starting from the wrong place...

I think you need to start from Customer, and left join onwards to appointments...(since there may not be any...as per your nulls)
and inner join onwards to dept and representatives

You're not looking for all appointments and then seeing who took part in them, you're backing up to qualify this list with appointments where they exist for a/all customer(s) and then moving onwards to seeing who attended.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-09-23 : 13:21:25

Not really. I don't want to see all customers w/ their associated appointments, I want to see all appointments in a time frame. (Example: I want to give reps a list of all their appointments in the month, and to make it easier for them, show them if anybody in their department has met with this customer previously and if yes, why did they meet. Hence the NULLs, this customer is meeting a rep from this department for the first time.)

Sarah Berger MCSD

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-23 : 14:16:05

Had to change Customers to Customers_x so I could use Northwind...

but how about this...


      SELECT 	  d.Name As Dept
		, a.AppointDate As Appointment
		, a.AppointType AS Type
		, RTRIM(r.LName)+', '+RTRIM(r.FName) AS Rep
		, RTRIM(c.LName)+', '+RTRIM(c.FName) AS Customer
	FROM Appointments a 
  INNER JOIN Representatives r
	  ON a.RepId = r.Id 
  INNER JOIN Departments d
	  ON r.DeptId = d.ID 
  INNER JOIN Customers_x c
	  ON a.CustomerId = c.ID
  INNER JOIN  ( SELECT CustomerId, MAX(AppointDate) AS MAX_AppointDate 
		 FROM Appointments 
	     GROUP BY CustomerId) AS l
	  ON a.CustomerId = l.CustomerId
	 AND a.AppointDate = l.MAX_AppointDate

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-09-23 : 15:54:04

Not so simple, Brett...
First, the main difficulty is getting out Last Appointment Type. Second, a derived table is no good; the MAX date will always be the most recent appointment, or maybe a future appointment. I want WHERE MAX(AppointDate) < AppointDate OF CURRENT ROW. This is easy to get out in a subquery



      SELECT 	  d.Name As Dept
		, a.AppointDate As Appointment
		, a.AppointType AS Type
		, RTRIM(r.LName)+', '+RTRIM(r.FName) AS Rep
		, RTRIM(c.LName)+', '+RTRIM(c.FName) AS Customer,
(SELECT MAX(AppointDate) FROM Appointments WHERE CustomerID = a.CustomerID AND RepID IN(SELECT ID FROM Representatives WHERE DeptID = d.ID) AND AppointDate < a.AppointDate) AS LastApptDate
	FROM Appointments a 
  INNER JOIN Representatives r
	  ON a.RepId = r.Id 
  INNER JOIN Departments d
	  ON r.DeptId = d.ID 
  INNER JOIN Customers_x c
	  ON a.CustomerId = c.ID

but it's the last appt. type that really gets me. It may be possible to get it out with sub-sub query, but that's really uggghhh...

Sarah Berger MCSD

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-23 : 16:03:33

Hey Sarah,

You didn't have any future rows in the data...so I overlooked that...

More likely you'll need an indicator to say if the meeting took place or not, unless 1 day is acceptable..

~~Second, you're scaring me with the cursor talk...and maybe I don't understand the rest..~~

EDIT: Damn changing platforms...OF CURRENT ROW is a DB2 FETCH option..sorry

I changed the data and added a future appointment, and eliminate them with a HAVING Clause...

I'm confused by "The Last appointment type". Isn't it by department? Isn't it the Greatest Date less than today?

Anyway, what about:


USE Northwind
GO


CREATE TABLE Appointments (CustomerID INT, AppointType CHAR(25), AppointDate DATETIME, RepID INT)
CREATE TABLE Customers_x (ID INT IDENTITY(1,1), Account INT,FName CHAR(20),LName CHAR(25))
CREATE TABLE Representatives(ID INT IDENTITY(1,1),FName CHAR(20), LName CHAR(25),DeptID INT)
CREATE TABLE Departments(ID INT IDENTITY(1,1), Name CHAR(30))
GO

--I would like a query that returns the last appointment the customer had
--the customer had with a representative in a specific department, and what the appointment was for.

INSERT Departments VALUES('Sales')
INSERT Departments VALUES('Marketing')
INSERT Departments VALUES('Tech')

INSERT Representatives VALUES('John','Doe',1)
INSERT Representatives VALUES('Jane','Doe',2)
INSERT Representatives VALUES('Bob','Smith',3)
INSERT Representatives VALUES('Ann','Jackson',2)
INSERT Representatives VALUES('Mark','Drew',1)

INSERT Customers_x VALUES(100,'Joe','White')
INSERT Customers_x VALUES(200,'Joe','Green')
INSERT Customers_x VALUES(300,'Joe','Blue')
INSERT Customers_x VALUES(400,'Joe','Black')
INSERT Customers_x VALUES(500,'Joe','Brown')

INSERT Appointments VALUES(1,'Meeting','7/1/2003',1)
INSERT Appointments VALUES(1,'Presentation','9/1/2003',5)
INSERT Appointments VALUES(1,'IT','7/15/2003',3)
INSERT Appointments VALUES(2,'Meeting','7/31/2003',2)
INSERT Appointments VALUES(2,'Meeting','8/30/2003',4)
INSERT Appointments VALUES(3,'IT','7/10/2003',3)
INSERT Appointments VALUES(3,'IT','9/25/2003',3)
INSERT Appointments VALUES(4,'Presentation','7/31/2003',5)
INSERT Appointments VALUES(4,'Meeting','8/31/2003',1)
INSERT Appointments VALUES(5,'Meeting','8/05/2003',1)
INSERT Appointments VALUES(5,'Meeting','9/1/2003',4)
GO

      SELECT 	  d.Name As Dept
		, a.AppointDate As Appointment
		, a.AppointType AS Type
		, RTRIM(r.LName)+', '+RTRIM(r.FName) AS Rep
		, RTRIM(c.LName)+', '+RTRIM(c.FName) AS Customer
	FROM Appointments a 
  INNER JOIN Representatives r
	  ON a.RepId = r.Id 
  INNER JOIN Departments d
	  ON r.DeptId = d.ID 
  INNER JOIN Customers_x c
	  ON a.CustomerId = c.ID
  INNER JOIN  ( SELECT CustomerId, MAX(AppointDate) AS MAX_AppointDate 
		 FROM Appointments 
	     GROUP BY CustomerId
HAVING Max(AppointDate) < GetDate()) AS l
	  ON a.CustomerId = l.CustomerId
	 AND a.AppointDate = l.MAX_AppointDate
GO
 
DROP TABLE Appointments
DROP TABLE Customers_x
DROP TABLE Representatives
DROP TABLE Departments
GO

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-09-23 : 20:46:07

No cursor, Brett

By OF CURRENT ROW, I meant current row of result set, as relative to current row. Please see the Expected Results sample I posted. (For each appointment row, check if there is a previous one. This doesn't use GETDATE() because it doesn't relate to today's date).
Let me explain this better: I used the Customer/Rep example because I thought it might be easier to understand, but I guess not. This is really a medical scenario: Every day, doctors get a printout of all appointments for that day (patient name, reason for visit, time of appt, etc). These can be printed in advance, invalidating the use of GETDATE(). Now it would be handy for a doctor to see when the patient was there last, but only for a doctor in his/her own department. It makes no difference to a pediatrician when the patient last saw a dentist. Also, the doctor might want to see why the patient was there last. So if the current appointment is for a pediatrician for a strep test, the doctor would like to see if the patient was there a month ago with a strep test and ask the patient "Did you have strep last month?"
I hope this makes the issue clearer.

Sarah Berger MCSD

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-24 : 05:14:44

Sarah:
How about replacing this piece of your last code

(SELECT MAX(AppointDate) FROM Appointments WHERE CustomerID = a.CustomerID
AND RepID IN(SELECT ID FROM Representatives WHERE DeptID = d.ID) AND
AppointDate < a.AppointDate) AS LastApptDate

with the following:

(SELECT MAX(AppointDate) FROM Appointments WHERE CustomerID = a.CustomerID
AND RepID IN(SELECT ID FROM Representatives WHERE DeptID = d.ID) AND
AppointDate < a.AppointDate) AS LastApptDate,

(SELECT TOP 1 AppointType FROM Appointments WHERE CustomerID = a.CustomerID
AND RepID IN(SELECT ID FROM Representatives WHERE DeptID = d.ID) AND
AppointDate < a.AppointDate ORDER BY AppointDate Desc) AS LastApptType

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-24 : 09:29:49

first off:

The only focus that should be done is on the Appointments table. No joins to other tables YET. Just focus on returning, for each customerID, the LATEST appointment date and the one just before that:


SELECT 
  A.CustomerID, A.AppointDate as LastAppointDate, 
  MIN(A2.AppointDate) as PrevAppointDate
FROM 
  Appointments A
INNER JOIN
  Appointments A2
ON
  A.CustomerID = A2.CustomerID AND
  A.AppointDate > A2.AppointDate
GROUP BY 
  A.CustomerID, A.AppointDate
HAVING 
  COUNT(*) = 1

Now, that only works if EVERYONE has at least 2 appointments. If anyone only has 1, they won't show up. If that might be a problem, you can do this:


SELECT 
  A.CustomerID, A.LastAppointDate, MAX(AppointDate) as PrevAppointDate
FROM
 (SELECT CustomerID, Max(AppointDate) as LastAppointDate 
  FROM Appointments 
  GROUP BY CustomerID) A
LEFT OUTER JOIN
  Appointments A2
ON
  A2.CustomerID = A.CustomerID AND
  A2.AppointDate < A.LastAppointDate
GROUP BY 
  A.CustomerID, A.LastAppointDate

Don't worry about JOINS or returning departments or names or descriptions or anything until you get this part taken care of.

Once that is done, you just encapsulate that SQL into a subquery and from there join to the tables that provide the descriptive information.

Question -- what is the PK of the appointments table? Hopefully, customerID and AppointDate? If not, this won't work.

- Jeff

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-09-24 : 15:04:38

Thank You, Stoad,

your query is correct. Thanks everybody for the input as well!

I somehow had a notion that ORDER BY wasn't allowed in subqueries...(confused it with derived tables???)

P.S. Actuall, Jeff, the PK is a generated INT column AppointmentID. A patient can have more than one appointment for a day.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-24 : 15:27:08

Yes, but how does that work with future dates?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-24 : 15:39:54

Oh, Sarah,

glad to hear it helped you! Cheers!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-24 : 15:42:27

Oh, Brett,

never mind about the future.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-24 : 16:48:43

quote:
Originally posted by Stoad

Oh, Brett,

never mind about the future.

"The future's uncertain, and the end is always near...."

But that was Sarah's req...not mine...

"Let it roll, baby, roll..."

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources