Generate Actual Business Time Spent resolving a ticket

Question

How can I write a SQL query to calculate the actual time agents spend resolving a ticket, given that the company's business hours are between 8:30 AM and 5:30 PM Monday through Saturday, excluding minutes that are outside business hours? The reply time shown below disregards business hours i.e. the timer/counter includes minutes outside business hours. I want to deduct outside business hours minutes from the reply time

TICKET_ID   CREATED_AT  REPLY_TIME
157 2023-05-14 19:15:04.000 690
193 2023-05-20 11:11:19.000 2,634
72  2023-04-18 07:12:08.000 81
186 2023-05-19 07:04:23.000 16
165 2023-05-15 14:15:27.000 1
60  2023-04-04 08:10:52.000 1,344
32  2023-02-22 19:05:46.000 
93  2023-04-29 15:45:57.000 3,730
70  2023-04-15 16:47:07.000 2,268
83  2023-04-27 07:29:40.000 

My snowflake sql looks something like this

WITH business_hours_calc AS (
    SELECT
        created_at,
        reply_time,
        DATEADD(
            SECOND,
            510 * 60, -- 510 minutes (8 hours and 30 minutes) in seconds
            TIMESTAMP_FROM_PARTS(
                YEAR(DATEADD(DAY, 1, DATE_TRUNC('DAY', created_at ))),
                MONTH(DATEADD(DAY, 1, DATE_TRUNC('DAY', created_at ))),
                DAYOFMONTH(DATEADD(DAY, 1, DATE_TRUNC('DAY', created_at ))),
                8, 30, 0, 0 -- 8:30 AM
            )
        ) AS start_time,
        DATEADD(
            SECOND,
            1020 * 60, -- 1020 minutes (17 hours) in seconds
            TIMESTAMP_FROM_PARTS(
                YEAR(DATEADD(DAY, 1, DATE_TRUNC('DAY', created_at ))),
                MONTH(DATEADD(DAY, 1, DATE_TRUNC('DAY', created_at ))),
                DAYOFMONTH(DATEADD(DAY, 1, DATE_TRUNC('DAY', created_at))),
                17, 0, 0, 0 -- 5:00 PM
            )
        ) AS end_time,
        DATEADD(MINUTE, reply_time, created_at)
    FROM tickets_metrics
), 
time_diff_calc AS (
    SELECT
        created_at,
        reply_time,
        start_time,
        end_time,
        CASE
            WHEN TIME_SLICE(created_at, 1, 'HOUR')::TIME BETWEEN start_time AND end_time
            THEN
                CASE 
                    WHEN TIME_SLICE(DATEADD(MINUTE, reply_time, created_at), 1, 'HOUR')::TIME NOT BETWEEN start_time AND end_time 
                    THEN DATEDIFF(MINUTE, DATEADD(MINUTE, reply_time, created_at), end_time)
                    ELSE 0
                END
            ELSE 0
        END AS outside_business_hours_minutes
    FROM business_hours_calc
)
SELECT
    created_at,
    reply_time - outside_business_hours_minutes AS adjusted_reply_time
FROM time_diff_calc
LIMIT 10;

I get an incompatible types: [TIME(9)] and [TIMESTAMP_NTZ(9)] error at the case statement level

Answer 1

I found this https://docs.getdbt.com/blog/measuring-business-hours-sql-time-on-task Tweaked it to suit my needs