Tracking time in SLA excluding weekend

[MABima]

Hi all,
i'm attempting to tracking the time a ticket spent in each SLA excluding the weekend.
i've followed one of the solution in this topic viewtopic.php?t=14242
and put daysofweek statement in the where statement but it doesn't work.

Code: Select all

SELECT th.id,
ts.name state,
th.name fromto,
th.create_time
createtime,
th.ticket_id
ticket_id,

Coalesce((SELECT th2.create_time
FROM ticket_history th2
WHERE th2.history_type_id IN ( '39' )
AND th2.ticket_id = th.ticket_id
AND th2.id > th.id
order by th2.id
LIMIT 1), Now())
custom_date,

Sec_to_time(Timestampdiff(second, th.create_time, Coalesce(
(SELECT th2.create_time
FROM ticket_history th2
WHERE th2.history_type_id IN ('39' )
AND th2.ticket_id = th.ticket_id
AND th2.id > th.id
order by th2.id
LIMIT 1), Now()))) Duration_in_SLA

FROM `ticket_history` th
LEFT JOIN ticket_state ts
ON ts.id = th.state_id
LEFT JOIN ticket t
ON t.id = th.ticket_id

WHERE
DAYOFWEEK(th.create_time) != 1 
AND DAYOFWEEK(th.create_time) !=7
AND th.history_type_id IN ( '39' )
AND t.tn = 1009181
ORDER BY ticket_id

can anyone help me ?

Thanks in advance

[MABima]

after trying my query for a while,
it has somethings that conflict with what i wanted
so here's the one i wanted except i still unable to exclude the weekend

Code: Select all

SELECT th.id,
ts.name state,
th.name fromto,
th.ticket_id
ticket_id,
th.create_time
createtime,

Coalesce((SELECT th2.create_time
FROM ticket_history th2
WHERE th2.history_type_id IN ('39','53')
AND th2.ticket_id = th.ticket_id
AND th2.id > th.id
order by th2.id
LIMIT 1), th.create_time )
custom_date,


time_format(sec_to_time(Timestampdiff(second, th.create_time, Coalesce(
(SELECT th2.create_time
FROM ticket_history th2
WHERE th2.history_type_id IN ('39','53' )
AND th2.ticket_id = th.ticket_id
AND th2.id > th.id
order by th2.id
LIMIT 1), th.create_time))), ' %Hh %im')
Duration_in_SLA


FROM `ticket_history` th
LEFT JOIN ticket_state ts
ON ts.id = th.state_id
LEFT JOIN ticket t
ON t.id = th.ticket_id

WHERE DAYOFWEEK(th.create_time) != 1 
AND DAYOFWEEK(th.create_time) !=7
AND th.history_type_id IN ('39','53')
AND t.tn = 1007331
ORDER BY id, ticket_id

any idea ?