-- DatesLastEdited.sql
/* 2025-02-12 Tom Holden ve3meo

Gathers UTCModDates for each person's most direct or relevant records for 
analysis of when additions and edits were made. Example queries are included 
that can be selectively executed after a temporary table and 3 temp VIEWs have
been created by the first part of the script. If the whole script is executed,
the results of the last query are shown.

Developed and tested on SQLiteSpy 3.47.2 for Windows 64-bit for RootsMagic 10 database. 
The sqlean extension stats.dll is required for the calculation of median dates; 
those queries using the stats_median() function could be commented out.
-- https://github.com/nalgeon/sqlean

The following tables are not included in the scope of record dates collected:
AddressLinkTable
AddressTable
AncestryTable records for Citations, Media
CitationLinkTable records for Tasks and Associations
CitationTable
ConfigTable
ExclusionTable
FANTypeTable
FactTypeTable
GroupTable
HealthTable
MediaLinkTable records for Source, Citation, Place, Task, Name, Place Details, Association
MultimediaTable
PayloadTable
PlaceTable
RoleTable
SourceTable records
SourceTemplateTable
TagTable
TaskLinkTable
TaskTable
URLtable records other than for Person
*/

-- COLLECT RECORDS from many tables into a temporary table DatesLastEdited

DROP TABLE IF EXISTS DatesLastEdited
;
CREATE TEMP TABLE DatesLastEdited AS
-- PersonTable records
SELECT [PersonID] AS [PID], 'PT' AS [Table], PersonID AS [Record], [UTCModDate]
FROM PersonTable
 
-- NameTable records
UNION
SELECT [OwnerID] AS [PID], 'NT' AS [Table], NameID AS [Record], [UTCModDate]
FROM NameTable

-- ChildTable records
UNION
SELECT [ChildID], 'ChT', RecID, [UTCModDate]
FROM ChildTable

-- FamilyTable records for Father
UNION
SELECT [FatherID], 'FT', [FamilyID], [UTCModDate]
FROM FamilyTable

-- FamilyTable records for Mother
UNION
SELECT [MotherID], 'FT', [FamilyID], [UTCModDate]
FROM FamilyTable

-- EventTable records for Individuals
UNION
SELECT [OwnerID], 'ET', [EventID], [UTCModDate]
FROM EventTable WHERE [OwnerType]=0  -- Individuals events 

-- EventTable records for the Father spouse
UNION
SELECT FT.[FatherID], 'ET', ET.[EventID], ET.[UTCModDate]
FROM FamilyTable FT
JOIN EventTable ET ON FT.[FamilyID]=ET.[OwnerID]
WHERE ET.[OwnerType]=1 

-- EventTable records for the Mother spouse
UNION
SELECT FT.[MotherID], 'ET', ET.[EventID], ET.[UTCModDate]
FROM FamilyTable FT
JOIN EventTable ET ON FT.[FamilyID]=ET.[OwnerID]
WHERE ET.[OwnerType]=1 

-- CitationLinkTable records for Individuals
UNION
SELECT CiLT.[OwnerID], 'CiLT', CiLT.[LinkID], CiLT.[UTCModDate]
FROM CitationLinkTable CiLT
WHERE CiLT.[OwnerType]=0

-- CitationLinkTable records for Individual-type events
UNION
SELECT CiLT.[OwnerID], 'CiLT', CiLT.[LinkID], CiLT.[UTCModDate]
FROM CitationLinkTable CiLT
JOIN EventTable ET USING([OwnerID]) 
WHERE ET.[OwnerType]=0=CiLT.[OwnerType]

-- CitationLinkTable records for Family-type events for Father
UNION
SELECT FT.[FatherID], 'CiLT', CiLT.[LinkID], CiLT.[UTCModDate]
FROM FamilyTable FT
JOIN EventTable ET ON FT.[FamilyID]=ET.[OwnerID] AND ET.[OwnerType]=1
JOIN CitationLinkTable CiLT ON FT.[FamilyID]=CiLT.[OwnerID] AND CiLT.[OwnerType]=1

-- CitationLinkTable records for Family-type events for Mother
UNION
SELECT FT.[MotherID], 'CiLT', CiLT.[LinkID], CiLT.[UTCModDate]
FROM FamilyTable FT
JOIN EventTable ET ON FT.[FamilyID]=ET.[OwnerID] AND ET.[OwnerType]=1
JOIN CitationLinkTable CiLT ON FT.[FamilyID]=CiLT.[OwnerID] AND CiLT.[OwnerType]=1

-- CitationLinkTable records for persons' Names
UNION
SELECT NT.[OwnerID], 'CiLT', CiLT.[LinkID], CiLT.[UTCModDate]
FROM NameTable NT
JOIN CitationLinkTable CiLT ON NT.[NameID]=CiLT.[OwnerID]
 AND CiLT.[OwnerType]=7
--
-- MediaLinkTable records for Individuals
UNION
SELECT MLT.[OwnerID], 'MLT', MLT.[LinkID], MLT.[UTCModDate]
FROM MediaLinkTable MLT
WHERE MLT.[OwnerType]=0

-- MediaLinkTable records for Couples
-- MediaLinkTable records for Father|Husband|spouse
UNION
SELECT FT.[FatherID] PID, 'MLT', MLT.[LinkID], MLT.[UTCModDate]
FROM FamilyTable FT
JOIN MediaLinkTable MLT ON FT.[FamilyID]=MLT.[OwnerID] AND MLT.[OwnerType]=1
-- MediaLinkTable records for Mother|Wife|spouse
UNION
SELECT FT.[MotherID], 'MLT', MLT.[LinkID], MLT.[UTCModDate]
FROM FamilyTable FT
JOIN MediaLinkTable MLT ON FT.[FamilyID]=MLT.[OwnerID] AND MLT.[OwnerType]=1

-- MediaLinkTable records for Individual-type events
UNION
SELECT MLT.[OwnerID], 'MLT', MLT.[LinkID], MLT.[UTCModDate]
FROM MediaLinkTable MLT
JOIN EventTable ET USING([OwnerID]) 
WHERE ET.[OwnerType]=0=MLT.[OwnerType]

-- MediaLinkTable records for Family-type events for Father
UNION
SELECT FT.[FatherID], 'MLT', MLT.[LinkID], MLT.[UTCModDate]
FROM FamilyTable FT
JOIN EventTable ET ON FT.[FamilyID]=ET.[OwnerID] AND ET.[OwnerType]=1
JOIN MediaLinkTable MLT ON FT.[FamilyID]=MLT.[OwnerID] AND MLT.[OwnerType]=1
-- MediaLinkTable records for Family-type events for Mother
UNION
SELECT FT.[MotherID], 'MLT', MLT.[LinkID], MLT.[UTCModDate]
FROM FamilyTable FT
JOIN EventTable ET ON FT.[FamilyID]=ET.[OwnerID] AND ET.[OwnerType]=1
JOIN MediaLinkTable MLT ON FT.[FamilyID]=MLT.[OwnerID] AND MLT.[OwnerType]=1

-- DNATable records for persons
UNION
SELECT [ID1], 'DNAT', [RecID], [UTCModDate]
FROM DNATable
UNION
SELECT [ID2], 'DNAT', [RecID], [UTCModDate]
FROM DNATable

-- FANTable records for persons
UNION
SELECT [ID1], 'FANT', [FanID], [UTCModDate]
FROM FANTable 
UNION
SELECT [ID2], 'FANT', [FanID], [UTCModDate]
FROM FANTable

-- AncestryTable records for Persons only, excluding Citations and Media
UNION
SELECT [rmID], 'AnT', [LinkID], [UTCModDate]
FROM AncestryTable AnT
WHERE [LinkType]=0

-- FamilySearchTable records
UNION
SELECT [rmID], 'FST', [LinkID], [UTCModDate]
FROM FamilySearchTable FST
WHERE [LinkType]=0

-- URLTable records for Person only
UNION
SELECT [OwnerID], 'UT', [LinkID], [UTCModDate]
FROM URLTable UT
WHERE [OwnerType]=0

-- WitnessTable records
UNION
SELECT [PersonID], 'WT', [WitnessID], [UTCModDate]
FROM WitnessTable WT
WHERE [PersonID] -- exclude name-only witnesses
;

-- LIST EARLIEST Record DateTime by PID (PersonID)
DROP VIEW IF EXISTS DLEmin
;
CREATE TEMP VIEW DLEmin AS
SELECT [PID], [Table], DATETIME(MIN([UTCModDate] + 2415018.5)) AS [Earliest]
FROM DatesLastEdited
GROUP BY [PID]
;

-- LIST LATEST Record DateTime by PID 
DROP VIEW IF EXISTS DLEmax
;
CREATE TEMP VIEW DLEmax AS
SELECT [PID], [Table], DATETIME(MAX([UTCModDate] + 2415018.5)) AS [Latest]
FROM DatesLastEdited
GROUP BY [PID]
;

-- LIST MEDIAN record DateTime for each person (requires sqlean extension for stats_median())
DROP VIEW IF EXISTS DLEmedian
;
CREATE TEMP VIEW DLEmedian AS
SELECT [PID], DATETIME(stats_median([UTCModDate]) + 2415018.5) AS [Median]
FROM DatesLastEdited
GROUP BY [PID]
;

-- ++++++++++++++++++++++++++++ --
-- Selectively execute any of the following queries 

-- COUNT RECORDS for each person [PID]
SELECT
 [PID], COUNT() AS Records
FROM DatesLastEdited
WHERE [PID] -- ignore 0
GROUP BY [PID]
;

-- LIST Range of DateTimes for each person's records
SELECT DLEmin.[PID], DLEmin.[Table], DLEmin.[Earliest], DLEmax.[Latest], DLEmax.[Table]
FROM DLEmin
JOIN DLEmax
USING ([PID])
WHERE [PID] -- ignore 0
;

-- LIST Range of DateTimes + Median DateTime for each person's records
SELECT DLEmin.[PID], DLEmin.[Table], DLEmin.[Earliest], DLEmedian.[Median], DLEmax.[Latest], DLEmax.[Table]
FROM DLEmin
JOIN DLEmax
USING ([PID])
JOIN DLEmedian
USING ([PID])
WHERE [PID] -- ignore 0
;


-- LIST DateLastEdited for all records for a given person
--- This could be revised to use a Run-Time Variable for the person's PID
SELECT *, DATETIME([UTCModDate] + 2415018.5) AS [DateTime]
FROM DatesLastEdited
WHERE [PID]=428  -- edit for desired person
ORDER BY [UTCModDate]
;

-- LIST DISTRIBUTION BY DateTime of all Records
SELECT DATETIME([UTCModDate] + 2415018.5) AS DateTime, COUNT()
FROM DatesLastEdited
GROUP BY DATETIME([UTCModDate] + 2415018.5)
;

-- LIST DISTRIBUTION BY DATE of all Records
SELECT DATE([UTCModDate] + 2415018.5) AS Date, COUNT()
FROM DatesLastEdited
GROUP BY DATE([UTCModDate] + 2415018.5)
;