Quote from kevync on 2026-02-28, 8:21 pm

Tweaked this is AI

Key Logic:

• Age Calculation: (w.StartYear - p.BirthYear) determines the person's age at the onset of the conflict.

• Adult Threshold: Set at 18. Anyone who was 17 or younger when the war started is labeled a Child for that specific war.

• Born Status: Anyone born during a war is automatically labeled a Child

• Strict Filtering: The People CTE now uses AND BirthYear > 0 AND DeathYear > 0. If even one date is missing, the person is omitted.

• Simplified LifeSpan: Since we are now guaranteed to have both years, the CASE logic for blank years has been removed for a cleaner Birth-Death string.

• Precision: This provides a much more accurate audit for genuine historical overlaps, as it removes the "noise" of people with only birth or death dates.

 

WITH WarDates(War, StartYear, EndYear) AS (
SELECT 'Amer Rev', 1775, 1783 UNION ALL
SELECT '1812', 1812, 1815 UNION ALL
SELECT 'Indian', 1817, 1898 UNION ALL
SELECT 'Mexican', 1846, 1848 UNION ALL
SELECT 'Civil', 1861, 1865 UNION ALL
SELECT 'Span-Amer', 1898, 1902 UNION ALL
SELECT 'WW1', 1917, 1918 UNION ALL
SELECT 'WW2', 1941, 1945 UNION ALL
SELECT 'Korean', 1950, 1953 UNION ALL
SELECT 'Vietnam', 1964, 1975
),
People AS (
SELECT OwnerID AS PID, Surname, Given, BirthYear, DeathYear
FROM NameTable
-- Omit if lifespan is not complete (requires both years > 0)
WHERE isPrimary != 0
AND NameType = 0
AND BirthYear > 0
AND DeathYear > 0
),
CalculatedStatus AS (
SELECT
p.PID, p.Surname, p.Given, p.BirthYear, p.DeathYear, w.War,
'Died' AS Status,
CASE WHEN (w.StartYear - p.BirthYear) >= 18 THEN 'Adult' ELSE 'Child' END AS AgeStatus
FROM People p
JOIN WarDates w ON p.DeathYear BETWEEN w.StartYear AND w.EndYear

UNION ALL

SELECT
p.PID, p.Surname, p.Given, p.BirthYear, p.DeathYear, w.War,
'Alive' AS Status,
CASE WHEN (w.StartYear - p.BirthYear) >= 18 THEN 'Adult' ELSE 'Child' END AS AgeStatus
FROM People p
JOIN WarDates w ON p.DeathYear >= w.StartYear AND p.BirthYear <= w.EndYear

UNION ALL

SELECT
p.PID, p.Surname, p.Given, p.BirthYear, p.DeathYear, w.War,
'Born' AS Status,
'Child' AS AgeStatus
FROM People p
JOIN WarDates w ON p.BirthYear BETWEEN w.StartYear AND w.EndYear
),
MultiWarPeople AS (
SELECT PID
FROM CalculatedStatus
GROUP BY PID
HAVING COUNT(DISTINCT War) > 1
)
SELECT
CS.PID,
CS.Surname,
CS.Given,
(CS.BirthYear || '-' || CS.DeathYear) AS LifeSpan,
COUNT(DISTINCT CS.War) AS WarCount,
GROUP_CONCAT('#' || CS.War || ' (' || CS.Status || ' - ' || CS.AgeStatus || ')', ', ') AS WarDetails
FROM CalculatedStatus CS
JOIN MultiWarPeople MWP ON CS.PID = MWP.PID
GROUP BY CS.PID
ORDER BY WarCount DESC, CS.Surname, CS.Given;