I am getting error in line number 13 at trunc function, as it is not able to perform date truncate and add plus 1 to the date field.
Currently the above code is in SQL, need to write the query which can work in dbt.
s_order_stop.appointment_st_ts format: 2014-06-30 06:00:00.000
Query:
select
(CASE
WHEN MAX(s_order_stop.appointment_st_ts) IS NULL
OR MAX(s_order_stop.appointment_end_ts) IS NULL
THEN ‘N’
ELSE CASE
WHEN (
to_number(substr(TO_CHAR(MAX(s_order_stop.appointment_st_ts), ‘DD-MON-YYYY HH24:MI:SS’), 13, 2)) BETWEEN 0 AND 5
AND MAX(s_order_stop.appointment_end_ts) <= to_date(to_char(MAX(s_order_stop.appointment_st_ts), ‘DD-MON-YYYY’) || ’ 05:00:00’, ‘DD-MON-YYYY HH24:MI:SS’)
)
OR (
to_number(substr(TO_CHAR(MAX(s_order_stop.appointment_st_ts), ‘DD-MON-YYYY HH24:MI:SS’), 13, 2)) >= 21
AND MAX(s_order_stop.appointment_end_ts) <= to_date(to_char(trunc(MAX(s_order_stop.appointment_st_ts)) + 1, ‘DD-MON-YYYY’) || ’ 05:00:00’, ‘DD-MON-YYYY HH24:MI:SS’)
)
THEN ‘Y’
ELSE ‘N’
END
END) flag_field
from unt_raw.sbl.s_order_stop limit 1;