SLA Calculation with Hours of Operation

[dc88310]

i am a bit underwater on this one and reaching out for help.

I have an assignment in where I need to calculate a 2 hour SLA based the hour of operation from 8am to 10pm.

I have a start datetime and an end datetime.  and i can subtract those two.. but here is the issue i am having with this.  The two hour time frame when something is approaching the end of the operational day (10pm).. since this is like a 2 hour ticker.. .i cannot have it count 10pm to 7:59am in the two hour calculation.

I have looked for 4 days now and cannot make sense of anything i have read, hence why i said i was a bit underwater.  I prob understated that.. i am drowning on this one.  I need to figure out how to stop the two hour calculation (ticker) when its outside of the hours of operation and have it resume when the hours of operation start again. 😫

Thanks to anyone that can help me!

[Reeza]

Please provide sample data which reflects your input data and what you expect as output, given that input data.

[ballardw]

Example data helps. Really.

We have people all the time asking about calculations such as dates and times and it takes a while to determine the format of their data.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Make sure to include an example of that  10Pm issue. Then very carefully describe what you expect the result to be.

And it may not hurt to describe just what an "SLA calculation" is. Jargon and TLA's (three-letter-acronyms) can be very group/industry/location specific and one man's SLA may be another's FEBA.

[Reeza]

You mean SLA isn't software license agreements?

[ballardw]

---

@Reeza wrote:
You mean SLA isn't software license agreements?

---

My first thought was Symbionese Liberation Army, but that might be giving my age away a bit.

[D_Z_]

ya'all made me chuckle.  I guess i have worked in call centers so long that the terms i incorrectly think everyone should know them.. LOL

SLA = Service Level Agreement.  Which is a calculation on the percentage of workload handled in a certian timeframe.  In my case its 2 hours. 

There is a datetime field for when the issue was received and a datetimestamp when the issue was resolved.  If the issue was handled within 2 hours, then its within the Service Level Agreement, if not then its not.

For another layer of complication to the matter, we only work between the hours of 8am and 10pm.  So if an email comes in at 10:05pm, the two hour service level agreement would start when we open the next morning at 8am.

I have a two hour ticker... if the two hours ends after 10pm... i am basically trying to figure out how to always keep the two hour ticker (so to speak) within our operating hours. 

examples: 

1.)Issue comes in at 10:30pm, no one is on staff to answer.  The Start of the 2 hour time frame would start at 8am

2.)Issue comes in at 9pm, but the agents are unable to get to the issue prior to leaving for the night.  There is one hour from 9pm to 10pm... so the rest of the two hours would begin at 8am and if it is not worked by 9am than it would be outside the acceptable hours.

I sure hope this makes sense to someone.. LOL.  I am really trying here.  I can prob get some sample data...  but i have to build it from my existing data because of my companies security policy... but its really just based on the start time and end time... for this metric the other fields do not play a part in this at all.

Thanks

[Reeza]

---

@D_Z_ wrote:

I sure hope this makes sense to someone.. LOL.  I am really trying here.  I can prob get some sample data...  but i have to build it from my existing data because of my companies security policy... but its really just based on the start time and end time... for this metric the other fields do not play a part in this at all.

Thanks

---

Make fake data in Excel is trivial. Just enter some random IDs and enter your start and end dates times to show what you have. And what the expected response is. I do this almost every day when I get stuck on an issue because I find breaking it down usually helps me find the solution quickly. 

[mkeintz]

Is it possible in your data that you could have start at 9:30PM on Monday, and end at 8:00AM Wednesday?  I.e. can the time span include more than one overnight?

[dc88310]

yes... it is possible cross more than one day.. so it could come in on monday and be resolved wednesday if the volume coming in is high enough.

DC

[mkeintz]

I'd suggest a two statement solution:

1. Calculate the direct difference in datetimes,   i.e.    ENDDT-BEGDT
2. From the calculation subtract 10 hours times the number of  days between BEGDT and ENDDT

slatime = ENDDT - BEGDT;
slatime = slatime  - '10:00:00't * (datepart(enddt)-datepart(begdt);

put slatime=time8.0;  ** slatime as HH:MM:SS **;

The "trick" here is to use  '10:00:00't  (ordinarily seen as a time literal representing 10AM) as a multiplier.  This works because the underlying value for '10:00:00't is the number of seconds after midnight (edited addition: which is the same as the number of seconds between 10PM and 8AM).  And the DATEPART functions provide a means to count the number of "overnights" between BEGDT and ENDDT.

[smantha]

Do you have two separate fields for date and time? If so you can follow the following logic

data sample;
date=mdy(7,23,2020);
time = hms(20,30,00);
output;
date=mdy(7,24,2020);
time = hms(20,30,00);
output;
date=mdy(7,24,2020);
time = hms(19,30,00);
output;
date=mdy(7,25,2020);
time = hms(19,30,00);
output;
date=mdy(7,26,2020);
time = hms(19,30,00);
output;
format date date9. time time8.;
run;

data _null_;
set sample;
if weekday(date)>=2 and weekday(date) <= 6 and hour(time) < 20 then do;
	sla_end_date = date;
	sla_end_time = time+7200;
end;

if weekday(date)>=2 and weekday(date) < 6 then do;
if time > hms(20,0,0) then ticker_time_remaining = 7200-22*60*60 + time  ;
sla_end_date = date+1;
sla_end_time= hms(8,0,0) + ticker_time_remaining ;
end;
else if weekday(date) = 6 then do;
 if time > hms(20,0,0) then do;
     ticker_time_remaining = 7200-22*60*60 + time ;
	sla_end_date = date+3;
	sla_end_time= hms(8,0,0) + ticker_time_remaining ;
  end;
  else do;
     sla_end_date = date;
	sla_end_time = time+7200;
  end;
end;
else if weekday(date) = 7 then do;
    sla_end_date = date+2;
	sla_end_time= hms(8,0,0) + 7200;
end;
else if weekday(date) = 1 then do;
	sla_end_date = date+1;
	sla_end_time= hms(8,0,0) + 7200 ;
end;
put date= date8. time=time8. sla_end_date =date8. sla_end_time= time8.;
run;