Certica Products

Mid-year rollover

Updated Mar 24, 2021

Mid-year rollover

On January 31st run the following script for all CAE instances. Also run for CEE customers on the main CRL Stack:

USE DATA_GATEWAY_ETL

BEGIN TRAN T1

UPDATE p1 set Parameter_Value = p2.parameter_value

--SELECT p1.NCESLEAID,p1.Parameter_value BEG_CURRENT_SEMESTER,p2.Parameter_value BEG_CUSTOM_2

fROM ETL_PARAMETER p1 

INNER JOIN ETL_PARAMETER p2 on p1.NCESLEAID = p2.NCESLEAID and p2.PARAMETER = 'BEG_CUSTOM_2'

where 

p1.PARAMETER = 'BEG_CURR_SEMESTER' --and p1.NCESLEAID = '0612880'

UPDATE p1 set Parameter_Value = p2.parameter_value

--SELECT p1.NCESLEAID,p1.Parameter_value END_CURRENT_SEMESTER,p2.Parameter_value END_CUSTOM_2

fROM ETL_PARAMETER p1 

INNER JOIN ETL_PARAMETER p2 on p1.NCESLEAID = p2.NCESLEAID and p2.PARAMETER = 'END_CUSTOM_2'

where 

p1.PARAMETER = 'END_CURR_SEMESTER' --and p1.NCESLEAID = '0612880'

UPDATE p1 set Parameter_Value = p2.parameter_value

--SELECT p1.NCESLEAID,p1.Parameter_value BEG_CURRENT_MARKING_PERIOD,p2.Parameter_value BEG_CUSTOM_2

fROM ETL_PARAMETER p1 

INNER JOIN ETL_PARAMETER p2 on p1.NCESLEAID = p2.NCESLEAID and p2.PARAMETER = 'BEG_CUSTOM_2'

where 

p1.PARAMETER = 'BEG_CURR_MARKING_PERIOD' --and p1.NCESLEAID = '0612880'

UPDATE p1 set Parameter_Value = p2.parameter_value

--SELECT p1.NCESLEAID,p1.Parameter_value END_CURRENT_MARKING_PERIOD,p2.Parameter_value BEG_CUSTOM_2

fROM ETL_PARAMETER p1 

INNER JOIN ETL_PARAMETER p2 on p1.NCESLEAID = p2.NCESLEAID and p2.PARAMETER = 'END_CUSTOM_2'

where 

p1.PARAMETER = 'END_CURR_MARKING_PERIOD' --and p1.NCESLEAID = '0612880'

commit tran t1

Previous Article Manually send email notifications when Scorecards did not go out (DRAFT)
Next Article Migrating cases from Salesforce to Freshdesk