May 12, 2017 at 6:11 am
Good afternoon,
I tried to do sql query where is temp table for holidays and then calculation for working days sla hours.
Sla hours works (maybe) but when I try use calendar, all values goes wrong. There's lots of data for me to handle.. 😀
Working hours: 06.00 - 18.00 on weekdays and when holiday, hours not added.
Thanks in advance and best regards,
Mikko S.
/*Code begins*/
--Step one - Create a temp table with all the relevant holiday values in:
CREATE TABLE #Holidays (HDate date)
INSERT INTO #Holidays (HDate)
VALUES
--2017
('2017-01-01'), --1 January Sunday New Year’s Day
('2017-01-06'), --06 March Friday Good Friday
('2017-04-14'), --14 March Monday Easter Monday
('2017-04-17'), --17 May Monday Early May bank holiday
('2017-05-01'), --1 April
('2017-05-25'), --25 August Monday Summer bank holiday
('2017-06-24'), --24 December Monday Boxing Day
('2017-12-06'), --06 December Tuesday Christmas Day (substitute day)
('2017-12-24'), --24 December Tuesday Christmas Day (substitute day)
('2017-12-25'), --25 December Tuesday Christmas Day (substitute day)
('2017-12-26'), --26 December Tuesday Christmas Day (substitute day)
--2018
('2018-01-01'), --2 January Monday New Year’s Day (substitute day)
('2018-04-14'), --14 April Friday Good Friday
('2018-04-17'), --17 April Monday Easter Monday
('2018-05-01'), --1 May Monday Early May bank holiday
('2018-05-29'), --29 May Monday Spring bank holiday
('2018-08-28'), --28 August Monday Summer bank holiday
('2018-12-25'), --25 December Monday Christmas Day
('2018-12-26'), --26 December Tuesday Boxing Day
--2019
('2019-01-01'), --2 January Monday New Year’s Day (substitute day)
('2019-04-14'), --14 April Friday Good Friday
('2019-04-17'), --17 April Monday Easter Monday
('2019-05-01'), --1 May Monday Early May bank holiday
('2019-05-29'), --29 May Monday Spring bank holiday
('2019-08-28'), --28 August Monday Summer bank holiday
('2019-12-25'), --25 December Monday Christmas Day
('2019-12-26') --26 December Tuesday Boxing Day
-- SLA Calculation from tickets
SELECT s.[d_ticket_type_id]
,b.[priority]
,s.[ticket_id]
,s.[ticket_header]
,a.[assignment_begin_date]
,s.[ticket_resolved]
,SUM(CASE WHEN DATENAME(dw,x.dt) IN ('Saturday','Sunday') THEN 0
--WHEN hh.HDate IS NULL THEN 0
WHEN x.dt > a.[assignment_begin_date] AND x.dt < s.[ticket_resolved] THEN 12
WHEN CONVERT(CHAR(8),a.[assignment_begin_date])<CONVERT(CHAR(8),s.[ticket_resolved])
THEN CASE WHEN x.dt = CONVERT(CHAR(8),a.[assignment_begin_date])
THEN 18 - CASE WHEN CONVERT(CHAR(2), a.[assignment_begin_date],108) >=18 THEN 18
WHEN CONVERT(CHAR(2), a.[assignment_begin_date],108)<6 THEN 6
ELSE CONVERT(CHAR(2), a.[assignment_begin_date],108)
END
ELSE
18 - CASE WHEN CONVERT(CHAR(2), s.[ticket_resolved],108)>=18 THEN 18
WHEN CONVERT(CHAR(2), s.[ticket_resolved],108)<6 THEN 6
ELSE CONVERT(CHAR(2), s.[ticket_resolved],108)
END
END
ELSE DATEDIFF(dd,CASE WHEN CONVERT(CHAR(2), a.[assignment_begin_date],108)<6 THEN 6
WHEN CONVERT(CHAR(2), a.[assignment_begin_date],108)>=18 THEN 18
ELSE CONVERT(CHAR(2), a.[assignment_begin_date],108)
END
,CASE WHEN CONVERT(CHAR(2), s.[ticket_resolved],108)<6 THEN 6
WHEN CONVERT(CHAR(2), s.[ticket_resolved],108)>=18 THEN 18
ELSE CONVERT(CHAR(2), s.[ticket_resolved],108)
END)
END) AS 'SLA hours'
FROM [Table1].[dbo].[f_ticket] s
INNER JOIN [Table1].[dbo].[b_ticket_assignee] a ON a.f_ticket_id = s.f_ticket_id
INNER JOIN [Table1].[dbo].[d_priority] b ON s.d_priority_id = b.d_priority_id
--INNER JOIN #Holidays hh ON 1=1
,(SELECT DATEADD(DAY,NUMBER,'20170401') AS dt
FROM master..spt_values WHERE TYPE='p') AS x
WHERE a.[assignment_begin_date] BETWEEN '2017-04-01' AND '2017-04-30'
AND x.dt BETWEEN CONVERT(CHAR(8),a.[assignment_begin_date],112)
AND CONVERT(CHAR(8),s.[ticket_resolved],112)
AND s.[d_ticket_type_id] IN ('101','104','105')
AND a.[d_assignee_id] = '151'
GROUP BY s.[d_ticket_type_id]
,a.[assignment_begin_date]
,s.[ticket_resolved]
,s.[ticket_id]
,s.[ticket_header]
,b.[priority]
-- END of SLA Calculation
--Step 4 - Clean up the temp table
--clean up
DROP TABLE #Holidays
/*Code Ends*/
May 12, 2017 at 12:53 pm
Is it possible to provide a simplified example of what you're trying to get at, (maybe using only temp tables and sample data, not using tables we don't have access to from your post) and then what you want the result set to look like? There's an awful lot going on there.
May 12, 2017 at 1:52 pm
Why does it have to be a temp table? I use a permanent table for my SLA calculations.
May 13, 2017 at 12:31 pm
RonKyle - Friday, May 12, 2017 1:52 PMWhy does it have to be a temp table? I use a permanent table for my SLA calculations.
Because it simplifies cleanup for people who are working on your problem. Temp tables are automatically dropped, so they don't have to remember to go back and drop the table that contains data that is only useful to them to help solve your problem.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 13, 2017 at 4:16 pm
Temp tables are automatically dropped, so they don't have to remember to go back and drop the table that contains data that is only useful to them to help solve your problem
First, you should explicitly drop your temp tables. Unless something is changed, they will hang around, even if they can't be used with the closing of the connection. Always clean up your work.
Second, that data can be useful for other things, and there's no point recreating the wheel for each time. Especially since holiday table can include additional days around the holiday that would constitute the holiday weekend. Otherwise other things could be made temp tables too, but I wouldn't recommend that.
May 13, 2017 at 8:21 pm
RonKyle - Saturday, May 13, 2017 4:16 PMTemp tables are automatically dropped, so they don't have to remember to go back and drop the table that contains data that is only useful to them to help solve your problem
First, you should explicitly drop your temp tables. Unless something is changed, they will hang around, even if they can't be used with the closing of the connection. Always clean up your work.
I'm pretty sure that's not true, Ron. Temp tables have always been auto-magically dropped when the session ends and, as of 2005, the "allocation" is temporarily saved for re-use even if you explicitly drop the temp table. For heavily used code, there can actually be a performance advantage to not dropping Temp Tables at the end of a proc. Don't take my word for it though. There a several good articles on the subject and the following is one of the better ones on the subject.
http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx
There's also the original MS Whitepaper on the subject. Search for the word "drop" in the following.
https://technet.microsoft.com/en-us/library/cc966545.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply