Hi all,
i'm looking to speed up a process LENGTHY at work and i don't know the best way to go about it. It's just to produce some reports for our call logging software, this scalar function brings in the hours remaining on a job. Does anyone have any advice at all? Would limiting the character size of the variables have a noticeable effect?
B@
i'm looking to speed up a process LENGTHY at work and i don't know the best way to go about it. It's just to produce some reports for our call logging software, this scalar function brings in the hours remaining on a job. Does anyone have any advice at all? Would limiting the character size of the variables have a noticeable effect?
Code:
--get the time left on an issue
=============================================
ALTER FUNCTION [dbo].[GetHoursLeftFULL]
(
@IssueID int
)
RETURNS DECIMAL(20,10)
AS
BEGIN
-- Declare the return variable here
DECLARE @HoursLeftFULL DECIMAL(20,10)
declare @HoursElapsed decimal(20,10)
declare @startdate varchar(20)
declare @enddate varchar(20)
declare @motion varchar(10) = 'Halt' --or 'Moving'
declare @engine varchar(10) = 'Stopped' --or 'Running'
declare @brakes varchar(10) = 'Applied' --or 'Released'
declare @mark varchar (20)
declare @seq smallint = 1
declare @entryline smallint = 1
declare @time datetime
declare @CompSLAHrs DECIMAL(20,10)
set @startdate = (select CAST(min(created) as date) from [VS019-GEMINI].[GEMINI].[dbo].gemini_issuehistory where issueid = @issueid)
set @enddate = (select CAST(GETDATE() as date))
declare @IssueEventsTemp table
(
seq int,
issueid int,
history nvarchar(250),
[time] datetime,
mark nvarchar(30),
entryline int,
motion nvarchar(30),
timeaccruedsecs int
);
with mycte as
(
select cast(@startdate as datetime) DateValue
union all
select DateValue + 1
from mycte
where DateValue < @enddate
)
Insert into @IssueEventsTemp
SELECT ROW_NUMBER() over (ORDER BY time asc) as seq, *
FROM
(
select @issueid issueid, 'BRAKES OFF' as history, dateadd(minute, 540, datevalue) as 'Time', 'BRAKES OFF' as 'mark', NULL as entryline, ' ' as motion, 0 as TimeAccruedSecs
from mycte
where LEFT(datename(weekday, DateValue), 3) not in ('Sat', 'Sun')
and CAST(DateValue as date) not in (select holiday from [VS019-GEMINI].[GEMINI].[dbo].ListofHolidays)--prevents brakes off/on for holidays
UNION ALL
select @issueid, 'BRAKES ON' as history, dateadd(minute, 1050, datevalue) as 'Time', 'BRAKES ON' as 'mark', NULL as entryline, ' ' as motion, 0 as timeaccruedsecs
from mycte
where LEFT(datename(weekday, DateValue), 3) not in ('Sat', 'Sun')
and CAST(DateValue as date) not in (select holiday from [VS019-GEMINI].[GEMINI].[dbo].ListofHolidays)--prevents brakes off/on for holidays
UNION ALL
select issueid, history, created as 'Time', case when history in ('Created','Issue status changed to Acknowledged', 'Issue status changed to Assigned', 'Issue status changed to Development Investigation','Issue status changed to Reopened', 'Issue status changed to Escalated', 'Issue status changed to In Progress') then 'ENGINE ON' ELSE 'ENGINE OFF' end as 'mark', NULL as entryline, ' ' as motion, 0 as timeaccruedsecs
from [VS019-GEMINI].[GEMINI].[dbo].gemini_issuehistory
where issueid = @issueid
and (history = 'created' or history like 'Issue status%')
UNION ALL
select @issueid, 'ENGINE OFF', GETDATE(), 'ENGINE OFF', NULL, '', 0 --this line adds an engine off at the time of calling this function
) as data
WHERE EXISTS (select issueid from KPI2015 where timetostart = 0)
order by Time
OPTION (MAXRECURSION 0)
--adding in the marks
while (@seq <= (select MAX(seq) from @IssueEventsTemp))
begin
set @mark = (select mark from @IssueEventsTemp where seq = @seq)
set @time = (select Time from @IssueEventsTemp where seq = @seq)
if @mark = 'ENGINE ON'
begin
set @engine = 'Running'
end
if @mark = 'ENGINE OFF'
begin
set @engine = 'Stopped'
end
if @mark = 'BRAKES OFF'
begin
set @brakes = 'Released'
end
if @mark = 'BRAKES ON'
begin
set @brakes = 'Applied'
end
if (@brakes = 'Released' and @engine = 'Running')
BEGIN
set @motion = 'Moving'
END
if NOT (@brakes = 'Released' and @engine = 'Running')
BEGIN
set @motion = 'Halt'
END
--first time we get a Moving
IF (@motion = 'Moving' and @entryline = 1)
BEGIN
Update @issueeventstemp
set motion = 'Moving', entryline = @entryline
where seq = @seq
set @entryline +=1
goto wayout --exit the function
END
--other times we get a moving only write log if previous entry was a halt
IF (@motion = 'Moving' and @entryline <> 1)
BEGIN
IF (select motion from @IssueEventsTemp where entryline = @entryline -1) = 'Halt'
BEGIN
Update @issueeventstemp
set motion = 'Moving', entryline = @entryline
where seq = @seq
set @entryline +=1
END
goto wayout --exit the function
END
--if we get a halt, only write if previous entry was a moving
IF @motion = 'Halt'
BEGIN
IF (select motion from @IssueEventsTemp where entryline = @entryline -1) = 'Moving'
BEGIN
Update @issueeventstemp
set motion = 'Halt', entryline = @entryline
where seq = @seq
set @entryline +=1
END
END
wayout:
set @seq +=1
END
--now add in the time accrued
update @issueeventstemp
set [@issueeventstemp].[timeaccruedsecs] = zxc.timeaccruedsecs
from @issueeventstemp
join
(select entryline, DATEDIFF(SECOND, (select time from @IssueEventsTemp where entryline = it.entryline-1), time) as timeaccruedsecs
from @IssueEventsTemp it
where motion = 'Halt') as zxc
on [@issueeventstemp].[entryline] = zxc.entryline
set @HoursElapsed = (select SUM(TimeAccruedSecs)/3600.00 from @IssueEventsTemp)
--get the time to complete SLA time
set @CompSLAHrs = (select --gi.projectid, RIGHT(gip.prioritydesc, 2) as 'priority', gp.projectcode,
case when RIGHT(gip.prioritydesc, 2)='P1' then sla.P1Comp when RIGHT(gip.prioritydesc, 2)='P2' then sla.P2Comp when RIGHT(gip.prioritydesc, 2)='P3' then sla.P3Comp when RIGHT(gip.prioritydesc, 2)='P4' then sla.P4Comp end as SLAHours
from
[VS019-GEMINI].[GEMINI].[dbo].gemini_issues gi
join [VS019-GEMINI].[GEMINI].[dbo].gemini_projects gp
on gi.projectid = gp.projectid
join [VS019-GEMINI].[GEMINI].[dbo].gemini_issuepriorities gip
on gi.issuepriorityid = gip.priorityid
join [VS019-GEMINI].[GEMINI].[dbo].zzsla sla
on gi.projectid = sla.projectid
where issueid = @issueid)
-- Add the T-SQL statements to compute the return value here
SELECT @HoursLeftFULL = @CompSLAHrs-@HoursElapsed
-- Return the result of the function
RETURN @HoursLeftFULL
ENDB@