[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