[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