WITH Burials AS
(
/*

set_burial_sort_date.sql   Jerry Bryan  8 Feb 2026

CTE to extract Sortdate as YYYY, MM, and DD. It is a very
incomplete extraction because it doesn't take into account
date modifiers such as BEFORE, AFTER, etc. nor does it take
into account that Sortdate uses the the second date as a
tie breaker when there is a date range with two dates and
the first dates match. Nevertheless, this particular SortDate
extraction is all we need for this project because all we
need to know is if SortDate is year only or year/month only.
Also, the extracted year, month, day are only valid when
SortDate does not consist only of 1 bits. But if SortDate
consists only of 1 bits, this project has nothing to do
anyway so this case is excluded automatically.

A few extra columns are created in case they are needed.
The can be ingnored when using the CTE if they are not needed.
*/
    SELECT E.EventID,
    E.Date, 
    E.SortDate,    
    (E.SortDate >> 49) - 10000 AS SortYYYY,
    (E.SortDate >> 45) & 0x0f AS SortMM,
    (E.SortDate >> 39) & 0x3f AS SortDD,
    E.OwnerID
    FROM EventTable AS E
    JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType AND FT.Name = 'Burial'
    WHERE E.SortDate != 0x7fffffffffffffff
)

/*

Turn off SortDate (set it high) for Burial facts where
the current SortDate is year only or year/month only.
In other words, to avoid being turned off, SortDate
must include both month and day.

*/


UPDATE EventTable
SET SortDate = 0x7fffffffffffffff     -- value that removes SortDate
WHERE EventID IN
(
SELECT EventID
FROM Burials WHERE SortMM = 0 OR SortDD = 0
)