[Babase] query for marie

Susan Alberts alberts at duke.edu
Thu May 10 22:19:27 EDT 2007


Marie,

I think this query does it.

Note that I have restricted the dates to just five years to reduce  
the amount of processing time. Run this query and check some of the  
results against the handwritten data or against a check of cycpoints.  
If it works then you can change the dates to cover all the months  
that you want to check.

I think that there were two problems with the previous query:

Main problem: The connection of members.date = cycpoints.date was  
missing -- we only connected members.sname to cycles.sname. This  
meant that every date in members was linking to every row in  
cycpoints where sname matched and it returned thousands of extra  
lines for every ddate.

Secondary problem: we selected on members.date instead of  
cycpoints.date. I am not sure whether this was a problem or not but  
on thinking about it I realized that the date in cycpoints was the  
relevant date (it was the D date) and not the date in members.


select count(cycles.cid), members.grp,
date_part('month', cycpoints.date) as month,
date_part('year', cycpoints.date) as year
from cycles, cycpoints, members
where cycles.cid = cycpoints.cid and cycles.sname = members.sname
and cycpoints.date = members.date
and cycpoints.date between '2000-01-01' and '2005-12-31'
and cycpoints.code = 'D'
group by members.grp, month, year

Let me know if it works.

Susan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.eco.princeton.edu/pipermail/babase/attachments/20070510/02dc457f/attachment.html


More information about the Babase mailing list