-- To-Do.sql
-- 1st SELECT gets tasks for Individuals and General tasks 
SELECT 
  R.Ownerid AS RIN , 
  COALESCE( 
   UPPER(N.Surname) COLLATE Nocase ||' '|| 
   N.Suffix COLLATE Nocase ||', '|| 
   N.Prefix COLLATE Nocase ||' '||
   N.Given COLLATE Nocase,   'General') AS Subject, 
  N.Birthyear AS Born , 
  N.Deathyear AS Died , 
  R.Refnumber AS File , 
  R.NAME COLLATE Nocase AS Task, 
  Substr( 'OPEN     ,COMPLETED,PENDING  ,PROBLEM', R.Status*10+1,9) AS 'Status' , 
  R.Priority AS Pty , 
 (Substr(R.Date1,4,4)||'-'||Substr(R.Date1,8,2)||'-'||Substr(R.Date1,10,2))AS'Started', 
 (Substr(R.Date2,4,4)||'-'||Substr(R.Date2,8,2)||'-'||Substr(R.Date2,10,2))AS'LastWorked',
 (Substr(R.Date3,4,4)||'-'||Substr(R.Date3,8,2)||'-'||Substr(R.Date3,10,2))AS'Completed',
  Quote( R.Details ) AS Details, 
  A.NAME COLLATE NOCASE AS Repository , 
  A.Street1 AS Adr1 , 
  A.Street2 AS Adr2 , 
  A.City AS 'City', 
  A.State AS 'State'
FROM 
  Researchtable R   LEFT JOIN Addresslinktable Al ON ( R.Taskid = Al.Ownerid AND Al.Ownertype = 6 )
  LEFT JOIN Addresstable A ON Al.Addressid = A.Addressid 
  LEFT JOIN Nametable N ON R.Ownerid = N.Ownerid  AND N.Isprimary = 1 -- 0=Alternate Name, 1=Primary Name
  LEFT JOIN FamilyTable F ON R.OwnerID = F.FamilyID AND F.FatherID=N.Ownerid AND R.OwnerType=1 AND N.Isprimary = 1
WHERE 
  Status NOT LIKE 1 -- 0=OPEN, 1=COMPLETED, 2=PENDING, 3=PROBLEM  AND R.OwnerType <>1 -- suppress Family tasks because wrong name linked  
  -- Add other filter criteria, e.g. AND Subject LIKE 'surname%'
  AND R.OwnerType<>1 -- suppress Family
UNION ALL
-- 2nd SELECT gets Family related tasks with name and RIN of Father
SELECT 
  R.Ownerid AS RIN , 
  'Family of '||UPPER(N.Surname) COLLATE Nocase ||' '|| 
   N.Suffix COLLATE Nocase ||', '|| 
   N.Prefix COLLATE Nocase ||' '||
   N.Given COLLATE Nocase AS Subject, 
  N.Birthyear AS Born , 
  N.Deathyear AS Died , 
  R.Refnumber AS File , 
  R.NAME COLLATE Nocase AS Task, 
  Substr( 'OPEN     ,COMPLETED,PENDING  ,PROBLEM', R.Status*10+1,9) AS 'Status' , 
  R.Priority AS Pty , 
 (Substr(R.Date1,4,4)||'-'||Substr(R.Date1,8,2)||'-'||Substr(R.Date1,10,2))AS'Started', 
 (Substr(R.Date2,4,4)||'-'||Substr(R.Date2,8,2)||'-'||Substr(R.Date2,10,2))AS'LastWorked',
 (Substr(R.Date3,4,4)||'-'||Substr(R.Date3,8,2)||'-'||Substr(R.Date3,10,2))AS'Completed',
  Quote( R.Details ) AS Details, 
  A.NAME COLLATE NOCASE AS Repository , 
  A.Street1 AS Adr1 , 
  A.Street2 AS Adr2 , 
  A.City , 
  A.State 
FROM 
  Researchtable R   LEFT JOIN Addresslinktable Al ON ( R.Taskid = Al.Ownerid AND Al.Ownertype = 6 )
  LEFT JOIN Addresstable A ON Al.Addressid = A.Addressid 
  INNER JOIN FamilyTable F ON R.OwnerID = F.FamilyID AND R.OwnerType=1 
  INNER JOIN Nametable N ON F.FatherID = N.Ownerid AND N.Isprimary = 1 -- 0=Alternate Name, 1=Primary Name
WHERE 
  Status NOT LIKE 1 -- 0=OPEN, 1=COMPLETED, 2=PENDING, 3=PROBLEM  AND R.OwnerType <>1 -- suppress Family tasks because wrong name linked  
  -- Add other filter criteria, e.g. AND Subject LIKE '%surname%'
ORDER BY 
  Subject -- or BY RIN, Task, Status, Repository or combination separated by comma
-- END OF QUERY - DO NOT DELETE trailing semi-colon
  ;