asked on

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

mbizup

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

mbizup

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

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xiao jinshou

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

xiao jinshou

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

rltomalin

ASKER

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

John Tsioumpris

Without sample data i am afraid we will be only guessing

rltomalin

ASKER

I'm sorry I can't follow the suggestion from xiao jinshou.
I'm not sure that it targets my problem.

John Tsioumpris

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

xiao jinshou

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

rltomalin

ASKER

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

mbizup

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.

Scott McDaniel (EE MVE )

My logic is to use Common Table Expression (CTE),

This is in the Microsoft Access topic area. Access doesn't use CTEs.

rltomalin

ASKER

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