/*  This script will generate data to populate the RT_TIMEZONE and RT_TIMEZONE_OFFSET tables.
    This script includes the standard U.S. time zones with the start/end of daylight savings time as of 2019.

    The script can be executed in SQL Server Management Studio (SSMS) 
    on an EQuIS database to generate data (relies on the ST_CALENDAR table).
    The return can then be copied from the two output tables and pasted into 
    a refvals EDD in the [rt_timezone] and [rt_timezone_offset] sections. 
    (Note: the STATUS_FLAG column is not needed)

    -or-

    Uncomment the rows with the INSERT INTO ... and the NOT EXISTS (...) statements;
    and execute the script - doing this will populate the two tables directly without the need of the EDD. 

    Written by:  EarthSoft, Inc. 
            on:  2019-12-09 
       updated:  <original version> 
*/

-- query list time zones in the U.S. 
DECLARE @timezone TABLE 
( timezone_code VARCHAR(10) NOT NULL PRIMARY KEY 
, timezone_name VARCHAR(255) NOT NULL 
, timezone_offset SMALLINT NOT NULL 
, daylight_saving_adj BIT NOT NULL ); 

-- the following are the current time zones in the U.S. Additional time zones may be added as needed.
INSERT INTO @timezone 
  (timezone_code, timezone_name,       timezone_offset, daylight_saving_adj)
VALUES ('ET',     'Eastern Time',            -5,              1) 
     , ('CT',     'Central Time',            -6,              1)
     , ('MT',     'Mountain Time',           -7,              1)
     , ('MT-AZ',  'Mountain Time - Arizona', -7,              0)
     , ('PT',     'Pacific Time',            -8,              1)
     , ('AST',    'Alaska Time',             -9,              1)
     , ('HAT',    'Hawaii-Aleutian Time',   -10,              1)
     ; 

DECLARE @remark VARCHAR(2000) = 'Row generated by "15637_rt_timezone_generate_data.txt" script.'; 

--INSERT INTO dbo.rt_timezone (timezone_code, timezone_name, timezone_offset, remark, status_flag) 
SELECT timezone_code, timezone_name, timezone_offset, @remark AS remark, 'A' AS status_flag 
FROM @timezone z 
--WHERE NOT EXISTS (SELECT * FROM dbo.rt_timezone t WHERE t.timezone_code = z.timezone_code) 
ORDER BY timezone_offset DESC, daylight_saving_adj DESC, timezone_code; 

-- query returns start and end dates for US Daylight Savings Time:
WITH generate_dls AS 
  ( SELECT CASE WHEN c.[month] = 3 THEN 1 ELSE 0 END AS is_dls
      , CASE WHEN c.[month] = 3 THEN DATEADD(dd, 7, MIN(c.dt)) ELSE MIN(c.dt) END AS offset_start_date 
    FROM st_calendar c 
    WHERE c.dt BETWEEN '2007-01-01' AND '2030-12-31'  -- 2007 is the year current daylight saving dates went into effect; 2030 is an arbitrary year in the future to end the generation of the data.
      AND c.[month] IN (3, 11) 
      AND c.day_of_week = 1 
    GROUP BY c.[year], c.[month] 
  ) 

--INSERT INTO dbo.rt_timezone_offset (timezone_code, offset_start_date, utc_offset_minutes, remark, status_flag)
SELECT timezone_code, CAST('1900-01-01' AS SMALLDATETIME) AS offset_start_date
     , (timezone_offset * 60) AS utc_offset_minutes
  , @remark AS remark 
  , 'A' AS status_flag   
FROM @timezone z 
WHERE NOT EXISTS (SELECT * FROM dbo.rt_timezone_offset t WHERE t.timezone_code = z.timezone_code AND  t.offset_start_date = CAST('1900-01-01' AS SMALLDATETIME))
UNION ALL 
SELECT z.timezone_code, g.offset_start_date 
  , (z.timezone_offset + g.is_dls) * 60 AS utc_offset_minutes 
  , @remark AS remark 
  , 'A' AS status_flag   
FROM @timezone z, generate_dls g 
WHERE z.daylight_saving_adj = 1 
--  AND NOT EXISTS (SELECT * FROM dbo.rt_timezone_offset t WHERE  t.timezone_code = z.timezone_code AND  t.offset_start_date = g.offset_start_date)
ORDER BY offset_start_date, timezone_code;