[Babase] POTENTIAL_DADS view can be tested-ish
Karl O. Pinc
kop at meme.com
Sun Oct 17 03:26:02 EDT 2010
Hi Susan, Lacey,
You can try out the POTENTIAL_DADS view.
(FYI for everybody else, a view that
shows who might be a dad based on
which males are in the group
during the fertile period on
and for 5 days before the zdate.)
There is as yet no documentation.
Note that there's no attempt to look
at the source of the MCE data. I'm assuming
that the manual records don't duplicate psion
data. Right?
I am still not happy with the age
computation. We can do things with
calendar years and months....
FYI, here's the query (more or less, I keep fussing):
SELECT kids.bioid AS bioid
, kids.sname AS kid
, zdates.date AS zdate
, kidmembers.grp AS grp
, pdads.sname AS pdad
, CASE
WHEN rankdates.ranked <= pdadmembers.date
THEN 'A'
WHEN maturedates.matured <= pdadmembers.date
THEN 'S'
ELSE 'O'
END
AS status
, pdadmembers.date - pdads.birth AS age_days
, trunc((pdadmembers.date - pdads.birth) / 365.25,
1) AS age_years
, (SELECT count(*)
FROM members as dadmembers
WHERE dadmembers.sname = pdads.sname
AND dadmembers.grp = kidmembers.grp
AND dadmembers.date <= zdates.date
AND dadmembers.date >= zdates.date - 5)
AS estrous_presence
, (SELECT count(*)
FROM interact_data, parts AS actor, parts AS actee
WHERE actor.sname = pdads.sname
AND actee.sname = cycles.sname
AND interact_data.iid = actor.iid
AND interact_data.iid = actee.iid
AND interact_data.date <= zdates.date
AND interact_data.date >= zdates.date - 5
AND (interact_data.act = 'M'
OR interact_data.act = 'E'))
AS estrous_me
, (SELECT count(*)
FROM interact_data, parts AS actor, parts AS actee
WHERE actor.sname = pdads.sname
AND actee.sname = cycles.sname
AND interact_data.iid = actor.iid
AND interact_data.iid = actee.iid
AND interact_data.date <= zdates.date
AND interact_data.date >= zdates.date - 5
AND interact_data.act = 'C')
AS estrous_c
FROM biograph as kids
JOIN pregs
ON (pregs.pid = kids.pid)
JOIN cycpoints AS zdates
ON (zdates.cpid = pregs.conceive)
JOIN cycles
ON (cycles.cid = zdates.cid)
JOIN members AS kidmembers
ON (kidmembers.date = zdates.date
AND kidmembers.sname = cycles.sname)
JOIN members AS pdadmembers
ON (pdadmembers.date = zdates.date
AND pdadmembers.grp = kidmembers.grp)
JOIN biograph AS pdads
ON (pdadmembers.sname = pdads.sname)
LEFT OUTER JOIN rankdates
ON (rankdates.sname = pdads.sname)
LEFT OUTER JOIN maturedates
ON (maturedates.sname = pdads.sname)
WHERE pdads.sex = 'M'
AND trunc((pdadmembers.date - pdads.birth) / 365.25, 1) >= 6
AND pdads.sname
IN (SELECT dadmembers.sname
FROM members AS dadmembers
WHERE dadmembers.sname = pdads.sname
AND dadmembers.grp = kidmembers.grp
AND dadmembers.date <= zdates.date
AND dadmembers.date >= zdates.date - 5);
Karl <kop at meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
More information about the Babase
mailing list