CODEWITHSUNDEEP
sqlsql-server
Zee-pro
Zee-pro

Reputation: 165

SQL: Comparing MAX Dates from Two different Tables

I have 3 Tables

User

enter image description here

Attendence

enter image description here

Payment

enter image description here

Now I like to get

GroupID, UserName, MAX(PaymetDate), MAX(AttendenceDate)

Where MAX(PaymetDate) IS LESS THAN MAX(AttendenceDate)

This what I have Tried

    SELECT  MAX(PaymetDate) AS Paied_Upto
    FROM Payment
    Group by GroupID   

    SELECT  MAX(AttendenceDate) AS Last_ AttendenceDate
    FROM Attendence FULL OUTER JOIN Users ON Attendence.Username = Users.Username
    Group by Users.GroupID

But how do get them to work together?

Thank

Upvotes: 1

Views: 3760

Answers (3)

Cristian Lupascu
Cristian Lupascu

Reputation: 40516

I think this does what you need (SqlFiddle link):

select UserName, GroupID, MaxAttendanceDate, MaxPaymentDate
from (
  select 
    u.UserName,
    u.GroupID,
    (select max(AttendanceDate) 
       from Attendance a 
       where a.UserName = u.UserName) as MaxAttendanceDate,
    (select max(PaymentDate) 
       from Payment p
       where p.GroupID = u.GroupId) as MaxPaymentDate
  from [User] u
) x
where MaxAttendanceDate > MaxPaymentDate

Upvotes: 0

Angus Atkins-Trimnell
Angus Atkins-Trimnell

Reputation: 121

Try this:

SELECT u.GroupID, u.UserName, py.LastPaymentDate, at.LastAttendenceDate
FROM User AS u,
(SELECT Username, Max(AttendenceDate) AS LastAttendenceDate FROM Attendence GROUP BY Username) AS at,
(SELECT GroupID, Max(PaymetDate) AS LastPaymentDate FROM Payment GROUP BY GroupID) AS py
WHERE u.UserName=at.Username
AND u.GroupID=py.GroupID
AND py.LastPaymentDate < at.LastAttendenceDate;

Upvotes: 1

AJP
AJP

Reputation: 2125

try this

select p.GroupID, u.UserName, MAX(p.PaymetDate), MAX(a.AttendenceDate)
from dbo.Users u
inner join dbo.Attandence a
    ON u.UserName = a.UserName
Inner join dbo.Payment p
    ON u.groupID = p.GroupID
GROUP BY p.GroupID, u.UserName
Having MAX(p.PaymentDate) < MAX(a.attendenceDate)

Upvotes: 0

Related Questions