-- SortDateEncoderDev2.sql
/*
2013-01-02 Tom Holden ve3meo

Streamlined version of SortDateEncoderDev.sql that is easier to read, maybe faster because of fewer operations.
Calculates SortDates from event dates, based on algorithms adapted from R. Steven Turley
and compares to the RM generated SortDates for errors.
See http://sqlitetoolsforrootsmagic.wikispaces.com/Dates%3E+SortDate+Algorithm#Advanced Date Decoder

Encode SortDate for regular non-Quaker dates
Ds = If no Date then 9223372036854775807
     else ((Y1 + 10000)<<49) + (M1<<45) + (D1<<39) + 
     (If Date2 then (Y2 + 10000)<<20 else 17178820608) + (M2<<16) + (D2<<10) + F
where Ds = SortDate, Y = Year, M = Month, D = Day, F = modifier flag.

Also handles Quaker dates and slash dates, separately and in combination, at least in part.

*/
SELECT EventID, Date, Details, SortDate, CalcDate, SortDate-CalcDate AS Error
FROM
(
SELECT EventID, DATE, Details, SortDate, 
  CASE Typ
		WHEN 'D'
			THEN --non-Quaker date
				CASE 
					WHEN Y1 = 0
						AND M1 + D1 != 0
						THEN 16383 << 49 -- a date with no year
					ELSE ((Y1 + 10000 + (Slsh = '/')) << 49) -- Slash date increments year by 1
					END + --Y1
				(M1 << 45) + --M1
				(D1 << 39) + --D1
				CASE Y2
					WHEN 0
						THEN 17178820608 -- x'03 FF F0 00 00' or (2^34 - 2^20)  SELECT (1<<34)-(1<<20) 
					ELSE ((Y2 + 10000) << 20) --Y2
					END + (M2 << 16) + --M2  
				(D2 << 10) + --D2
				Flag -- Flag
		WHEN 'Q'
			THEN --Quaker date
				CASE 
					WHEN M1 > 10
						AND Y1 < 1752 -- month 1=Mar, 12=Feb of next year, before 1752
						THEN (Y1 + 10001 + (Slsh = '/')) << 49
					ELSE (Y1 + 10000 + (Slsh = '/')) << 49
					END + -- Y1
				CASE 
					WHEN Y1 < 1752
						THEN CASE 
								WHEN M1 = 0
									THEN 0
								WHEN M1 < 11
									THEN (M1 + 2) << 45 
								WHEN M1 > 10
									THEN (M1 - 10) << 45  -- SELECT Date,SUBSTR(Date,8,2)-10 FROM EventTable WHERE ABS(SUBSTR(Date,8,2))>10
								END
					ELSE (M1 << 45)
					END + --M1 
				(D1 << 39) + --D1
				CASE Y2
					WHEN 0
						THEN 17178820608 -- x'03 FF F0 00 00' or (2^34 - 2^20)  SELECT (1<<34)-(1<<20) 
					ELSE CASE 
							WHEN M2 > 10
								AND Y2 < 1752
								THEN (Y2 + 10001) << 20
							ELSE (Y2 + 10000) << 20
							END
					END + --Y2
				CASE 
					WHEN Y2 < 1752
						THEN --
							CASE 
								WHEN M2 = 0
									THEN 0
								WHEN M2 < 11
									THEN (M2 + 2) << 16 -- SELECT 11%12
								WHEN M2 > 10
									THEN (M2 - 10) << 16 -- SELECT Date,SUBSTR(Date,8,2)-10 FROM EventTable WHERE ABS(SUBSTR(Date,8,2))>10
								END
					ELSE (M2 << 16)
					END + --M2
				(D2 << 10) + --D2
				Flag -- Flag
	--  WHEN '.' OR 'T' --blank or text date
		ELSE 9223372036854775807 --x'7F FF FF FF FF FF FF FF' (2^63-1)
		END AS CalcDate
FROM (
	SELECT 
    EventID, 
    DATE, 
    SortDate, 
    Details, 
    SUBSTR(DATE, 1, 1) AS Typ, 
    CAST(SUBSTR(DATE, 3, 5) AS INT) AS Y1, 
    CAST(SUBSTR(DATE, 8, 2) AS INT) AS M1, 
    CAST(SUBSTR(DATE, 10, 2) AS INT) AS D1, 
    SUBSTR(DATE, 12, 1) AS Slsh, 
    CAST(SUBSTR(DATE, 14, 5) AS INT) AS Y2, 
    CAST(SUBSTR(DATE, 19, 2) AS INT) AS M2, 
    CAST(SUBSTR(DATE, 21, 2) AS INT) AS D2, 
    
				CASE SUBSTR(DATE, 2, 1) -- Flag
					WHEN 'B'
						THEN 0 --Before
					WHEN 'Y'
						THEN 3 --By
					WHEN 'T'
						THEN 6 --To
					WHEN 'U'
						THEN 9 --Until
					WHEN '.'
						THEN 12 --On
					WHEN 'R'
						THEN 15 --Between x and y
					WHEN 'S'
						THEN 18 --From x to y
					WHEN '-'
						THEN 21 --Hyphen x-y
					WHEN 'O'
						THEN 24 --x Or y
					WHEN 'F'
						THEN 1047579 --From    (27 + x'FFC00')
					WHEN 'I'
						THEN 1047582 --Since   (30 + x'FFC00')
					WHEN 'A'
						THEN 1047583 --After   (31 + x'FFC00')
					ELSE 0
					END  AS Flag
	FROM EventTable
	)
)
;