-- DateDecoder.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. 
-- #3 2010-01-21 rev by ve3meo: Quaker dates now brought out undecoded rather than null.
--    more modifier code slots displayed.
SELECT   * , 
Substr( Rmcode , 2 , 1 ) AS C2 , 
substr( Rmcode , 3 , 1 ) AS C3 ,
Substr( Rmcode , 12 , 1 ) AS C12 , 
Substr( Rmcode , 13 , 1 ) AS C13 , 
Substr( Rmcode , 14 , 1 ) AS C14 ,
Substr( Rmcode , 23 , 1 ) AS C23 , 
Substr( Rmcode , 24 , 1 ) AS C24 , 
CASE Substr( Rmcode , 1 , 1 )
	WHEN 'Q' THEN Rmcode 
	WHEN 'T' THEN Substr( Rmcode , 2 ) 
	WHEN 'D' THEN 
		CASE Substr( Rmcode , 2 , 1 ) 
			WHEN 'A' THEN 'aft ' 
			WHEN 'B' THEN 'bef ' 
			WHEN 'F' THEN 'from ' 
			WHEN 'I' THEN 'since ' 
			WHEN 'R' THEN 'bet ' 
			WHEN 'S' THEN 'from ' 
			WHEN 'T' THEN 'to ' 
			WHEN 'U' THEN 'until ' 
			WHEN 'Y' THEN 'by ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( Rmcode , 13 , 1 ) 
			WHEN 'A' THEN 'abt ' 
			WHEN 'C' THEN 'ca ' 
			WHEN 'E' THEN 'est ' 
			WHEN 'L' THEN 'calc ' 
			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]
ORDER BY RMcode
;