[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