/* SortDateSameDayOrder.sql
   2011-12-19 ve3meo
   Alters SortDates of Birth, Christen, Baptism, Death, Cremation and Burial
   to a natural order when any pair or more occur on the same date. 
   Could be extended to order other facts also. SortDates are effectively assigned 
   (by arithmetical offsets) an absolute suffix -1, -2, ... related to the FactType.
   Affects only those events whose SortDates correspond to the Fact Date, as computed 
   by a Date encoding algorithm. The algorithm does not handle Date modifiers so not all 
   Event dates are handled, e.g. "Bef 1960". 
   2011-12-20 order of Cremation and Burial corrected
*/

UPDATE EventTable 
SET SortDate = SortDate-6692012023*(EventType 
  IN (1,3,7,2,4,5))  -- list of FactTypes we want to sort, in no particular order except this corresponds to the desired order
  +1048576*(SUBSTR('1426503',EventType,1)-1) -- the substr maps the FactType to its order
WHERE EventID 
IN (SELECT EventID FROM EventTable
    INNER JOIN 
    (SELECT -- matching dates
     SortDate, OwnerID, COUNT()-1 AS Matches FROM EventTable
     WHERE OwnerType = 0 AND EventType IN (1,3,7,2,5,4) 
     AND 
     SortDate =   -- equals encoded event Date (if not a match, suggests that user has modified SortDate so don't touch it)
       (CASE 
        WHEN Date LIKE '.%' 
        THEN 1 
        ELSE Substr(Date,3,5) END 
        +10000
        )*562949953421312 
        + Substr(Date,8,2)*35184372088832 
        + Substr(Date,10,2)*549755813888 
        + 17178820620 
      GROUP BY SortDate, OwnerID, OwnerType
     )
     USING (OwnerID, SortDate)
     WHERE Matches
    )
;