[Babase] POTENTIAL_DADS view can be tested-ish

Susan Alberts alberts at duke.edu
Sun Oct 17 13:51:32 EDT 2010


> Hi Susan, Lacey,
>
> You can try out the POTENTIAL_DADS view.

I have done so. very nice. Quite slow browsing when I first brought it  
up, but that might have been the connectiivty problems you and Ryan  
mentioned. On the other hand, it is a big query to build to 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.

OK, standing by for that.
>
> 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?

That is correct; I check with the team on this about once every year  
or so.
>
> I am still not happy with the age
> computation.  We can do things with
> calendar years and months....

I don't understand what you don't like or what you are proposing. I  
like the age columns.

One thing that should be fixed is the count of days the male was  
present in the female's five-day window. Currently we have 6 possible  
days because I think you counted Z-day as well as the five days prior.  
We only want the five days prior.

Thanks for sending the query, I have not looked through it in detail  
but will if you want.

Susan

>
> 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
>
>
> _______________________________________________
> Babase mailing list
> Babase at www.eco.princeton.edu
> http://www.eco.princeton.edu/mailman/listinfo/babase

------------------------------------------------------------
Susan Alberts, Professor of Biology, Duke University, Box 90338,  
Durham NC 27708. Phone 919-660-7272, FAX 919-660-7293






More information about the Babase mailing list