I have two tables:
'client', 'clientusage'

Each time a client uses our service, a row is inserted into the 'clientusage' table.

This row is of course linked to the 'client' table.

I would like to select all of the clients that have reached their 10th usage within a given date range.

Example Scenario:

I would like to select all clients that have reached their 10th usage sometime between 2008-09-01 and 2008-11-01.

The data I would like to return:
client id: client.client_id
client name: client.client_name
client register date: client.client_registerdate
date the 10th usage occured on: clientusage.usage_date

I am using SQL Server 2000

Is there a way to accomplish all of this within a query?

Thanks for your help!

Seems pretty simple to me:

select c.client_id, c.client_name, c.client_registerdate, max(u.usage_date)
from client as c
  left outer join clientusage as u on u.client_id=c.client_id  --I'm guessing the join here
where u.usage_date between '2008-09-01' and '2008-11-01'
having count(u.usage_date)>=10
group by c.client_id, c.client_name, c.client_registerdate, d.usage_date

Check the join condition, you might need to adapt.
You have not given the complete schema, so I had to extrapolate.

Thanks for the reply, tripy.

Something similar to your query was the first thing that came to my mind when I was first handed the problem. However, it will only return clients that have used 10 or more times within the given range.

I am needing to find out what clients reached their 10th usage within a given date range. This means that they could have 9 other uses before the lower date in the date range, and one usage within the range.

I think I may have solved the problem, though. I'll do some more testing and post back exactly what I did.

It is something like this:

SELECT
  c.client_id, c.client_name, c.client_register_date, max(u.usage_date) as maxdate, count(*) as totalusage
From
  clientusage u
JOIN client c ON u.client_id= c.client_id
WHERE
  u.usage_date <= '2008-11-01'
AND
  c.cient_id IN
   (
     SELECT
         c.client_id
     FROM
         clientusage u
     JOIN client c ON u.client_id = c.client_id
     WHERE
       u.usage_date < '2008-09-01'
     GROUP BY c.client_id
     HAVING count(*) < 10
  )
GROUP BY c.client_id, c.client_name, c.client_register_date
HAVING count(*) >= 10

What I did in the second select is grab all of the client IDs that had less than 10 usages before the lower of the 2 dates and then ensure they have 10 or more uses by the higher of the 2 dates.

I'm sure there is a more efficient way.

I could write some simple queries and handle most of the logic with my programming, but I would prefer to do as much work as possible within the query itself.

Thanks again for your reply!

Is that logic right? I'm not sure why you have to check less than 10 before a given date and more than 10 overall? Why can't you just use Tripy's query with a WHERE clause that restricts the date range?

That said, you're using SQL Server (2000), so don't fret too much over subquery performance. What will probably happen when this is run (you can verify with the query plan) is the server will "flatten" the subquery. It will be treated (logically) as an anonymous, temporary view, executed once, and joined to the result set of the outer query. It's not likely to be run for every row in the output. The query optimizer is usually very smart about these things.

However, it will only return clients that have used 10 or more times within the given range

I would like to select all clients that have reached their 10th usage sometime between 2008-09-01 and 2008-11-01.

Make up your mind....
You want those or not ?

@ tripy:

I think you're misreading those two lines.

Your query will return only those clients that have used 10 or more times within a given range.

I need clients that have reached their 10th usage within a given range.

This means that the client can technically have only 1 usage within the given range, and 9 usages prior to the lowest date in the range.

@Learning Newbie:

I don't see how that would work. Would you mind giving an example?


Also, I did goof something up in my above query example. I need to use 'NOT IN' instead of just 'IN'.

I need clients that have reached their 10th usage within a given range.

Ok, yeah, my bad. I didn't read enough...

But in that case, wouldn't simply this work?
Instead of limiting the date between a range, you only check for the max(date).
John, what do you think. I'm not 100% sure of this, but it's the first idea that comes to my mind after inner queries, but I like to go to the simplest.

select c.client_id, c.client_name, c.client_registerdate, max(u.usage_date)
from client as c
  left outer join clientusage as u on u.client_id=c.client_id  --I'm guessing the join here
where max(u.usage_date) between '2008-09-01' and '2008-11-01'
having count(u.usage_date)>=10
group by c.client_id, c.client_name, c.client_registerdate, d.usage_date

@tripy:

select c.client_id, c.client_name, c.client_registerdate, max(u.usage_date)
from client as c
  left outer join clientusage as u on u.client_id=c.client_id  --I'm guessing the join here
where max(u.usage_date) between '2008-09-01' and '2008-11-01'
having count(u.usage_date)>=10
group by c.client_id, c.client_name, c.client_registerdate, d.usage_date

Thanks for the suggestion. I appreciate your help, bro.

The clients can potentially have a max(u.usage_date) outside of the given date range. i.e. Most clients have well over 100 usages. So max(u.usage_date) would be the date of the most recent usage, which could be well past the 10th usage and well outside of the given range. I need to know if the 10th usage was within the given date range. I'm by no means a SQL expert so I could be misunderstanding your query, as I have not yet tested it.

The query I put together seems to be working, but I'm sure there is a more efficient way.

Any more suggestions?

Why are you convinced there's a more efficient way? What part bothers you, specifically? What does the execution plan say?