Logo Home    Page list

Dates - Same Day Sort Order #date #sortdate #update

Table of Contents

SortDateSameDayOrder.sql
SortDateSameDayOrderCustom.sql
Some RootsMagic users bemoan the fact that events having the same date are displayed and reported in an unnatural or illogical order, having more to do with the order they were entered. Burial before Death or Christen before Birth are often mentioned. RootsMagic does provide the user with the option to modify the SortDate date to establish the desired order.

RM's proprietary SortDate is lost in an export to another program; any import into RM from anything other than a RM database or an RM generated GEDCOM with the Extra Details (RM specific) box checked will be assigned SortDates matching the event date resulting in the probable loss of the desired order. So manual control over the SortDate is of no comfort to those who move their database between different programs to take advantage of their respective strengths. An enhancement to provide better results for the order of same day events has long been on the RootsMagic Wish List.

Meanwhile, here are two SQLite queries that can re-order all same day events of limited types in seconds with results as shown in the screenshots below. They both exploit the SortDate feature that values of the form date-n, where n is an integer from 1 to 4999, are sorted in ascending order.
Dates-SameDaySortOrder.png
Before and after the query was applied.


SortDateSameDayOrder.sql

SortDateSameDayOrder.sql, currently addresses the following in the order listed: Birth, Christen, Baptism, Death, Cremation and Burial. Others could be added. Should work with any SQLite manager on both RM4 and RM5 databases. Currently deprecated because it changes other facts on the same day; left here because its comparative simplicity may help to understand its successor below.
/* 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
    )
;

SortDateSameDayOrderCustom.sql

SortDateSameDayOrderCustom.sql RMtrix_tiny_check.png
Goes to the next level, supporting the ranking of any number of facts, be they standard or custom. It also corrects an oversight in SortDateSameDayOrder.sql and does not touch unranked facts on the same date. Requires the use of a fake RMNOCASE collation or possible revision with COLLATE NOCASE phrases where FactTypeTable.Name is used. Because SortDate supports up to date-4999, this query could be modified to have a multiplier of the rank, thus leaving room for other events on the same date to be manually added with a SortDate lying between those assigned by the query, without having to revise the existing SortDates.
/* SortDateSameDayOrderCustom.sql
   2011-12-20 ve3meo
   Alters SortDates of any set of Fact types
   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 rank of 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".
*/
DROP TABLE IF EXISTS TmpFactOrder
;
CREATE TEMP TABLE IF NOT EXISTS TmpFactOrder
(Rank INTEGER PRIMARY KEY, FactName TEXT)
;
 
/* list of Fact Names, standard and custom, to be sorted, in rank order.
   Revise the list to suit your needs */
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Birth');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Christen');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Baptism');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Death');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Funeral');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Cremation');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Burial');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Obituary');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Memorial');
/* revise SortDates */
UPDATE EventTable
SET SortDate = SortDate
  -6692012023  -- this offset goes to Date-1 if the event is a ranked event
  *( (
      SELECT Rank
      FROM TmpFactOrder,
           FactTypeTable
      WHERE FactName LIKE Name
      AND FactTypeID = EventType
      )>0
    )
  +1048576  -- this offset adds steps of 1 to Date-1 multiplied by (rank-1)
  *( (
      SELECT Rank
      FROM TmpFactOrder,
           FactTypeTable
      WHERE FactName LIKE Name
      AND FactTypeID = EventType
      )-1
    ) -- 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
     INNER JOIN FactTypeTable
     ON EventType = FactTypeID
     WHERE EventTable.OwnerType = 0
     AND Name IN (SELECT FactName FROM TmpFactOrder)
     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, EventTable.OwnerType
     )
     USING (OwnerID, SortDate)
     INNER JOIN FactTypeTable
     ON EventType = FactTypeID
     WHERE Matches
     AND EventTable.OwnerType = 0
     AND Name IN (SELECT FactName FROM TmpFactOrder)
    )
;
 

Discussions

chuckfilteau

Alternate method using the TmpFactOrder table

chuckfilteau 05 November 2016 18:50:42

Thanks for the algorithm and SQL code to set the SortDate.
From it, I created the TmpFactOrder table, but I added two additional columns (both integers)
FT - for the FactType that matched the FactName
ADJ - to pre-calculate each adjustment for each Rank
I then coded two simple update statement to update this table

â—¦Rationale - do the work only once, in this table, rather than redoing the work in SQL for each fact that had duplicate names **
UPDATE TmpFactOrder SET FT = (Select FactTypeID from FactTypeTable where FactName = Name);
UPDATE TmpFactOrder SET ADJ = ((Rank-1)*1048576) - 6692012023;

Finally, I came up with a much simpler, easier to understand, SQL statement that updated the SortDate for all events that had duplicate dates (for standard single date values)
Here is that statement:
UPDATE EventTable
SET SortDate=
((Substr(DATE,3,5) +10000)*562949953421312 -- year
+ Substr(DATE,8,2)*35184372088832 -- month
+ Substr(DATE,10,2)*549755813888 -- day
+ 17178820620 -- constant (time?)
+ (Select ADJ from TmpFactOrder -- Rank adjustment (-1 to -20)
where EventType = FT)
)
where EventID in
(Select distinct E1.EventID -- get the list of IDs that need to be changed
from EventTable as E1
JOIN EventTable as E2
Using (OwnerID,Date)
where E1.OwnerType=0
AND E1.[Date] like "D.%" -- only events that have a single standard date
AND E1.EventType IN (Select FT from TmpFactOrder)
AND E1.EventID <> E2.EventID -- DISCARD MATCHES ON THE SAME ROW
)
;
/* ALL DONE */

thejerrybryan

Minor "problem" with the handling of sort dates

thejerrybryan 11 September 2017 15:35:28

I am playing around with Tom's original SortDate code rather than with chuckfilteau's SortDate code. The code is very tricky (to me, at least), and the "problem" I have identified is most likely in myself rather than in Tom's code.

I am using SQLite to add a Parents fact to each person who has parents. Having done so, I want the sort dates set such that the sort date for the Birth fact becomes birthdate_fact_date-1 and the sort date for the Parents fact becomes parents_fact_date-2, where birthdate_fact_date and parents_fact_date are the same date. So in addition to requiring a person actually to have parents before adding a Parents fact, my SQLite code also checks for the presence of a Birth fact and only adds the Parents fact if there is a Birth fact. For the newly added Parents fact, my code sets the Date, SortDate, and EditDate to be the same as for the Birth fact with other Parents fact fields being set appropriately for the Parents fact.

Having run my code to add the Parents fact, I copy Tom's SortDate code, add my Parents fact to the list of facts, and run my copy of Tom's code. All is mostly well, but all is not completely well. For an example when all is well, if the original birth date and sortdate is 18 May 1819, then my code sets the Parents fact date and sortdate to 18 May 1819. Tom's code sets the Birth fact sortdate to 18 May 1819-1 and the Parents fact sortdate to 18 May 1819-2. Perfect.

For an example of when all is not well, I have a person with a birth date of 1790 and a sort date of 1 Jul 1790. So my newly added Parents fact also gets a date of 1790 and a sort date of 1 July 1790. Under these conditions, Tom's code does not adjust the sort dates to 1 Jul 1790-1 and 1 Jul 1790-2, respectively. It thinks I have already set sort dates manually that should not be overridden. If before running Tom's code, I change both sort dates to 1790, then Tom's code changes the sort dates to 1790-1 and 1790-2, respectively. Perfect, except that all the sort dates in my database seem to be 1 Jul 1790 for year dates and something like 15 Mar 1790 for month dates like Mar 1790. And curiously, when I add a new year only date by hand, RM sets the sort date just to the year and not to 1 Jul of the year.

For another example of when all is not well, I have person with a birth date of Abt 1725 and a sort date of 1 Jul 1725. My code adds a Parents fact with a date of Abt 1725 and a sort date of 1 Jul 1725. Again, Tom's code does not adjust the respective sort dates. I may not be understanding the code correctly, but I think the problem is not with the "Abt" modifier but again it's because the code sees both events as having a 1 Jul 1725 sort date. If I manually change both sort dates to just plain 1725, then Tom's code is not bothered by the "Abt" modifier and it does change the sort dates to 1725-1 and 1725-2, respectively. Again, this is perfect except that all my Abt dates for years have sort dates of 1 Jul for the appropriate year. I remember the use of 1 Jul sort dates being a standard RM technique for year only dates and my database is full of them, but now if I add a new year only fact by hand, the sort date is the year only without the 1 Jul. Has something changed in RM and I didn't notice?

I thought I had come to a sufficient understanding of the sort date code to adjust it to meet my needs, but so far my efforts have failed. I can't get it to override a Birth sort date of 1 Jul 1790 and a Parents sort date of 1 Jul 1790. I want the dates to become 1 Jul 1790-1 and 1 Jul 1790-2, respectively. Therefore, any advice would be most gratefully accepted.

thejerrybryan
thejerrybryan 11 September 2017 16:37:37

Aha (I think)!

I commented out the following code, and it seems to have solved my problem. I was trying to make changes within the commented out code, and I just needed to get rid of the commented out code entirely.


/* 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 */

I would be interested in opinions concerning the following observation. It seems to me that commenting out the code in question might be a good idea for the general use case, not just for my specific use case. Which is to say that even with this code removed from the script, the script still does not modify sort dates that are different. For example, if the RM user has established a birth sort date of 1 Jul 1850-1 and a death sort date of 1 Jul 1850-2, then the script will not modify the sort dates even with the code commented out as indicated. The sort dates are not identical and are therefore are prevented from matching by INNER JOIN's. They don't need to be prevented from matching by the code that has been commented out. At least that's the way it seems to me, and that's what I'm going to go with for now.

www.000webhost.com