any help with this sql...

[ironchef]

Hi all,

Trying to get this sql to work properly.

What the query is supposed to do is show individual volunteers that have not achieved minimum required hours for Bike Patrol. The must have a total of 40 hours, 30 of which must be classified as "Bike Patrol" (JobID=6) and the remaining 10 may be classified as either "Bike Patrol" (JobID=6), "Bike Patrol Special Events" (JobID=12), or "Bike Patrol Special Projects" (JobID=13).

I can get the query to filter the minimum hours for bike patrol, but cannot integrate the other criteria. Appreciate any help!


SELECT DISTINCTROW JobHours.Date
, [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & "." AS VolunteerName
, JobStatus.Status
, Jobs.Job
, Jobs.Minimum
, Sum(JobHours.Hours) AS SumOfHours
FROM Volunteers
INNER JOIN ((Jobs
INNER JOIN (JobStatus
INNER JOIN VolunteerJobs
ON JobStatus.JobStatusID = VolunteerJobs.JobStatusID)
ON Jobs.JobID = VolunteerJobs.JobID)
INNER JOIN JobHours
ON VolunteerJobs.VolunteerJobID = JobHours.VolunteerJobID)
ON Volunteers.VolunteerID = VolunteerJobs.VolunteerID
WHERE (((Jobs.JobID)=6 Or (Jobs.JobID)=12 Or (Jobs.JobID)=13))
GROUP BY JobHours.Date, [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & "."
, JobStatus.Status
, Jobs.Job
, Jobs.Minimum
HAVING (((JobStatus.Status)="Active"
Or (JobStatus.Status)="Being Trained")
AND ((Sum(JobHours.Hours))<[Jobs].[Minimum]))
ORDER BY [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & ".";

[raf]

I would create a crosstab with the agregated data.

If i understand it correctly, then you basically need a crosstab with the JobID's in the columns (like jobID6, jobID12), and the volunteersID's in the rows, and the sum of all entrys for the JobID-VolunteerID in the cells. If you don't need a timedimension (for instance, You can do this with a loop of And then you want to have a condition like

where jobID6 >= 40 or (jobID6 >= 30 and (jobID12 >= 10 or jobID13))

which i would use to set a flag in an extra column (like 'achieved_goal') with

UPDATE crossJobVol SET achieved_goal = 1 where jobID6 >= 40 or (jobID6 >= 30 and (jobID12 >= 10 or jobID13))

From there on, it's easy to link the volunteer to the volunteerstable. The jobdata is completely independent of the volunteersdata so i don't see why you would want to retrieve both with one select. Your blowing up the internaly used recordsets and the amount of records with it, and this query will most likely not be very performant.
If you use this agregated table (also known as MQT's --> materialised query table) then performance will be very good, because you only need a 2 table join on the crosstab and the volunteerstable. (for the jobsinfo, its a straight select with the jobID's in an In-clause (where JobID In (6,12,13, ...) And you could add an extra (indexed) variable to the crosstab to add the timedimension, like 'yearmonth' which contains value like 200401, 200402 etc) so that you can get the overview for a specific month.
It's a classical situation where a bit of data-denormalisation will be the best sollution.

The key issue is how you need to create the MQT. This depends on your requirements. Their are 2 main requirements i can think of right now: 'up to dateness' (If you only need the overview at the end of the month or at any given moment in time) and 'timedimension' (just an increment over any period of time, or for each month)
To me, it sounds like monthly computed overview data.
For each of the requirements-combination, you need to apply a differnt strategy to keep the MQT in sync with your Jobhours-table.

For monthly computed, you need to append the agregated data each month. Unique key is a multifield key on volunteerID and the yearmonth. You best use an i
nsert into tablename (volunteerID) select distinct volunteerid from jobhours where extract(YEAR_MONTH from datevariable) = 200401
Then an update on the table to set the yearmonth
Then you need a select for each job. Like
select sum(jobID) as totalforjob, volunteerID jobID from Jobhours GROUP BY volunteerID where volunteerID In (select distinct volunteerid from jobhours where extract(YEAR_MONTH from datevariable) = 200401) and JobID=6
Then you need to loop through the recordst and updat your MQT like
"update tablename set Job6 =" . $row['totalforjob'] ." where volunteerID=" . $row['volunteerID']
It might be done more efficient but you'll see that when you try it out.

If it needs to be accurate at any time, then you need to update the QMT on each datamanipulation of JobHours. Normally this is done with stored procedures (triggers) but mySQL doesn't support that yet, so you need to take care of it inside your application --> each time you insert, update, delete hours in JobHours, you need to perform a similar query on the MQT.
These will be the simplest querys, but they will be scatered all over your app (it's a shame mySQL doesn't support triggers)

If its a monthly process, without timedimension, then you need to truncate the table like
truncate table tablename
and then run a
insert into tablename (volunteerID, jobID6) select volunteerID, sum(jobID) from JobHours Group by volunteerID where jobID=6
then a
select volunteerID, jobID, sum(jobID) as tothours from JobHours group by volunteerID, jobID where jobID In (12, 13) order by volunteerID
then loop throught this recordset and update the MQT.

It might all look complicated, but once you've set it up, it will very quickly delever you the data you need.

If you don't want to use an MQT, then a simple
select volunteerID, jobID, sum(jobID) as tothours from JobHours group by volunteerID, jobID order by volunteerID
would also do IF you process it inside your server side scripting to check the conditions (wount be that hard, but requires more resourcs then if the data is precomputed and stored in the MQT. If you add a join to the volunteerstable then you'll basivally have what you need.