Link to home
Start Free TrialLog in
Avatar of butterhook
butterhookFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Complex joined query

Hello, I have three tables which I need to get information out of which is proving slightly more complicated than I expected.

The tables are
Client
Job
Contact
Call Log

And the brief is to return the list of clients who have not had booked a job in for the last three months, along with the details for the most recent job and the details of the most recent call log made in regard to that client.

The links in the tables are Job.JobID, Client.ClientID, Job.ClientID, CallLog.ClientID, Job.ClientID

The date field to sort the jobs by is Job.DeadlineDate, this would be the one which would be the basis of the date filter. The date filter would be the function <= DATEADD(Month,-3,GetDate())

So in essence I need to select

Client.Name, Job.JobTitle, Job.DeadlineDate, Contact.FirstName, Contact.LastName, CallLog.ID

Where the job record is the most recent one (but only if that job was before 3 months ago, and not if there are any jobs for that client since....), and the call log must be the most recent one for that client, regardless of timing.

Can anyone help? Let me know if you need any more information or detail of any kind.
ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Forced accept.

Computer101
EE Admin