Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Wednesday, May 8, 2013 10:16 AM
Hi all,
I have to develop a SLA Report. I have already developed using the query in below link
Everything is fine, except its taking a little time. Now i am facing a difficulty here.
I should be able to skip weekends. Weekends should not be included in Calculation and if any item comes in after 3AM ist, the calculation shoould start from 8AM IST. Any help on how can i achieve this.
Thanks & Regards,
Manjunath C Bhat,
http://manjunathcbhat.blogspot.com
http://manjunathcbhat.wordpress.com
Wednesday, May 8, 2013 10:48 AM ✅Answered | 2 votes
Below function is used to calculate the time difference in Min, taking care of working hours i.e say 8:30 AM to 4:30 PM.
Create function [dbo].[getWorkingHour](@Tstartdate datetime,@Tenddate datetime)returns intasbegin -- declare @Tstartdate datetime,@Tenddate datetime-- set @Tstartdate='08/01/2011 08:30:40'-- set @Tenddate='08/02/2011 09:30:40' declare @startdate varchar(10),@enddate varchar(10),@daydiff int declare @minCount int set @startdate=convert(varchar,cast(@Tstartdate as datetime),101) set @enddate=convert(varchar,cast(@Tenddate as datetime),101) set @daydiff = datediff(dd,@startdate,@enddate) set @minCount=0 if @daydiff=0 begin -- Checks whether starttime and endtime is within working hour if (@Tstartdate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime)) AND (@Tenddate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime)) begin set @minCount=@minCount + datediff(n,@Tstartdate,@Tenddate) end else begin if @Tstartdate < cast(@startdate + ' ' + '08:30' as datetime) AND @Tenddate > cast(@startdate + ' ' + '16:30' as datetime) begin set @minCount=@minCount + 480 -- add complete one working day in min end else begin if @Tstartdate < cast(@startdate + ' ' + '08:30' as datetime) and @Tenddate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime) begin set @minCount=@minCount + datediff(n,cast(@startdate + ' ' + '08:30' as datetime),@Tenddate) end if @Tenddate > cast(@startdate + ' ' + '16:30' as datetime) and @Tstartdate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime) begin set @minCount=@minCount + datediff(n,@Tstartdate,cast(@startdate + ' ' + '16:30' as datetime)) end end end end -- end of daydiff=0 else begin declare @date datetime set @date=cast(@startdate as datetime) while @date <= @enddate begin --Checking Sat and Sun if datepart(dw,@date)<>1 and datepart(dw,@date)<>7 --and datepart(dd,@date)<>25 and datepart(mm,@date)<>12 and datepart(dd,@date)<>1 and datepart(mm,@date)<>1 begin --calculating min for start day if datediff(d,@date,@startdate)=0 begin if (@Tstartdate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime)) begin set @minCount=@minCount + datediff(n,@Tstartdate,cast(@startdate + ' ' + '16:30' as datetime)) end else begin if @Tstartdate < cast(@startdate + ' ' + '08:30' as datetime) set @minCount=@minCount + 480 end end --calculating min for days between start and end day if datediff(d,@date,@startdate) <> 0 and datediff(d,@date,@enddate)<> 0 set @minCount=@minCount + 480 --calculating min for end day if datediff(d,@date,@enddate)=0 begin if (@Tenddate between cast(@enddate + ' ' + '08:30' as datetime) and cast(@enddate + ' ' + '16:30' as datetime)) begin set @minCount=@minCount + datediff(n,cast(@enddate + ' ' + '08:30' as datetime),@Tenddate) end else begin if @Tenddate > cast(@enddate + ' ' + '16:30' as datetime) set @minCount=@minCount + 480 end end end this is if end part set @date=dateadd(d,1,@date) end -- while end end return @minCountend
Regards, RSingh
Wednesday, May 8, 2013 1:27 PM ✅Answered
I think we need to split working hour as well as creating 2 function or adding parameter i.e
working hour function1: 8 AM to 24 PM today(same day)
X= [dbo].[getWorkingHour1]('05/02/2013 08:00:00','05/02/2013 24:00:00')
working hour function2: 00 AM to 3 AM next day(same day)
Y= [dbo].[getWorkingHour2]('05/03/2013 00:00:00','05/03/2013 03:00:00')
So finally X+Y.
Regards, RSingh
Wednesday, May 8, 2013 10:36 AM
How long it takes to run "dbo.fn_diffsecond(@previous_date1,@Current_date1)" ?
Regards, RSingh
Wednesday, May 8, 2013 10:39 AM
HI,
Time taken is not because of dbo.fn_diffsecond(@previous_date1,@Current_date1, time is taken because i am using multiple cursors. Thank you.
Any idea on my above query. thank you
Thanks & Regards,
Manjunath C Bhat,
http://manjunathcbhat.blogspot.com
http://manjunathcbhat.wordpress.com
Wednesday, May 8, 2013 11:00 AM
You can extend the scope of the function by introduction a check for holiday list. That function is a limited edition. It took me some days to construct that function.
Regards, RSingh
Wednesday, May 8, 2013 12:05 PM
Hi,
This is awesome. It works great. There is small problem here. if WOrking hours in between same day it works great.
But in my case
Working hour starts from 8AM and it goes till 3 AM next day.
say something
select [dbo].[getWorkingHour]('05/02/2013 04:00:00','05/03/2013 10:30:00')
The starttime now it should take as '05/02/2013 08:00:00' and should stop at '05/03/2013 03:00:00' and again start at '05/03/2013 08:00:00' and end at '05/03/2013 10:30:00'
which will be
select DATEDIFF(n,'05/02/2013 08:00:00','05/03/2013 03:00:00') + DATEDIFF(n,'05/03/2013 08:00:00','05/03/2013 10:30:00')
the result should be 1290
i replaced 8.30 with 8:00 and 4.30 with 03:00 in your query.
But i am getting output as 960
Thanks & Regards,
Manjunath C Bhat,
http://manjunathcbhat.blogspot.com
http://manjunathcbhat.wordpress.com
Thursday, May 9, 2013 8:27 AM
Hi,
THank you. Sure i will try this. I think i need to check for substring for hours if its next day go to function 2 else 1.
Thanks & Regards,
Manjunath C Bhat,
http://manjunathcbhat.blogspot.com
http://manjunathcbhat.wordpress.com
Monday, June 10, 2013 9:57 AM
Hi,
It was working for me.
But i have a strange issue here.
The StartDate is
2012-12-08 12:24:16.553
EndDate is
2013-05-17 14:58:29.710
Now the result of
Select dbo.getWorkingHourInMS(2012-12-08 12:24:16.553, 2013-05-17 14:58:29.710) is 37838960
There is difference of more than a year. How is this possible.
Below is the code.
CREATE function [dbo].[getWorkingHourInMS](@Tstartdate datetime,@Tenddate datetime)
returns int
as
begin
-- declare @Tstartdate datetime,@Tenddate datetime
-- set @Tstartdate='08/01/2011 04:30:40'
-- set @Tenddate='08/02/2011 09:30:40'
declare @startdate varchar(10),@startdate1 varchar(10),@enddate varchar(10),@daydiff int
declare @minCount int
set @startdate=convert(varchar,cast(@Tstartdate as datetime),101)
set @startdate1=convert(varchar,cast(@Tstartdate as datetime)+1,101)
set @enddate=convert(varchar,cast(@Tenddate as datetime),101)
set @daydiff = datediff(dd,@startdate,@enddate)
set @minCount=0
if @daydiff=0
begin
-- Checks whether starttime and endtime is within working hour
if (@Tstartdate between cast(@startdate + ' ' + '04:30' as datetime) and cast(@startdate1 + ' ' + '23:30' as datetime))
AND (@Tenddate between cast(@startdate + ' ' + '04:30' as datetime) and cast(@startdate1 + ' ' + '23:30' as datetime))
begin
set @minCount=@minCount + dbo.fn_diffsecond(@Tstartdate,@Tenddate)
end
else
begin
if @Tstartdate < cast(@startdate + ' ' + '04:30' as datetime) AND @Tenddate > cast(@startdate1 + ' ' + '23:30' as datetime)
begin
set @minCount=@minCount + 1140 -- add complete one working day in min
end
else
begin
if @Tstartdate < cast(@startdate + ' ' + '04:30' as datetime) and @Tenddate between cast(@startdate + ' ' + '04:30' as datetime) and cast(@startdate1 + ' ' + '23:30' as datetime)
begin
set @minCount=@minCount + dbo.fn_diffsecond(cast(@startdate + ' ' + '04:30' as datetime),@Tenddate)
end
if @Tenddate > cast(@startdate1 + ' ' + '23:30' as datetime) and @Tstartdate between cast(@startdate + ' ' + '04:30' as datetime) and cast(@startdate1 + ' ' + '23:30' as datetime)
begin
set @minCount=@minCount + dbo.fn_diffsecond(@Tstartdate,cast(@startdate1 + ' ' + '23:30' as datetime))
end
end
end
end -- end of daydiff=0
else
begin
declare @date datetime
set @date=cast(@startdate as datetime)
while @date <= @enddate
begin
--Checking Sat and Sun
if datepart(dw,@date)<>1 and datepart(dw,@date)<>7 --and datepart(dd,@date)<>25 and datepart(mm,@date)<>12 and datepart(dd,@date)<>1 and datepart(mm,@date)<>1
begin
--calculating min for start day
if datediff(d,@date,@startdate)=0
begin
if (@Tstartdate between cast(@startdate + ' ' + '04:30' as datetime) and cast(@startdate1 + ' ' + '23:30' as datetime))
begin
set @minCount=@minCount + dbo.fn_diffsecond(@Tstartdate,cast(@startdate1 + ' ' + '23:30' as datetime))
end
else
begin
if @Tstartdate < cast(@startdate + ' ' + '04:30' as datetime) set @minCount=@minCount + 1140
end
end
--calculating min for days between start and end day
if datediff(d,@date,@startdate) <> 0 and datediff(d,@date,@enddate)<> 0 set @minCount=@minCount + 1140
--calculating min for end day
if datediff(d,@date,@enddate)=0
begin
if (@Tenddate between cast(@enddate + ' ' + '04:30' as datetime) and cast(@enddate + ' ' + '23:30' as datetime))
begin
set @minCount=@minCount + dbo.fn_diffsecond(cast(@enddate + ' ' + '04:30' as datetime),@Tenddate)
end
else
begin
if @Tenddate > cast(@enddate + ' ' + '23:30' as datetime) set @minCount=@minCount + 1140
end
end
end this is if end part
set @date=dateadd(d,1,@date)
end -- while end
end
return @minCount
end
CREATE function [dbo].[fn_diffsecond]
(
@date1 datetime,
@date2 datetime
)
returns bigint
as
begin
return ( (convert(bigint, datediff(day, @date1, @date2)) * 24 * 60 * 60)
- (datediff(second, dateadd(day, datediff(day, 0, @date1), 0), @date1))
+ (datediff(second, dateadd(day, datediff(day, 0, @date2), 0), @date2))) * 1000
end
Thanks & Regards,
Manjunath C Bhat,
http://manjunathcbhat.blogspot.com
http://manjunathcbhat.wordpress.com
Monday, June 10, 2013 1:30 PM
Hi,
Got it. Done. The problem was 1140, i was calculating difference in Seconds and adding 1140 Minutes to it. Now i converted to MS and done. thank you.
Thanks & Regards,
Manjunath C Bhat,
http://manjunathcbhat.blogspot.com
http://manjunathcbhat.wordpress.com
Monday, June 10, 2013 6:53 PM
Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math.
The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
julian_business_nbr INTEGER NOT NULL,
...);
INSERT INTO Calendar
VALUES ('2007-04-05', 42),
('2007-04-06', 43), -- good Friday
('2007-04-07', 43),
('2007-04-08', 43), -- Easter Sunday
('2007-04-09', 44),
('2007-04-10', 45); --Tuesday
To compute the business days from Thursday of this week to next
Tuesdays:
SELECT (C2.julian_business_nbr - C1.julian_business_nbr)
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05',
AND C2.cal_date = '2007-04-10';
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
Wednesday, February 5, 2014 6:15 PM
Thank you for this function. I am to calculate times in minutes between dates for a project, and this works perfectly.
Only, I am asked now to account for company holidays in addition to the weekends.
You say below that the scope of this function can be extended to accomplish this.
Please, how can the scope be extended to account for a list of holiday dates?
Thursday, February 6, 2014 3:48 PM | 1 vote
First check how many holiday fall between TStartDate and TEndDate. You can store a list of holiday in a table by year. Then to calculate holiday count use the below query,
SELECT @HCount= COUNT(HolidayDate) FROM HolidayTable WHERE HolidayDate BETWEEN @TStartDate AND @TEndDate
If say there is 2 Holiday Count then, 2 * 8 * 60 Min = 960 Min.
Finally : Return (@minCount - 960) will give the answer.
Regards, RSingh
Friday, March 28, 2014 10:07 AM
Hi Rsingh ,
Am not good at function . For adding this holiday part , where i need to include it
Please let me know. It will be very helpful.
Thanks
Regards, Subathra
Friday, March 28, 2014 10:33 AM
Refer the below function. I have highlighted in bold where holiday count is extracted and substracted.
ALTER function [dbo].[getworkhour](@Tstartdate datetime,@Tenddate datetime)
returns int
begin
-- declare @Tstartdate datetime,@Tenddate datetime
-- set @Tstartdate='2014-01-24 21:46:57.000'
-- set @Tenddate='2014-01-27 10:53:05.000'
declare @startdate varchar(10),@enddate varchar(10),@daydiff int
declare @hrstart varchar(2),@minstart varchar(2),@hrend varchar(2),@minend varchar(2)
declare @minCount int,@holidayMin int
set @startdate=convert(varchar,cast(@Tstartdate as datetime),101)
set @enddate=convert(varchar,cast(@Tenddate as datetime),101)
set @daydiff = datediff(dd,@startdate,@enddate)
set @minCount=0
set @holidayMin=0
** select @holidayMin = count(holidays)*8*60 from dbo.sla_tbl_holidays_calendar where holidays between @Tstartdate and @Tenddate**
--print @daydiff
if @daydiff=0 AND datename(w,@startdate) not in ('Saturday','Sunday')
begin
if (@Tstartdate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime))
AND (@Tenddate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime))
begin
set @minCount=@minCount + datediff(n,@Tstartdate,@Tenddate)
end
else
begin
if @Tstartdate < cast(@startdate + ' ' + '08:30' as datetime) AND @Tenddate > cast(@startdate + ' ' + '16:30' as datetime)
begin
set @minCount=@minCount + 480
end
else
begin
if @Tstartdate < cast(@startdate + ' ' + '08:30' as datetime) and @Tenddate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime)
begin
set @minCount=@minCount + datediff(n,cast(@startdate + ' ' + '08:30' as datetime),@Tenddate)
end
if @Tenddate > cast(@startdate + ' ' + '16:30' as datetime) and @Tstartdate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime)
begin
set @minCount=@minCount + datediff(n,@Tstartdate,cast(@startdate + ' ' + '16:30' as datetime))
end
end
end
end
else
begin
declare @date datetime
set @date=cast(@startdate as datetime)
while @date <= @enddate
begin
--calculating min for start day
if datediff(d,@date,@startdate)=0 AND datename(w,@date) not in ('Saturday','Sunday')
begin
if (@Tstartdate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime))
begin
set @minCount=@minCount + datediff(n,@Tstartdate,cast(@startdate + ' ' + '16:30' as datetime))
end
else
begin
if @Tstartdate < cast(@startdate + ' ' + '08:30' as datetime) set @minCount=@minCount + 480
end
end
--calculating min for days between start and end day
if datediff(d,@date,@startdate) <> 0 and datediff(d,@date,@enddate)\<> 0 AND datename(w,@date) not in ('Saturday','Sunday') set @minCount=@minCount + 480
--calculating min for end day
if datediff(d,@date,@enddate)=0 AND datename(w,@date) not in ('Saturday','Sunday')
begin
if (@Tenddate between cast(@enddate + ' ' + '08:30' as datetime) and cast(@enddate + ' ' + '16:30' as datetime))
begin
set @minCount=@minCount + datediff(n,cast(@enddate + ' ' + '08:30' as datetime),@Tenddate)
end
else
begin
if @Tenddate > cast(@enddate + ' ' + '16:30' as datetime) set @minCount=@minCount + 480
end
end
--print @date
--imcrement
set @date=dateadd(d,1,@date)
end
end
--print @minCount
@minCount : Total Min between StartDate and EndDate
**return (@minCount - @holidayMin)**
end
Regards, RSingh
Friday, March 28, 2014 10:52 AM
Thank for ur reply .
select dbo.getWorkHour('2014-01-01 06:30:00.000','2014-01-02 18:30:00.000')
my work time is 9:30 to 6:30 . i have modified for that .
but if start is selected as holiday date as above
i am getting result including this '2014-01-01 06:30:00.000', but it should not , It should only of 2Jan
at the same time if i give end date as Holiday , I am getting correct min .
Please help me
Regards, Subathra
Friday, March 28, 2014 2:30 PM
Thanks for your time , I hve got by giving as
select @holidayMin = count(holidaydate)*9*60 from dbo.Holiday where holidaydate between @startdate and @enddate
Thanks ..
Regards, Subathra
Friday, March 28, 2014 5:07 PM
Try this,
create function [dbo].[getworkhour](@Tstartdate datetime,@Tenddate datetime)returns intbegin declare @startdate varchar(10),@enddate varchar(10),@daydiff int declare @minCount int,@holidayMin int set @startdate=convert(char(10),cast(@Tstartdate as datetime),101) set @enddate=convert(char(10),cast(@Tenddate as datetime),101) set @daydiff = datediff(dd,@startdate,@enddate) set @minCount=0 set @holidayMin=0 -- exclude start and end date holiday if @startdate in (select holidays from tbl_holidays_calendar where year(holidays)=year(@startdate)) or @enddate in (select holidays from tbl_holidays_calendar where year(holidays)=year(@enddate)) select @holidayMin = (count(holidays) - 1) * 8 * 60 from tbl_holidays_calendar where holidays between @startdate and @enddate else if @startdate in (select holidays from tbl_holidays_calendar where year(holidays)=year(@startdate)) and @enddate in (select holidays from tbl_holidays_calendar where year(holidays)=year(@enddate)) select @holidayMin = (count(holidays) - 2) * 8 * 60 from tbl_holidays_calendar where holidays between @startdate and @enddate else select @holidayMin = count(holidays) * 8 * 60 from tbl_holidays_calendar where holidays between @startdate and @enddate -- startdate equal to enddate if @daydiff=0 and datename(w,@startdate) not in ('Saturday','Sunday') begin if (@Tstartdate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime)) AND (@Tenddate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime)) set @minCount=@minCount + datediff(n,@Tstartdate,@Tenddate) else begin if @Tstartdate < cast(@startdate + ' ' + '08:30' as datetime) AND @Tenddate > cast(@startdate + ' ' + '16:30' as datetime) set @minCount=@minCount + 480 else begin if @Tstartdate < cast(@startdate + ' ' + '08:30' as datetime) and @Tenddate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime) set @minCount=@minCount + datediff(n,cast(@startdate + ' ' + '08:30' as datetime),@Tenddate) if @Tenddate > cast(@startdate + ' ' + '16:30' as datetime) and @Tstartdate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime) set @minCount=@minCount + datediff(n,@Tstartdate,cast(@startdate + ' ' + '16:30' as datetime)) end end end else -- when start date not equal to end date begin declare @date datetime set @date=cast(@startdate as datetime) while @date <= @enddate begin --calculating min for start day if datediff(d,@date,@startdate)=0 AND datename(w,@date) not in ('Saturday','Sunday') if (@Tstartdate between cast(@startdate + ' ' + '08:30' as datetime) and cast(@startdate + ' ' + '16:30' as datetime)) set @minCount=@minCount + datediff(n,@Tstartdate,cast(@startdate + ' ' + '16:30' as datetime)) else if @Tstartdate < cast(@startdate + ' ' + '08:30' as datetime) set @minCount=@minCount + 480 --calculating min for days between start and end day if datediff(d,@date,@startdate) <> 0 and datediff(d,@date,@enddate)<> 0 AND datename(w,@date) not in ('Saturday','Sunday') set @minCount=@minCount + 480 --calculating min for end day if datediff(d,@date,@enddate)=0 AND datename(w,@date) not in ('Saturday','Sunday') if (@Tenddate between cast(@enddate + ' ' + '08:30' as datetime) and cast(@enddate + ' ' + '16:30' as datetime)) set @minCount=@minCount + datediff(n,cast(@enddate + ' ' + '08:30' as datetime),@Tenddate) else if @Tenddate > cast(@enddate + ' ' + '16:30' as datetime) set @minCount=@minCount + 480 set @date=dateadd(d,1,@date) end end return (@minCount - @holidayMin)end
Refer below holiday table. I am assuming that all holiday does not fall in Saturday and Sunday.
--create table tbl_holidays_calendar(holidays datetime)
--insert into tbl_holidays_calendar values('01/01/2014')
--insert into tbl_holidays_calendar values('02/01/2014')
Regards, RSingh
Monday, March 7, 2016 3:18 PM
Everyone,
I have been tasked to create SLA report on the attendance to Service Desk Incidents, the report retrieves all incidents that were resolved after 4 hours. The challenge here is the Service Desk system is considered to work as follows:
8:00 a.m. (start of the day) -- 12:45 p.m. (lunch starts)
12:45 p.m. -- 13:30 p.m. (lunch time)
13:30 p.m. (lunch over) -- 16:30 (End of the day),
I run SQL Server 2014 On Windows Server 2008 R2
The report must exclude after hours, weekends and holidays. I have tried without success, I have seen Ch. Rajen's solution however it does not fully respond to my query,please see the script below and assist:
SELECT a.callid [Call ID], a.AssignedBy [Assigned By], a.Assignee [Technician],
a.dtAcknowledge [Date Acknowledged], a.dtResolved [Date Resolved],
ISNULL((((DATEDIFF(dd,a.dtAcknowledge,a.dtResolved)-1) --Start with total number of days including weekends
- (DATEDIFF(wk,a.dtAcknowledge,a.dtResolved)*2) --Subtract 2 days for each full weekend
- (1-SIGN(DATEDIFF(dd,6,a.dtAcknowledge)%7)) --If StartDate is a Sunday, Subtract 1
- (1-SIGN(DATEDIFF(dd,5,a.dtResolved) %7))) * 465) --This will give us full days minus one that we'll complete with following operations
+ (SELECT CASE WHEN a.dtAcknowledge <= DATEADD(MI, 480, DATEADD(DD, DATEDIFF(DD, 0, a.dtAcknowledge), 0)) THEN 465
--if Start Date is earlier than 8:00 then it counts as full day
WHEN a.dtAcknowledge >= DATEADD(MI, 990, DATEADD(DD, DATEDIFF(DD, 0, a.dtAcknowledge), 0)) THEN 0
--if Start Date is later than 16:30 then it won't count
ELSE DATEDIFF(MI, a.dtAcknowledge, DATEADD(MI, 990, DATEADD(DD, DATEDIFF(DD, 0, a.dtAcknowledge), 0)))
--Minutes between start date and 16:30
-
CASE WHEN a.dtAcknowledge <= DATEADD(MI, 750, DATEADD(DD, DATEDIFF(DD, 0, a.dtAcknowledge), 0))
THEN 45 --If it's earlier than lunch, substract whole lunch time
WHEN a.dtAcknowledge <= DATEADD(MI, 795, DATEADD(DD, DATEDIFF(DD, 0, a.dtAcknowledge), 0))
--If it's earlier than the end of lunch time, substract corresponding minutes
THEN DATEDIFF( MI, a.dtAcknowledge, DATEADD(MI, 795, DATEADD(DD, DATEDIFF(DD, 0, a.dtAcknowledge), 0)))
ELSE 0
END
END
+ CASE WHEN a.dtResolved <= DATEADD(MI, 480, DATEADD(DD, DATEDIFF(DD, 0, a.dtResolved), 0))
THEN 0 --if End Date is earlier than 8:00 then it won't count
WHEN a.dtResolved >= DATEADD(MI, 990, DATEADD(DD, DATEDIFF(DD, 0, a.dtResolved), 0))
THEN 465 --if End Date is later than 16:30 then it counts as full day
ELSE DATEDIFF(MI, DATEADD(MI, 990, DATEADD(DD, DATEDIFF(DD, 0, a.dtResolved), 0)), a.dtResolved) --Minutes between 8:00 and end date
-
CASE WHEN a.dtResolved >= DATEADD(MI, 795, DATEADD(DD, DATEDIFF(DD, 0, a.dtResolved), 0))
THEN 45 --If it's later than lunch , substract whole lunch time
WHEN a.dtResolved >= DATEADD(MI, 750, DATEADD(DD, DATEDIFF(DD, 0, a.dtResolved), 0))
--If it's later than the start of lunch time, substract corresponding minutes
THEN DATEDIFF( MI, DATEADD(MI, 750, DATEADD(DD, DATEDIFF(DD, 0, a.dtResolved), 0)),a.dtResolved)
ELSE 0
END
END
--holidays table
-((SELECT count(*) FROM dbo.tblHoliday
WHERE dtHdate BETWEEN a.dtAcknowledge AND a.dtResolved) * 7.75) -- Get the number of holidays between dtAcknowledge and dtResolved,
-- and multiply that by 7.75 to get the number of hours
,0)) / 60.00 AS [Hours Spent]
FROM Asgnmnt a INNER JOIN CallLog c ON a.CallID = c.CallID
LEFT JOIN [Profile] p ON c.CustID = p.CustID
WHERE a.dtAssigned between '2016-01-01' and '2016-03-01' --dates used as an example, will be replaced by parameters
AND [Hours Spent] -- As calculated in the select statement
AND a.dtAcknowledge <= a.dtResolved
( SELECT count(*)
FROM dbo.tblHoliday
WHERE dtHdate BETWEEN a.dtAcknowledge AND a.dtResolved)
* 7.75)
,0))/60.00) > 4
Thursday, August 3, 2017 8:21 PM
Hey Rajen, great work. While trying to copy and make this work for my case, I run into following issue. Let's say the STARTDATE is a HOLIDAY and the ENDDATE is also HOLIDAY and there is a working days between them. (Think Monday, Tuesday, Wednesday). The section where you calculate holiday has a OR condition first i.e.
if @startdate in (select holidays from tbl_holidays_calendar where year(holidays)=year(@startdate)) or
@enddate in (select holidays from tbl_holidays_calendar where year(holidays)=year(@enddate))
and then the AND condition i.e.
else if @startdate in (select holidays from tbl_holidays_calendar where year(holidays)=year(@startdate)) and
@enddate in (select holidays from tbl_holidays_calendar where year(holidays)=year(@enddate))
Now you can see that my case will be captured by the first condition because the 1st condition is getting true, whereas in reality my case should be captured by the 2nd condition. But it will never get to the 2nd condition since it is an ELSE to the first condition.
Can you please confirm if I am right or is there something that I am missing here.
Thanks again.
Sohail