Number of time spent in non_working_hours should be 0

VIVEKANANDHAN_PERIASAMY
VIVEKANANDHAN_PERIASAMY used Ask the Experts™
on
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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Christopher KileSenior Software Analyst

Commented:
Looks cool, what exactly do you want it to do?
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.
Christopher KileSenior Software Analyst

Commented:
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?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.
Christopher KileSenior Software Analyst

Commented:
What exactly is adjusted_date?  What's its function, and what makes it different from the actual date?
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
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_27077770.html
I've requested that this question be deleted for the following reason:

Since there was no answer provided.Going to repost the question again.
Senior Software Analyst
Commented:
Sorry to take so long to respond.

Have you tried replacing 48 for 72 as you suggested should be correct in your next-to-last message?  Here's what it looks like:

--

;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)) * 48) 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)) * 48) 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)) * 48)) 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

--

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial