troubleshooting Question

Number of time spent in non_working_hours should be 0

Avatar of VIVEKANANDHAN_PERIASAMY
VIVEKANANDHAN_PERIASAMY asked on
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
8 Comments1 Solution378 ViewsLast Modified:
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
;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
ASKER CERTIFIED SOLUTION
Christopher Kile
Senior Software Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros