VIVEKANANDHAN_PERIASAMY
asked on
Number of time spent in non_working_hours should be 0
Experts,
Query below calculate SLA for tickets.Logic is time spent on ticket from saturday 18:00 PM to monday 18:00 pm should not be considered and irrespective to days,if the ticket is assigned to approval','work,time will not be counted.
Expected:
Number of time spent in non_working_hours should be 0.
Current output:
Ticketid AssignedtoteamId assignedto adjusted_date adjusted_end non_working_hours time_not_counted time_difference working_hours
111 100 vivek datetime datetime 0 0 24 24
111 100 approval datetime datetime 50 100 100 -50
111 100 work datetime datetime 100 1350 1350 -100
Ticketid AssignedtoteamId working_hours
111 100 -126
Expected output:
Ticketid AssignedtoteamId assignedto adjusted_date adjusted_end non_working_hours time_not_counted time_difference working_hours
111 100 vivek datetime datetime 0 0 24 24
111 100 approval datetime datetime 50 100 100 -50
111 100 work datetime datetime 100 1350 1350 -100
Ticketid AssignedtoteamId working_hours
111 100 24
Query Used
Query below calculate SLA for tickets.Logic is time spent on ticket from saturday 18:00 PM to monday 18:00 pm should not be considered and irrespective to days,if the ticket is assigned to approval','work,time will not be counted.
Expected:
Number of time spent in non_working_hours should be 0.
Current output:
Ticketid AssignedtoteamId assignedto adjusted_date adjusted_end non_working_hours time_not_counted time_difference working_hours
111 100 vivek datetime datetime 0 0 24 24
111 100 approval datetime datetime 50 100 100 -50
111 100 work datetime datetime 100 1350 1350 -100
Ticketid AssignedtoteamId working_hours
111 100 -126
Expected output:
Ticketid AssignedtoteamId assignedto adjusted_date adjusted_end non_working_hours time_not_counted time_difference working_hours
111 100 vivek datetime datetime 0 0 24 24
111 100 approval datetime datetime 50 100 100 -50
111 100 work datetime datetime 100 1350 1350 -100
Ticketid AssignedtoteamId working_hours
111 100 24
Query Used
;With Hours_cte as
(
select t1.Ticketid, t1.AssignedtoteamId, isnull(t2.userassignmentactivity,t1.userassignmentactivity) as activity,isnull(t2.userassignmentactivity,'No Details') as assignedto,
case when datename(dw ,isnull(t2.lastupdateddtim,t1.lastupdateddtim)) = 'Saturday' and datepart(hour,isnull(t2.lastupdateddtim,t1.lastupdateddtim)) > 18 then dateadd(hour,18 - datepart(hour,isnull(t2.lastupdateddtim,t1.lastupdateddtim)),isnull(t2.lastupdateddtim,t1.lastupdateddtim))
when datename(dw ,isnull(t2.lastupdateddtim,t1.lastupdateddtim)) = 'Sunday' then dateadd(hour,-24 + 18 - datepart(hour,isnull(t2.lastupdateddtim,t1.lastupdateddtim)),isnull(t2.lastupdateddtim,t1.lastupdateddtim))
when datename(dw ,isnull(t2.lastupdateddtim,t1.lastupdateddtim)) = 'Monday' and datepart(hour,isnull(t2.lastupdateddtim,t1.lastupdateddtim)) > 18 then dateadd(hour,-48 + 18 - datepart(hour,isnull(t2.lastupdateddtim,t1.lastupdateddtim)),isnull(t2.lastupdateddtim,t1.lastupdateddtim))
else isnull(t2.lastupdateddtim,t1.lastupdateddtim)
end as adjusted_date,
row_number() over (partition by t1.ticketid, t1.assignedtoteamid order by t2.lastupdateddtim) as RN
from #table1 t1
left outer join #table2 t2 on t1.ticketid = t2.ticketid and t1.assignedtoteamid = t2.previousteamid
where t1.status = 'active'
AND not exists (select NULL from #table2 t2x where t2x.ticketid = t2.ticketid and t2x.previousteamid <> t2.previousteamid and t2x.lastupdateddtim > t2.lastupdateddtim)
)
Select h1.ticketid, h1.assignedtoteamid, h1.activity, h1.assignedto, h1.adjusted_date, isnull(h2.adjusted_date,getdate()) as adjusted_end,
((datepart(wk,isnull(h2.adjusted_date,getdate())) - datepart(wk,h1.adjusted_date)) * 72) as non_working_hours,
case when h1.assignedto in ('approval','work') then datediff(hour, h1.adjusted_date, isnull(h2.adjusted_date,getdate())) else 0 end as time_not_counted,
datediff(hour, h1.adjusted_date, isnull(h2.adjusted_date,getdate())) as time_difference,
datediff(hour, h1.adjusted_date, isnull(h2.adjusted_date,getdate()))
- case when h1.assignedto in ('approval','work') then datediff(hour, h1.adjusted_date, isnull(h2.adjusted_date,getdate())) else 0 end
- ((datepart(wk,isnull(h2.adjusted_date,getdate())) - datepart(wk,h1.adjusted_date)) * 72) as working_hours
from Hours_cte h1
left outer join hours_cte h2 on h1.ticketid = h2.ticketid and h1.assignedtoteamid = h2.assignedtoteamid and h1.rn + 1 = h2.rn
;With Hours_cte as
(
select t1.Ticketid, t1.AssignedtoteamId, isnull(t2.userassignmentactivity,t1.userassignmentactivity) as activity,isnull(t2.userassignmentactivity,'No Details') as assignedto,
case when datename(dw ,isnull(t2.lastupdateddtim,t1.lastupdateddtim)) = 'Saturday' and datepart(hour,isnull(t2.lastupdateddtim,t1.lastupdateddtim)) > 18 then dateadd(hour,18 - datepart(hour,isnull(t2.lastupdateddtim,t1.lastupdateddtim)),isnull(t2.lastupdateddtim,t1.lastupdateddtim))
when datename(dw ,isnull(t2.lastupdateddtim,t1.lastupdateddtim)) = 'Sunday' then dateadd(hour,-24 + 18 - datepart(hour,isnull(t2.lastupdateddtim,t1.lastupdateddtim)),isnull(t2.lastupdateddtim,t1.lastupdateddtim))
when datename(dw ,isnull(t2.lastupdateddtim,t1.lastupdateddtim)) = 'Monday' and datepart(hour,isnull(t2.lastupdateddtim,t1.lastupdateddtim)) > 18 then dateadd(hour,-48 + 18 - datepart(hour,isnull(t2.lastupdateddtim,t1.lastupdateddtim)),isnull(t2.lastupdateddtim,t1.lastupdateddtim))
else isnull(t2.lastupdateddtim,t1.lastupdateddtim)
end as adjusted_date,
row_number() over (partition by t1.ticketid, t1.assignedtoteamid order by t2.lastupdateddtim) as RN
from #table1 t1
left outer join #table2 t2 on t1.ticketid = t2.ticketid and t1.assignedtoteamid = t2.previousteamid
where t1.status = 'active'
AND not exists (select NULL from #table2 t2x where t2x.ticketid = t2.ticketid and t2x.previousteamid <> t2.previousteamid and t2x.lastupdateddtim > t2.lastupdateddtim)
)
Select h1.ticketid, h1.AssignedtoteamId,
sum (datediff(hour, h1.adjusted_date, isnull(h2.adjusted_date,getdate()))
- case when h1.assignedto in ('approval','work') then datediff(hour, h1.adjusted_date, isnull(h2.adjusted_date,getdate())) else 0 end
- ((datepart(wk,isnull(h2.adjusted_date,getdate())) - datepart(wk,h1.adjusted_date)) * 72)) as working_hours
from Hours_cte h1
left outer join hours_cte h2 on h1.ticketid = h2.ticketid and h1.assignedtoteamid = h2.assignedtoteamid and h1.rn + 1 = h2.rn
group by h1.Ticketid, h1.AssignedtoteamId
Christopher Kile
Looks cool, what exactly do you want it to do?
ASKER
Referencing to the above example:
current output
Ticketid AssignedtoteamId working_hours
111 100 -126
expected output:
Ticketid AssignedtoteamId working_hours
111 100 24
The time spend on work,approval should also be 0.Means it shouldn't be counted.
current output
Ticketid AssignedtoteamId working_hours
111 100 -126
expected output:
Ticketid AssignedtoteamId working_hours
111 100 24
The time spend on work,approval should also be 0.Means it shouldn't be counted.
I've tried to analyze the query and I've come up with a question.
First, you know that DATEPART(wk, ...) means "number of the week in which this date occurs in this year"? Because i don't understand the purpose of subtracting the week number from one date from the week number of the other date and multipliying by 72, which if units were maintained would be x * 72 weeks, not hours. What is the purpose of this part of the calculation?
First, you know that DATEPART(wk, ...) means "number of the week in which this date occurs in this year"? Because i don't understand the purpose of subtracting the week number from one date from the week number of the other date and multipliying by 72, which if units were maintained would be x * 72 weeks, not hours. What is the purpose of this part of the calculation?
ASKER
it should be 48 instead 72.
As it's count number of weekends and will multiply by 48, to convert day into hours.
Here i want to be 0. Instead of calculating the hour spend in the weekends.
Purpose of this query:
I want to know how many hours spend ticket was in our team queue and it should exclude weekends hours.
My problem:
1>Amount of time spend in the weekend should be made to 0.
2>adjusted_date always take less date time than the actual date time.
eg: If the ticket is created on 4th jun, the adjusted date is calulating from 2nd june.
As it's count number of weekends and will multiply by 48, to convert day into hours.
Here i want to be 0. Instead of calculating the hour spend in the weekends.
Purpose of this query:
I want to know how many hours spend ticket was in our team queue and it should exclude weekends hours.
My problem:
1>Amount of time spend in the weekend should be made to 0.
2>adjusted_date always take less date time than the actual date time.
eg: If the ticket is created on 4th jun, the adjusted date is calulating from 2nd june.
What exactly is adjusted_date? What's its function, and what makes it different from the actual date?
ASKER
Actual date is time a person assign the ticket and adjusted date is time where next user assign the ticket.
Please check below link if anymore details are required
https://www.experts-exchange.com/questions/27077770/T-SQL-COMPLEX-TIME-Calculation.html
Please check below link if anymore details are required
https://www.experts-exchange.com/questions/27077770/T-SQL-COMPLEX-TIME-Calculation.html
ASKER
I've requested that this question be deleted for the following reason:
Since there was no answer provided.Going to repost the question again.
Since there was no answer provided.Going to repost the question again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.