Compare Dates in Two separate tables

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

General SQL Server Forums

New to SQL Server Programming

Compare Dates in Two separate tables

Author

Topic

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-11-02 : 16:19:26

I have been trying to write a query that does the following:

ddbo.AdEnroll
SyStudentID
StartDate
GradDate

dbo.AdEnrollSched
ADEnrollID
SyStudentID
ExpectedEndDate

dbo.AdEnrollDegree
AdEnrollID
AdDegreeID
DateAwarded

I want any student ID that has a DateAwarded Date in dbo.AdEnrollDegree
AND
I want to compare the DateAwarded to ExpectedEndDate

SELECT DISTINCT
TOP (100) PERCENT dbo.syStudent.SyStudentId, dbo.AdEnroll.GradDate, dbo.AdEnrollDegree.DateAwarded, dbo.syStudent.LastName,
dbo.syStudent.FirstName, dbo.AdEnrollSched.ExpectedEndDate
FROM dbo.AdEnrollSched RIGHT OUTER JOIN
dbo.AdEnroll ON dbo.AdEnrollSched.AdEnrollID = dbo.AdEnroll.AdEnrollID LEFT OUTER JOIN
dbo.AdEnrollDegree ON dbo.AdEnroll.GradDate = dbo.AdEnrollDegree.DateAwarded RIGHT OUTER JOIN
dbo.syStudent ON dbo.AdEnroll.SyStudentID = dbo.syStudent.SyStudentId
WHERE (dbo.AdEnrollSched.ExpectedEndDate IS NOT NULL) AND (dbo.AdEnroll.GradDate IS NOT NULL)

This query pulls back the raw data, but when I try to use a group by or MAX Sub Query to compare the tables I get errors.

Here is the sub Query I was trying to use:

(Select
LastName,
FirstName,
AdEnrollId,
Max(ExpectedEndDate) AS MaxDate
FROM
dbo.AdEnrollSched,
dbo.SyStudent
Group By
LastName, FirstName, AdEnrollID, ExpectedEndDate)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-02 : 20:16:15

[code]
SELECT s.SyStudentID, s.ExpectedEndDate, d.DateAwarded,
DateDiff(day, s.ExpectedEndDate, d.DateAwarded)
FROM dbo.AdEnrollSched s
JOIN dbo.AdEnrollDegree d
On d.EnrollID = s.EnrollID
WHERE DateArarded is NOT NULL
[/code]

You may not need the line highlighted in red, can't tell from your post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources