In need of help with a query

[altpro]

Hi

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.

Any help would be greatly appreciated, thanks, Steve

[swg1cor14]

Without seeing your table structure and some sample data...its hard to know what you are trying to accomplish. But I think I get it.

My suggestion is to use the command GROUP BY and group things by clientID but also do a ORDER BY datetime ASC

If you want to make a report listing like:

January
Bob Smith (131)
February
Susie Thompson (243)
Jack Horton (845)
March
...etc...etc

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

for ($i = 0, $i < count($array), $i++) {
     //for visual understanding purposes
   $month = $array[$i];
    $sql = "SELECT * FROM meetings WHERE MONTH(mtgdate) = $month GROUP BY clientID ORDER BY mtgdate";
    $qry = mysql_query($sql);
    //----an so forth
} 

I think that code will work. IF not forgive me. But maybe it will at least point u in the right direction.

//------END REPLY

[altpro]

Hi

Many thanks for that.

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.

Thanks again for your help

Steve