Need a query to list clients without jobs

I have what I thought is a simple requirement, but the solution is alluding me.

I have a table tblClients.  This links to a table tblJobs via the ClientID.
tblClients has a RenewalDate.
tblJobs has a DateJobReceived.

So I want a list of all clients who are registered (ie RenewalDate > 1/4/18) [say]..
.. who do not have any Jobs since their renewal date (ie count of jobs with DateJobReceived > 1/4/18 = zero)

Can anyone suggest a query that would give me this list please.

Regards

Richard
rltomalinAsked:
Who is Participating?
 
John TsioumprisSoftware & Systems EngineerCommented:
A bit more generic
SELECT * 
FROM  Clients,(SELECT Max(DateJobReceived) as LastJobDate , ClientID FROM Jobs GROUP BY ClientID) as Jobs
WHERE Clients.ClientId = Jobs.ClientID AND Clients.RenewalDate > Jobs.LastJobDate 

Open in new window

0
 
mbizupCommented:
Try this:
SELECT * 
FROM  tblClients c INNER JOIN 
(SELECT COUNT(JobTableID) AS TotalJobs, ClientID FROM tblJobs  WHERE DateJobReceived > #1/4/18# GROUP BY ClientID) q  
ON c.ClientID = q.ClientID
WHERE q.TotalJobs = 0 

Open in new window


Or this:
SELECT * 
FROM  tblClients c INNER JOIN 
(SELECT COUNT(JobTableID) AS TotalJobs, ClientID FROM tblJobs  WHERE DateJobReceived > #1/4/18# GROUP BY ClientID HAVING q.TotalJobs = 0) q  
ON c.ClientID = q.ClientID

Open in new window

0
 
mbizupCommented:
Correction to that second suggestion:

SELECT * 
FROM  tblClients c INNER JOIN 
(SELECT COUNT(JobTableID) AS TotalJobs, ClientID FROM tblJobs  WHERE DateJobReceived > #1/4/18# GROUP BY ClientID HAVING COUNT(JobTableID) = 0) q  
ON c.ClientID = q.ClientID

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
xiao jinshouCommented:
My understanding of your tblClients has a unique row for each ClientID or there is a flag to indicate whether the ClientID is active.  So I write the query below:

create table #tblClients (ClientID INT, RenewalDate Date)
insert into #tblClients values
                         (1,'2017-12-01')
                  ,(2,'2018-02-03')
                  ,(3,'2018-03-01')

create table #tblJobs (ClientID INT, DateJobReceived Date)

insert into #tblJobs values
                         (1,'2018-01-01')
                  ,(1,'2018-01-02')
                  ,(2,'2018-02-01')
                  ,(2,'2018-02-02')
                  ,(2,'2018-02-03')
                  ,(3,'2018-03-02')


;With
  TMPClientsJobs As
      (SELECT
                 tblClients.ClientID
                  ,RenewalDate
              ,DateJobReceived
              ,ROW_NUMBER() OVER (PARTITION BY tblClients.ClientID ORDER BY tblClients.RenewalDate DESC) rn
       FROM #tblClients tblClients
       INNER JOIN #tblJobs tblJobs
         ON tblClients.ClientID = tblJobs.ClientID
      WHERE RenewalDate >= '2018-01-04' AND DateJobReceived >= RenewalDate
        )
,TMPWithJobs As
  (SELECT
    ClientID
   ,RenewalDate
   ,DateJobReceived
  FROM TMPClientsJobs
  WHERE rn = 1)

 SELECT DISTINCT
    tblClients.ClientID
   ,tblClients.RenewalDate
 FROM #tblClients tblClients
 WHERE tblClients.ClientID NOT IN (SELECT ClientID FROM TMPWithJobs)

drop table #tblClients
drop table #tblJobs
0
 
xiao jinshouCommented:
Or simply:

create table #tblClients (ClientID INT, RenewalDate Date)
insert into #tblClients values
             (1,'2017-12-01')
                  ,(2,'2018-02-03')
                  ,(3,'2018-03-01')

create table #tblJobs (ClientID INT, DateJobReceived Date)

insert into #tblJobs values
             (1,'2018-01-01')
                  ,(1,'2018-01-02')
                  ,(2,'2018-02-01')
                  ,(2,'2018-02-02')
                  ,(2,'2018-02-03')
                  ,(3,'2018-03-02')


;With
  TMPClientsJobs As
      (SELECT
             tblClients.ClientID
          ,RenewalDate
              ,DateJobReceived
              ,ROW_NUMBER() OVER (PARTITION BY tblClients.ClientID ORDER BY tblClients.RenewalDate DESC) rn
       FROM #tblClients tblClients
         INNER JOIN #tblJobs tblJobs
         ON tblClients.ClientID = tblJobs.ClientID
         WHERE RenewalDate >= '2018-01-04' AND DateJobReceived >= RenewalDate
        )

 SELECT DISTINCT
    tblClients.ClientID
   ,tblClients.RenewalDate
 FROM #tblClients tblClients
 WHERE tblClients.ClientID NOT IN (SELECT ClientID FROM TMPClientsJobs WHERE rn = 1)

drop table #tblClients
drop table #tblJobs
0
 
rltomalinAuthor Commented:
Thanks for getting back so promptly.  Unfortunately I cannot get any of these solutions to work (probably my own inexperience with SQL)

Just to clarify that my Client and Job IDs actually have a space in the name, so I have changed the code in the suggestions to match that.

The code I am now using therefore is....

FROM mbizip
SELECT *
FROM  tblClients c INNER JOIN
(SELECT COUNT([Job ID]) AS TotalJobs, [Client ID] FROM tblJobs  WHERE DateReceived > #1/4/18# GROUP BY [Client ID]) q  
ON c.[Client ID] = q.[Client ID]
WHERE q.TotalJobs = 0


SELECT *
FROM  tblClients c INNER JOIN
(SELECT COUNT([Job ID]) AS TotalJobs, [Client ID] FROM tblJobs  WHERE DateReceived > #1/4/18# GROUP BY [Client ID] HAVING COUNT([Job ID]) = 0) q  
ON c.[Client ID] = q.[Client ID]

Neither of these produce anything


FROM John
SELECT *
FROM  tblClients,(SELECT Max(DateReceived) as LastJobDate , [Client ID] FROM tblJobs GROUP BY [Client ID]) as Jobs
WHERE tblClients.[Client Id] = tblJobs.[Client ID] AND tblClients.RenewalDate > Jobs.LastJobDate

This does produce an output, but far too many (over 200).  I think there is a problem with this as there is no check that the client is registered (ie RenewalDate > Date())

But if I change the last bit of code to look for that rather than > Jobs.LastJobDate it returns what looks like the total of registered clients without the check for jobs created since renewal (which I think is as would be expected - about 120 jobs).
If I then add a further AND statement at the end - tblClients.RenewalDate > Jobs.LastJobDate it doesn't make any difference - still shows the number of current registered clients.

Sorry this is a bit rambling.  I am having a bit of trouble following this.  Any help would be appreciated.

Regards
Richard
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Without sample data i am afraid we will be only guessing
0
 
rltomalinAuthor Commented:
I'm sorry I can't follow the suggestion from xiao jinshou.
I'm not sure that it targets my problem.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Nothing is stopping you from adding one more condition
SELECT * 
FROM  tblClients,(SELECT Max(DateReceived) as LastJobDate , [Client ID] FROM tblJobs GROUP BY [Client ID]) as Jobs
WHERE tblClients.[Client Id] = tblJobs.[Client ID] AND tblClients.RenewalDate > Jobs.LastJobDate AND tblClients.RenewalDate>Date()

Open in new window

0
 
xiao jinshouCommented:
My logic is to use Common Table Expression (CTE), which is to use one  (or more) temp tables to do a query.  

Please see my second reply of the query.  The first temp table TMPClientsJobs list all the ClientIDs who was registered and had a latest job after Jan 4, 2018.  Then the final query lists the ClientIDs who are not in the table who has a job after Jan 4, 2018.

Please try to run it.

- Xiao
0
 
rltomalinAuthor Commented:
Thanks for all the help.  I THINK I have figured out the problem (I think my fault).  I will look at it tomorrow and feedback.

Regards

Richard
0
 
mbizupCommented:
Xiao (and to clear up some confusion for Richard),

The topic area here is MS Access (and I believe the database in question is as well).  CTE's do not apply here, and Temp tables should *generally* be avoided as they tend to bloat Access databases.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
My logic is to use Common Table Expression (CTE),
This is in the Microsoft Access topic area. Access doesn't use CTEs.
0
 
rltomalinAuthor Commented:
Thank you very much, this is now fixed.  The code was fine.  I did modify it a bit but the problem that I was having yesterday was entirely my own fault.
I was confusing myself with my own terminology.  I have the field RenewalDate which gets modified when the client renews (by adding 1 year to it).  But then I was looking at this current issue and regarding the RenewalDate as the date they renewed.  What I did was to just subtract 1 year (using DateAdd) from RenewalDate in the SQL.

Thanks a lot.

Best regards
Richard
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.