-- DateFormat.sql
-- Decodes RM4 Dates with 'D' and 'T' prefixes.
-- #1 2010-01-20 ve3meo 
--    uses a sample database RMDateFormats.db3 with the table Formats
--    no Quaker, double year; constructed using COALESCE
-- #2 2010-01-20 rev by ve3meo: discovered CASE and replaced most COALESCE.
--    Much cleaner; double years still no Quaker, nor months in abbrev.
--    Awfully big to have to plug in for every date type field; argues for 
--    an extended function in sqlite or a similar function in the calling program. 
SELECT   * , 
Nullif( Substr( Rmcode , 2 , 1 ) , '.' ) AS C2 , 
Nullif( Substr( Rmcode , 13 , 1 ) , '.' ) AS C13 , 
CASE Substr( Rmcode , 1 , 1 ) 
	WHEN 'T' THEN Substr( Rmcode , 2 ) 
	WHEN 'D' THEN 
		CASE Substr( Rmcode , 2 , 1 ) 
		WHEN 'Y' THEN 'by ' 
		WHEN 'B' THEN 'bef ' 
		WHEN 'T' THEN 'to ' 
		WHEN 'U' THEN 'until ' 
		WHEN 'F' THEN 'from ' 
		WHEN 'I' THEN 'since ' 
		WHEN 'A' THEN 'aft ' 
		WHEN 'R' THEN 'bet ' 
		WHEN 'S' THEN 'from ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( Rmcode , 13 , 1 ) 
		WHEN 'A' THEN 'abt ' 
		WHEN 'E' THEN 'est ' 
		WHEN 'L' THEN 'calc ' 
		WHEN 'C' THEN 'ca ' 
		WHEN 'S' THEN 'say ' 
		WHEN '6' THEN 'cert ' 
		WHEN '5' THEN 'prob ' 
		WHEN '4' THEN 'poss ' 
		WHEN '3' THEN 'lkly ' 
		WHEN '2' THEN 'appar ' 
		WHEN '1' THEN 'prhps ' 
		WHEN '?' THEN 'maybe ' 
		ELSE '' 
		END 
		|| 
		CASE WHEN Substr( Rmcode , 3 , 1 ) = '-' THEN 'BC' ELSE '' END  
		|| 
		CASE WHEN Substr( Rmcode , 4 , 4 ) = '0000' THEN '' ELSE Substr( Rmcode , 4 , 4 ) END 
		|| 
		CASE WHEN Substr( Rmcode , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( Rmcode , 3 , 5 ) ) ELSE '' END 
		|| 
		CASE 
		WHEN Substr( Rmcode , 8 , 2 ) = '00' AND ( Substr( Rmcode , 4 , 4 ) <> '0000' AND Substr( Rmcode , 10 , 2 ) <> '00' ) THEN '-??' 
	
		WHEN Substr( Rmcode , 8 , 2 ) = '00' AND ( Substr( Rmcode , 4 , 4 ) <> '0000' AND Substr( Rmcode , 10 , 2 ) == '00' ) THEN '' 
		WHEN Substr( Rmcode , 8 , 2 ) = '00' THEN '' 
	
		ELSE '-' || Substr( Rmcode , 8 , 2 ) 
	
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( Rmcode , 10 , 2 ) , '-00' ) , '' ) 
		|| 
		CASE Substr( Rmcode , 2 , 1 ) 
		WHEN 'R' THEN ' and ' 
		WHEN 'S' THEN ' to ' 
		WHEN 'O' THEN ' or ' 
		WHEN '-' THEN ' - ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( Rmcode , 24 , 1 ) 
	
		WHEN 'A' THEN 'abt ' 
		WHEN 'E' THEN 'est ' 
		WHEN 'L' THEN 'calc ' 
		WHEN 'C' THEN 'ca ' 
		WHEN 'S' THEN 'say ' 
		WHEN '6' THEN 'cert ' 
		WHEN '5' THEN 'prob ' 
		WHEN '4' THEN 'poss ' 
		WHEN '3' THEN 'lkly ' 
		WHEN '2' THEN 'appar ' 
		WHEN '1' THEN 'prhps ' 
		WHEN '?' THEN 'maybe ' 
		ELSE '' 
		END 
		|| 
		CASE WHEN Substr( Rmcode , 14 , 1 ) = '-' THEN 'BC' ELSE '' END 
		|| 
		CASE WHEN Substr( Rmcode , 15 , 4 ) = '0000' THEN '' ELSE Substr( Rmcode , 15 , 4 ) END 
		|| 
		CASE WHEN Substr( Rmcode , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( Rmcode , 14 , 5 ) ) ELSE '' END 
		|| 
		CASE 
		WHEN Substr( Rmcode , 19 , 2 ) = '00' AND ( Substr( Rmcode , 15 , 4 ) <> '0000' AND Substr( Rmcode , 21 , 2 ) <> '00' ) THEN '-??' 
		WHEN Substr( Rmcode , 19 , 2 ) = '00' AND ( Substr( Rmcode , 15 , 4 ) <> '0000' AND Substr( Rmcode , 21 , 2 ) == '00' ) THEN '' 
		WHEN Substr( Rmcode , 19 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( Rmcode , 19 , 2 ) 
	
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( Rmcode , 21 , 2 ) , '-00' ) , '' ) 
	ELSE '' 
END AS Decoded 
FROM 
Main.[formats] --WHERE  C2 <>'' OR C13 <> ''
;