-- GroupTableWrite.sql
/*
2012-12-07 Tom Holden ve3meo

Creates StartID-EndID ranges for use in GroupTable from
a list of Marked PersonIDs and 
Writes records to GroupTable

Replace marklist with the desired list, however generated, in 
the form 1,3,479,480,481,...

In the Update GroupTable section,
replace the GroupID value with the one desired.

*/

DROP Table IF EXISTS tmpStartIDTable;
DROP Table IF EXISTS tmpEndIDTable;
CREATE TEMP TABLE tmpStartIDTable
AS

 SELECT PersonID AS StartID 
 FROM PersonTable
 WHERE 
 StartID IN
 (
-- marklist
SELECT PersonID FROM PersonTable, GroupTable
WHERE GroupID = 2
AND PersonID BETWEEN StartID AND EndID
  )
 AND
 StartID -1 NOT IN
 (
-- marklist
SELECT PersonID FROM PersonTable, GroupTable
WHERE GroupID = 2
AND PersonID BETWEEN StartID AND EndID
  )

; 
 
CREATE TEMP TABLE tmpEndIDTable
AS

 SELECT PersonID AS EndID 
 FROM PersonTable
 WHERE 
 EndID IN
 (
-- marklist
SELECT PersonID FROM PersonTable, GroupTable
WHERE GroupID = 2
AND PersonID BETWEEN StartID AND EndID
  )
 AND
 EndID +1 NOT IN
 (
-- marklist
SELECT PersonID FROM PersonTable, GroupTable
WHERE GroupID = 2
AND PersonID BETWEEN StartID AND EndID
  )

; 

-- Update GroupTable
-- change GroupID value to match LabelValue for group from LabelTable
DELETE FROM GroupTable WHERE GroupID = 3
;

INSERT INTO GroupTable
SELECT NULL AS RecID, 3 AS GroupID, * FROM tmpStartIDTable StartID INNER JOIN tmpEndIDTable EndID  
ON StartID.rowid = EndID.rowid
;