Category=Database Maintenance
Name=02.4. Migrate- Dates
Description=Grabs date data from current VMR tables and imports them into the data model

OutputLevel=3

Heading=Import Date data from HSSListe table data

Heading=Import all dates from old table
ImportSQL=SELECT 									\
ObjID, 												\
min(InstID), 										\
Jh, 												\
COALESCE(Datiert_1, 0),											\
COALESCE(Datiert_2, 0)											\
	\
 FROM HSSListe WHERE ObjID IN (SELECT DOCUMENTID FROM DOCUMENT) group by ObjID order by ObjID, InstID|	\
UPDATE DOCUMENT SET ORIGINYEAREARLY={3}, ORIGINYEARLATE={4} WHERE DOCUMENTID={0};

Heading2=set all lates to early if late doesn't exist and early does exist
SQL2=UPDATE DOCUMENT SET ORIGINYEARLATE=ORIGINYEAREARLY WHERE ORIGINYEARLATE=0 AND ORIGINYEAREARLY>0


Heading3=Perform gnarly roman numeral logic to the rest
ImportSQL3=SELECT 									\
ObjID, 												\
min(InstID), 										\
Jh, 												\
COALESCE(Datiert_1, 0),											\
COALESCE(Datiert_2, 0)											\
	\
 FROM HSSListe WHERE ObjID IN (SELECT DOCUMENTID FROM DOCUMENT WHERE ORIGINYEAREARLY=0) group by ObjID order by ObjID, InstID|	\
UPDATE DOCUMENT SET ORIGINYEAREARLY=[r1:{2}], ORIGINYEARLATE=[r2:{2}] WHERE DOCUMENTID={0};