If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

In need of help with a query

I'm working on a client management system and one of the report requirements has got the better of me.

I have a table which is used to log all meetings with a client, the relevant columns being id, clientid & datetime.

I need to generate a report which shows years and months with the number of clients who had their 4th meeting in each.

I know the logic is to get the clientid and date of their 4th meeting then count clientids grouped by year & month of that meeting but have got myself lost in a mess of queries, subqueries and groupings.

The main issue I have is getting the clientid and date of 4th meeting, some have less than 4 and shouldn't be counted at all, some more and should only be counted on the date of the 4th, I'm beginning to think some kind of loop may be required.

Then I would first turn the month listings into an array (because date function requires 2 digits (01, 02, 03, 04, 05...) and a while or for loop only starts with 1 digit (0, 1, 2, 3, 4, 5...). Then do something like

It's not quite what I was looking for, I've been reading MySQL documentation and forums for days but still haven't been able to come up with an SQL only solution, have gained a lot of valuable knowledge for other projects though.

I've now taken your suggestion and used php to create a couple of loops and bring everything together, it's really not pretty and I'm sure there must be a better way but the job's done and the report is only generated once a month so efficiency is not a huge concern.