Timestamp TimeZone Cleaner Coding

Database

Timestamp TimeZone Cleaner Coding

Hello, 

I'm super new to TD and SQL. This code was given to by a co-worker. It works but very slow. Is there a way to speed it up  using 'Interval' and 'Timestamps'?

SELECT DISTINCT

TimeStamp(FORMAT 'YYYYMMDD') (CHAR(8))  AS CALL_DT,

TimeStamp(FORMAT 'HH:MI:SS') (CHAR(8))  AS CALL_TIME,

CASE WHEN TEMP1>=240000 

AND TEMP2>=240000 

AND TEMP1=TEMP2  THEN CAST(CALL_DT +1 AS INT)

ELSE CAST(CALL_DT AS INT)

END AS CALL_DT_NEW,

CAST (CALL_DT_NEW AS CHAR(8)) AS CALL_DT_LOCAL,

CASE WHEN C.TIME_ZONE1='13' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)+CAST('00:00:00' AS TIME) AS TIME)

WHEN C.TIME_ZONE1='12' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)+CAST('17:00:00' AS TIME) AS TIME)  

WHEN C.TIME_ZONE1='2' THEN  CAST(CALL_TIME-CAST('01:00:00' AS TIME)-CAST('03:00:00' AS TIME) AS TIME)  

WHEN C.TIME_ZONE1='18' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)+CAST('01:00:00' AS TIME) AS TIME)

WHEN C.TIME_ZONE1='20' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)+CAST('18:00:00' AS TIME) AS TIME)

WHEN C.TIME_ZONE1='24' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)-CAST('04:00:00' AS TIME) AS TIME)

WHEN C.TIME_ZONE1='30' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)-CAST('01:00:00' AS TIME) AS TIME)

WHEN C.TIME_ZONE1='33' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)-CAST('02:00:00' AS TIME) AS TIME)

WHEN C.TIME_ZONE1='38' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)+CAST('02:00:00' AS TIME) AS TIME)

WHEN C.TIME_ZONE1='39' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)+CAST('19:00:00' AS TIME) AS TIME)

WHEN C.TIME_ZONE1='43' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)+CAST('14:00:00' AS TIME) AS TIME)

WHEN C.TIME_ZONE1='45' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)+CAST('15:00:00' AS TIME) AS TIME)

WHEN C.TIME_ZONE1='46' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)+CAST('01:00:00' AS TIME) AS TIME)

WHEN C.TIME_ZONE1='47' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)-CAST('01:00:00' AS TIME) AS TIME)

WHEN C.TIME_ZONE1='50' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)+CAST('16:00:00' AS TIME)-CAST ('24:00:00' AS TIME) AS TIME)

END AS TEMP1,

CASE WHEN D.TIME_ZONE2='1' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)-CAST('16:00:00' AS TIME) AS TIME) 

WHEN D.TIME_ZONE2='2' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)-CAST('04:00:00' AS TIME) AS TIME)  

WHEN D.TIME_ZONE2='3' THEN  CAST(CALL_TIME-CAST('01:00:00' AS TIME)-CAST('03:00:00' AS TIME) AS TIME)  

WHEN D.TIME_ZONE2='4' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)-CAST('02:00:00' AS TIME) AS TIME)

WHEN D.TIME_ZONE2='5' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)-CAST('01:00:00' AS TIME) AS TIME)

WHEN D.TIME_ZONE2='6' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)-CAST('00:00:00' AS TIME) AS TIME)

WHEN D.TIME_ZONE2>='7' THEN CAST(CALL_TIME-CAST('01:00:00' AS TIME)+CAST('01:00:00' AS TIME) AS TIME)

END AS TEMP2,

CAST(TEMP1 AS TIME) AS LOCAL_CALL_TIME1,

CAST(TEMP2 AS TIME) AS LOCAL_CALL_TIME2

1 REPLY
Junior Contributor

Re: Timestamp TimeZone Cleaner Coding

I don't understand what this code is doing, in fact it should return some syntax errors :)

Can you show some actual data and the result?