Hi. A little explanation of the table usage would have been nice. E.g. it's not clear to me, if shift 232 is also listed in the "shifts" table or not. I presume the latter from now (in which case "shifts" is more like a "did_show_shifts").
Also, you did not specify what special cases are to be taken care of. E.g. can there be volunteers which do not have a shift assigned yet? Can there be a shift in the future? And so on... I presume that the tables reflect complete recordings _after_ each shift. On Fri, Feb 15, 2002 at 11:38:29PM -0800, [EMAIL PROTECTED] wrote: > I'm stumped -- which isn't saying much because I'm hardly a scientist. > I am struggling to write a query that will tell me how many times a new > volunteer ( defined as a volunteer who has never worked a shift ) did > not show up for his shift (first shift). From my data I would > eventually like to be able to say: a brand new volunteer does not show > up for his shift 1 in x times. Well, either one of my presumptions is false or a different layout would probably better, namely: shifts SID | WHEN | VID | SHOW ------------------------------- 230 | 2000-01-28 | 584 | yes 231 | 2000-02-01 | 485 | yes 232 | 2000-02-01 | 259 | no 233 | 2000-02-03 | 147 | yes ... (avoiding DATE because it's a reserved word) In this case your question would be a variation of the one described in the tutorial section of the manual (getting the rows for the group-wise smallest (=date) value of a column): http://www.mysql.com/doc/e/x/example-Maximum-column-group-row.html Your question is like "for each volunteer, find whether he showed up (=SHOW) for his first shift (minimum WHEN)". See the metioned manual section for more detail. So this would be something like CREATE TEMPORARY TABLE tmp SELECT VID, MIN(WHEN) AS WHEN FROM shifts GROUP BY VID; SELECT t.VID, t.DATE, s.SHOW FROM tmp t, shifts s WHERE s.VID=t.VID AND s.WHEN=t.WHEN; This gives you, for each volunteer, when his first shift was and if he showed up. You want to know, the percentage of how often a volunteer misses his first shift in avarage. You get this, if you replace the SELECT clause by: SELECT AVG( IF( s.SHOW = 'no', 1, 0 ) ) FROM ... In MySQL a comparision returns 1, 0 or NULL and therefore this can be abbriviated as SELECT AVG( s.SHOW = 'no' ) FROM ... The disadvantage of using AVG() to evaluate the number of posivite hits is that the query has to go through the whole tmp table and cannot use indexes. So, if your tables are not small (>10000 rows) or you do this query not only once, you want to use the following instead: (of course, this presume that you created an index on tmp.SHOW!) SELECT @volunteers := COUNT(VID) FROM volunteer; SELECT COUNT(*) / @volunteers FROM tmp t, shifts s WHERE s.VID=t.VID AND s.WHEN=t.WHEN AND s.SHOW='no'; This presumes, that there are no VID in volunteer, that are not in shifts and vice versa. If that's not true, use instead SELECT @volunteers := COUNT(DISTINCT v.VID) FROM volunteer v, shifts s WHERE v.VID = s.VID Okay, so your final queries could look like (the simple way) SELECT @volunteers := COUNT(VID) FROM volunteer; CREATE TEMPORARY TABLE tmp SELECT VID, MIN(WHEN) AS WHEN FROM shifts GROUP BY VID; SELECT AVG( s.SHOW = 'no' ) FROM tmp t, shifts s WHERE s.VID=t.VID AND s.WHEN=t.WHEN; Now back to your original table layout. Without going in-depth again, I guess the following should work (presuming that SID in shifts and no_show_shifts are distinct): SELECT @volunteers := COUNT(VID) FROM volunteer; CREATE TEMPORARY TABLE tmp SELECT VID, MIN(WHEN) AS WHEN, 'yes' AS SHOW FROM shifts GROUP BY VID; INSERT INTO tmp SELECT VID, MIN(WHEN), 'no' AS SHOW FROM no_show_shifts GROUP BY VID; CREATE TEMPORARY TABLE tmp2 SELECT VID, MIN(WHEN) AS WHEN FROM tmp GROUP BY VID; SELECT AVG( s.SHOW = 'no' ) FROM tmp2 t, tmp s WHERE s.VID=t.VID AND s.WHEN=t.WHEN; Or with a more complex SELECT (don't know if it's faster or slower without testing): SELECT @volunteers := COUNT(VID) FROM volunteer; CREATE TEMPORARY TABLE tmp SELECT v.VID, MIN(s.WHEN) AS WHEN_YES, MIN(n.WHEN) AS WHEN_NO FROM shifts LEFT JOIN shifts s ON s.VID=v.VID LEFT JOIN no_show_shifts n ON n.VID=v.VID GROUP BY VID; SELECT AVG( IS_NULL(WHEN_YES) OR ( !ISNULL(WHEN_NO) AND WHEN_NO<WHEN_YES ) ) FROM tmp t, shifts s WHERE s.VID=t.VID AND s.WHEN=t.WHEN; Btw, this presume that not both, MIN(shifts.WHEN) and MIN(no_show_shifts.WHEN) can be NULL for a certain VID at the same time, i.e. that for each VID there is a row either in shifts or in no_show_shifts. Of course, in MySQL 4.x, you could use UNION to simplify that query. But using UNION is a sign for strange table design (justified by needs or not) and this is why I started my first example with a changed table layout. And, as initally said, maybe I made the false assumptions for the gaps. And depending on your needs, you may want to lock the tables for the queries as shown in the mentioned manual page. So, and now... was I blind and someone shows me that there is a far easier way? ;-) Bye, Benjamin. PS: Ah well, and note, that I did test none of the queries, so beware of typos and so on. On Fri, Feb 15, 2002 at 11:38:29PM -0800, [EMAIL PROTECTED] wrote: > I have three tables: > > volunteer > VID | Name > ----------------- > 524 | Joe Doe > 254 | Karen Smith > 485 | Bob Nesbit > > shifts > SID | DATE | VID > --------------------------- > 230 | 2000-01-28 | 584 > 231 | 2000-02-01 | 485 > 233 | 2000-02-03 |147 > 234 | 2000-02-04 | 584 > > no_show_shifts > SID | DATE | VID > --------------------------- > 232 | 2000-02-01 | 259 > 239 | 2000-02-08 | 369 > > > Is it possible to write a query that will solve this problem? If so, > can anyone tell me how I can do it? > If not what other data do I need? Any help is appreciated. > > Richard > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
