/* DeathBurialDelay.sql
2024-05-22 Tom Holden ve3meo
Lists record numbers (RINs) of people having a Burial event 
more than 30 days after their Death event
*/
SELECT ED.OwnerID AS RIN, ED.Date AS DeathDate, EB.Date AS BurialDate
      ,SUBSTR(ED.Date,4,4) ||'-'|| SUBSTR(ED.Date,8,2) ||'-'|| SUBSTR(ED.Date,10,2) AS Died -- ISO-8601 format
      ,SUBSTR(EB.Date,4,4) ||'-'|| SUBSTR(EB.Date,8,2) ||'-'|| SUBSTR(EB.Date,10,2) AS Buried
FROM EventTable ED 
JOIN EventTable EB
 USING (OwnerID) -- Record Number (RIN) of Person for Individual events
WHERE ED.EventType=2 --Death 
  AND EB.EventType=4 --Burial
  AND ED.Date NOT LIKE '.' -- not blank
  AND EB.Date NOT LIKE '.' 
  AND (JulianDay(Buried)-JulianDay(Died)) > 30 -- more than 30 days from Death to Burial
ORDER BY RIN -- sorted by record number
;