only first date for each record

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)

only first date for each record

Author

Topic

forwheeler
Starting Member

44 Posts

Posted - 2007-11-27 : 09:23:17

SELECT ClassInstructor.ClassID, Date.StartDateTime, Date.EndDateTime
FROM  Instructor 
INNER JOIN ClassInstructor ON Instructor.InstructorID = ClassInstructor.InstructorID 
INNER JOIN ClassDate ON ClassInstructor.ClassID = ClassDate.ClassID
INNER JOIN Date ON ClassDate.DateID = Date.DateID 

WHERE (Instructor.InstructorID = @InstructorID)

The result is

4 2007-09-04 12:00:00 2007-09-04 16:00:00
21 2007-10-02 00:00:00 2007-10-02 00:00:00
21 2007-10-10 00:00:00 2007-10-10 12:00:00

I only want one StartDateTime and EndDateTime for each class, not the other times for a given class.
What is the easiest way to do this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-27 : 09:41:52

SELECT ClassInstructor.ClassID, min(Date.StartDateTime) as StartDateTime, min(Date.EndDateTime) as EndDateTime
FROM Instructor
INNER JOIN ClassInstructor ON Instructor.InstructorID = ClassInstructor.InstructorID
INNER JOIN ClassDate ON ClassInstructor.ClassID = ClassDate.ClassID
INNER JOIN Date ON ClassDate.DateID = Date.DateID
WHERE (Instructor.InstructorID = @InstructorID)
GROUP BY ClassInstructor.ClassID

Madhivanan

Failing to plan is Planning to fail

forwheeler
Starting Member

44 Posts

Posted - 2007-11-27 : 09:46:29

Perfect!
Thanks

quote:
Originally posted by madhivanan

SELECT ClassInstructor.ClassID, min(Date.StartDateTime) as StartDateTime, min(Date.EndDateTime) as EndDateTime
FROM Instructor
INNER JOIN ClassInstructor ON Instructor.InstructorID = ClassInstructor.InstructorID
INNER JOIN ClassDate ON ClassInstructor.ClassID = ClassDate.ClassID
INNER JOIN Date ON ClassDate.DateID = Date.DateID
WHERE (Instructor.InstructorID = @InstructorID)
GROUP BY ClassInstructor.ClassID

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources