Avatar of VIVEKANANDHAN_PERIASAMY
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
;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

Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL ServerDatabases

Avatar of undefined
Last Comment
Christopher Kile

8/22/2022 - Mon
Christopher Kile

Looks cool, what exactly do you want it to do?
VIVEKANANDHAN_PERIASAMY

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.
Christopher Kile

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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
VIVEKANANDHAN_PERIASAMY

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.
Christopher Kile

What exactly is adjusted_date?  What's its function, and what makes it different from the actual date?
VIVEKANANDHAN_PERIASAMY

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/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_27077770.html
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
VIVEKANANDHAN_PERIASAMY

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.
ASKER CERTIFIED SOLUTION
Christopher Kile

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question