SQL View to find dormant clients based on no activity within 6 months.

What I need to do is EXCLUDE any of those ClientID who have a record within the last 6 months.

does this worked?

WHERE NOT
dbo.tblShow.ShowStartDate > DATEADD(month, -6, GETDATE())

Select allOpen in new window

why don't try like:

WHERE dbo.tblShow.ShowStartDate <= DATEADD(month, -6, GETDATE())

Select allOpen in new window

As an extra layer I would also like it to return that client only once - at the moment it returns every instance of that client in the record.

reason being, you are joining multiple fields from multiple tables, which creating a multiply effect to it, unless you want to get the latest record per client, for example?

Thanks Ryan but how does this EXCLUDE clients?

I get the part that it excludes dates - maybe I should be more clear.

so I have a pretty large database of entries made into events - dating back to 2003.

I want to check on those people who were making entries (which I did) but i need to refine that to specifically list only people who did not make entries within the last 6 months.

So it's quite specific that it has to remove say a client with ID 22354 who made an entry on 15 June 2018 but ALSO made an entry on 3rd December 2018.

I don't think your suggestion does this because it just looks like another way of doing what I have already done? It certainly returns exactly the same number of records

The last thing I want to do is send a "we miss you" email out to someone who was here last week!

It needs to be specific to ClientID

Try this:

SELECT	MAX(show.ShowStartDate) AS LastSHowStartDate,
		ent.EntryResultsMark,
		clnt.ClientSurName,
		clnt.ClientEmail,
		clnt.ClientForeName
FROM	dbo.tblClass cls
JOIN 	dbo.tblEntry ent
ON 	cls.ClassID = ent.ClassID 
JOIN 	dbo.tblClient clnt
ON 	clnt.ClientID = ent.RiderID
JOIN 	dbo.tblShow show
ON 	show.ShowID = cls.ShowID 
GROUP BY ent.EntryResultsMark,
		clnt.ClientSurName,
		clnt.ClientEmail,
		clnt.ClientForeName
HAVING MAX(ShowStartDate) < DATEADD(month, -6, GETDATE()) 
AND 	ent.EntryResultsMark is not NULL

Select allOpen in new window

Looked promising but unfortunately it has returned a list of clients, some of whom HAVE entered events within the last 6 months.

I made a minor modification to the original code as I also needed to limit by event type and sort by last entry date so I can manually remove people who haven't been customers for a very long time.

SELECT	MAX(show.ShowStartDate) AS LastSHowStartDate,
		ent.EntryResultsMark,
		clnt.ClientSurName,
		clnt.ClientEmail,
		clnt.ClientForeName, show.ShowType
FROM	dbo.tblClass cls
JOIN 	dbo.tblEntry ent
ON 	cls.ClassID = ent.ClassID 
JOIN 	dbo.tblClient clnt
ON 	clnt.ClientID = ent.RiderID
JOIN 	dbo.tblShow show
ON 	show.ShowID = cls.ShowID 
GROUP BY ent.EntryResultsMark,
		clnt.ClientSurName,
		clnt.ClientEmail,
		clnt.ClientForeName,
show.ShowType
HAVING MAX(ShowStartDate) < DATEADD(month, -6, GETDATE()) 
AND 	ent.EntryResultsMark is not NULL AND show.ShowType = 2
ORDER BY LastSHowStartDate

Select allOpen in new window

Should I do this another way than with SQL?

No sure what other way you'd have. It is data in a SQL database.

Also not sure why it would return rows where ShowStartDate is within the previous 6 months. I don't have any data to play with. Interestingly, I am working on a VERY similar situation with a query (identifying clients that have a history of changes, but there has not been a change in the last 3 years.) Almost the same syntax and it is working as expected. The expectation in my case is to delete the 'stale' history.

Hi Doug.

Sorry! It doesn’t return clients with dates in the last 6 months. It does however return clients with dates more than 6 months old that have new entries within the 6 month period.

The records returned are all more than 6 months old so that bit is right. But I happen to recall some of the client names and know they have more recent entries.

So like you say I’m looking for dormant clients who haven’t transacted within 6 months but have had transactions of that particular event type (showtypeid) historically.

Hope that makes sense.

We the sql comment - just thought maybe using some kind of web script to remove the entries “manually” so to speak although with the number of records I suspect this might be a bit slower.

Have you run queries against the data it returned to make sure the returned data did in fact have activity in the past 6 months. You might recall the name because they had activity for a different ShowType than 2, or had a EntryResultsMark that was NULL.

Is the value for LastShowStartDate that is returned for the client(s) in question within the 6-month range?

Ok so I have confirmed that:

1.The recordset returned is all with Last ShowStart Date of more than 6 months from today
2.The list is clients who have entered a showtypeid of 2
3.The list DOES contain non-dormant clients for this showtypeid

The SQL code is not effectively excluding those clients on the list who have entries within the last 6 months.

I thought I'd posted a code block yesterday for you to run. Appears it didn't make it. I've made a slight change. Try running this:

SELECT	MAX(show.ShowStartDate) AS LastSHowStartDate,
		ent.EntryResultsMark,
		clnt.ClientSurName,
		clnt.ClientEmail,
		clnt.ClientForeName, show.ShowType
FROM	dbo.tblClass cls
JOIN 	dbo.tblEntry ent
ON 	cls.ClassID = ent.ClassID 
JOIN 	dbo.tblClient clnt
ON 	clnt.ClientID = ent.RiderID
JOIN 	dbo.tblShow show
ON 	show.ShowID = cls.ShowID 
GROUP BY ent.EntryResultsMark,
		clnt.ClientSurName,
		clnt.ClientEmail,
		clnt.ClientForeName,
show.ShowType
HAVING MAX(ShowStartDate) < DATEADD(month, -6, GETDATE()) 
WHERE	ent.EntryResultsMark is not NULL AND show.ShowType = 2
ORDER BY LastSHowStartDate

Select allOpen in new window

If you still get non-dormant clients listed, run the following (substituting suspectID with the client ID in the WHERE clause) and examine the results:

SELECT	show.ShowStartDate,
		ent.EntryResultsMark,
		clnt.ClientSurName,
		clnt.ClientEmail,
		clnt.ClientForeName, show.ShowType
FROM	dbo.tblClass cls
JOIN 	dbo.tblEntry ent
ON 	cls.ClassID = ent.ClassID 
JOIN 	dbo.tblClient clnt
ON 	clnt.ClientID = ent.RiderID
JOIN 	dbo.tblShow show
ON 	show.ShowID = cls.ShowID 
WHERE	clnt.ClientID = [i]suspectID[/i]
AND	ent.EntryResultsMark is not NULL AND show.ShowType = 2
ORDER BY LastSHowStartDate

Select allOpen in new window

sorry my database doesn't like the existence of the WHERE clause in that SQL - it wont run

Which one (the query to examine results, without the GROUP BY, or the other one to look at details)? Does it return an error? If so, what is the message?
If the second query, did you substitute suspectID (I notice italics don't show up in a code block) with an actual ClientID?

I see the problem. Move the WHERE to just before the GROUP BY.

WHERE	ent.EntryResultsMark is not NULL AND show.ShowType = 2
GROUP BY ...

Select allOpen in new window

Hi Doug,

Thanks - ran through all the checks and it just doesnt work for some reason.

I did some digging and I think it might have something to do with the date of the event.

So i removed that from the query and went, instead on the transaction date which is stored in the Entry table. Not quite the same but normally not massively different from event date.

First impression - need a bit more verification - but it looks like it works. Very much along the lines of what you put above.

Im guessing that the way the ShowID, EntryID, ClassID all join is causing some kind of override and showing more data than it should.

Looks like this works

SELECT
dbo.tblClient.ClientID,
dbo.tblClient.ClientSurName,
dbo.tblClient.ClientForeName,
MAX(dbo.tblEntry.EntryDateMade) AS LastTransDate,
dbo.tblShow.ShowType
FROM
dbo.tblClient
LEFT JOIN dbo.tblEntry
ON dbo.tblClient.ClientID = dbo.tblEntry.RiderID 
JOIN dbo.tblClass
ON dbo.tblEntry.ClassID = dbo.tblClass.ClassID 
JOIN dbo.tblShow
ON dbo.tblClass.ShowID = dbo.tblShow.ShowID
WHERE
dbo.tblShow.ShowType = 2 AND dbo.tblEntry.EntryResultsMark is not null
GROUP BY
dbo.tblClient.ClientID,dbo.tblClient.ClientSurName,dbo.tblClient.ClientForeName, dbo.tblShow.ShowType
HAVING 
MAX(dbo.tblEntry.EntryDateMade) < DATEADD(month, -6, GETDATE())
ORDER BY
LastTransDate DESC

Select allOpen in new window