-- OneEventEachTypePerPerson.sql
/* 2024-02-15 Tom Holden ve3meo
A parallel to the RM9 People List which allows only one
event of each event type to be displayed but improved by prioritizing 
events set as Primary which People List ignores.

People List displays the first record added for a given EventType for the person.
So does this script but changing each instance of Min(EventID) to Max(EventID)
switches it to selecting the last added record.

The RawDate extract is available for sorting in the same way that People List
sorts on the Date column of an event type.
*/

-- VIEW the first Primary record for each EventType for a person
DROP VIEW IF EXISTS PrimaryEventsView
;
CREATE TEMP VIEW PrimaryEventsView
AS
SELECT OwnerID, EventType, Min(EventID) EventID
FROM EventTable
WHERE OwnerType=0 --Person events
AND IsPrimary
GROUP BY OwnerID, EventType -- Rec# or RIN in RM
;

-- VIEW the first record for each EventType w/o a Primary for a person
DROP VIEW IF EXISTS NotPrimaryEventsView
;
CREATE TEMP VIEW NotPrimaryEventsView
AS
SELECT OwnerID, EventType, Min(EventID) EventID
FROM EventTable
WHERE OwnerType=0 --Person events
AND NOT IsPrimary
AND OwnerID||'.'||EventType NOT IN (SELECT OwnerID||'.'||EventType FROM PrimaryEventsView)
GROUP BY OwnerID, EventType -- Rec# or RIN in RM
;

-- VIEW the data for the combined record sets from the above two VIEWs
DROP VIEW IF EXISTS UniqueEventsView
;
CREATE TEMP VIEW UniqueEventsView
AS
SELECT
 U.OwnerID
 , U.EventType
 , U.EventID
 , SUBSTR(E.Date,4,8) AS RawDate -- yyyymmdd extracted from 1st instance in Date
 , E.Date
 , E.PlaceID
 , E.SiteID
 , E.SortDate
 , E.IsPrimary -- add others as needed
FROM
(
SELECT * FROM PrimaryEventsView
UNION
SELECT * FROM NotPrimaryEventsView
) AS U
JOIN EventTable E USING (EventID)
;

-- display the results
SELECT * FROM UniqueEventsView
-- add constraints, order, JOINs to other tables for names,...
;