Delete burial dates which only contain the year or month and year
I have found that when importing individuals or families from FamilySearch, very frequently there is a burial date which only contains the year or the month and year instead of a full date. This is really useless information and I would like to be able to delete all burial dates that are not full dates. I was thinking maybe it would be possible to delete those that only contain 4 characters (i.e. "1900" and those that contain only 8 characters (i.e. "Jan 1900"). If there is not a burial location or description, then I would like to delete the entire fact, but if there is a burial location or text in the description, I obviously want to keep that information, so I don't want to delete the entire fact, just the useless dates. I hope this makes sense.
I'm going to offer you three options. You probably won't like Option #1 or Option #2 because they only find the Burial facts that need to be fixed, and expect you to do the fix manually in the RM user interface.
Here is option #1. Just run it to get a list of Burial facts that need to be fixed, and work the list manually.
SELECT E_Burial.OwnerID, E_Burial.EventID,
E_Burial.Date AS Bu_Date,
E_Death.Date AS De_Date,
SUBSTR(E_Burial.Date,2,1) AS B_DateType,
SUBSTR(E_Burial.Date,8,2) AS B_MM,
SUBSTR(E_Burial.Date,10,2) AS B_DD
FROM
(
SELECT *
FROM EventTable
WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE 'Burial')
) AS E_Burial
LEFT JOIN
(
SELECT *
FROM EventTable
WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE 'Death')
) AS E_Death ON E_Death.OwnerID = E_Burial.OwnerID
WHERE B_MM LIKE '00' OR B_DD LIKE '00'
ORDER BY B_DateType DESC, B_MM, B_DD
Here is option #2. Just run it to get a list of Burial facts that need to be fixed, and work the list manually. The difference is that I have done a GROUP BY. With a GROUP BY, there is only one item on the screen, or only a few. Work what is on the screen and do a refresh. New unworked items will appear until you have done them all. There is a counter of how many are left. I find this easier to work with than option #1, but that's just me.
SELECT E_Burial.OwnerID, E_Burial.EventID,
E_Burial.Date AS Bu_Date,
E_Death.Date AS De_Date,
SUBSTR(E_Burial.Date,2,1) AS B_DateType,
SUBSTR(E_Burial.Date,8,2) AS B_MM,
SUBSTR(E_Burial.Date,10,2) AS B_DD,
COUNT(*)
FROM
(
SELECT *
FROM EventTable
WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE 'Burial')
) AS E_Burial
LEFT JOIN
(
SELECT *
FROM EventTable
WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE 'Death')
) AS E_Death ON E_Death.OwnerID = E_Burial.OwnerID
WHERE B_MM LIKE '00' OR B_DD LIKE '00'
GROUP BY B_DateType, B_MM, B_DD
ORDER BY B_DateType DESC, B_MM, B_DD
Option #3 is automatic. You just run it, and you are done. But I don't like it because it makes no attempt to set the sort date for the burial fact to be a sort date that keeps the burial fact in the correct order. To be honest, I suspect it doesn't matter because it will leave the existing sort date for the Burial fact unchanged which is probably ok. However, I'm discovering that my Burial facts that are year only are accompanied by a Death fact that is year only, and that they are in the correct order only by accident. If I were doing data entry on year only Deaths and Burials today, I would leave the Burial Date blank and set sort dates to put the Death and Burial facts into the correct order. But that is a much more complicated script. You will notice that my scripts have all picked up but the Death Date and a Burial Date on the theory that it would make a future script to clean up the year only burial dates while setting sort dates correctly much easier. But I have not yet tried to write that logic. In the meantime, the following should work as option #3, but I have not yet tested it. I simply used the option #1 query as a subquery, except I had to double nest it to be able to pull out just the EventID column. The original Option #1 query had to have more than one column. This double nesting was the easiest "quick and dirty" way I could think of to reduce the number of columns to 1 as required.
UPDATE EventTable
SET Date = '.' -- blank burial date
WHERE EventID IN
(
SELECT A.EventID
FROM
(
SELECT E_Burial.OwnerID, E_Burial.EventID,
E_Burial.Date AS Bu_Date,
E_Death.Date AS De_Date,
SUBSTR(E_Burial.Date,2,1) AS B_DateType,
SUBSTR(E_Burial.Date,8,2) AS B_MM,
SUBSTR(E_Burial.Date,10,2) AS B_DD
FROM
(
SELECT *
FROM EventTable
WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE 'Burial')
) AS E_Burial
LEFT JOIN
(
SELECT *
FROM EventTable
WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE 'Death')
) AS E_Death ON E_Death.OwnerID = E_Burial.OwnerID
WHERE B_MM LIKE '00' OR B_DD LIKE '00'
ORDER BY B_DateType DESC, B_MM, B_DD
) AS A
)
Note: There was a bug in my original posting. I have now edited the posting to fix the bug. Namely, the definition of B_DD is SUBSTR(E_Burial.Date,10,2) AS B_DD rather than SUBSTR(E_Burial.Date,8,2) AS B_DD. The bug does not prevent any of the three scripts from running, and probably would not even prevent it from producing the correct results. But please use the corrected version rather than the original version in any case.
Correction to my correction: the fixed code is correct, but my comment about the new code not mattering was not correct. When I ran the fixed code in my database, it identified additional burials that needed my attention. Namely, it identified burials with a year and month and without a day. I'm really surprised. Those additional burials that need my attention apparently came into my database decades ago via GEDCOM import. I never entered burial dates that way myself.
Jerry, as usual, you are so thorough with your scripts and testing. Thank you for your solution. I will try it out soon and let you know how it goes. Jaime