UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Sla Calculation Using Ms Access Query, Office 2010    
 
   
vajeeth
post Jun 20 2019, 10:48 AM
Post#1



Posts: 51
Joined: 29-July 16
From: Chennai, India


Hi Experts,

Good day,

Need your help to calculate SLA in MS Access Query.

Working Hrs starts: 06:00:00
Working Hrs end: 23:00:00


SLA has to be adjusted based on Importance of the job.

For High Importance: 17 Hrs SLA
For Medium Importance: 34 Hrs SLA
For Low Importance: 51 Hrs SLA

Saturday and Sunday not applicable(Working days are Monday to Friday). For now I am maintaining it in excel. But i need to calculate it in Access query.

I need your help in 2 things.

1. Scenario

Task Received: 20-06-2019 21:00:00

The SLA end date/time has to be automatically calculated based on Importance as mentioned above.

Result: I need to find out how many hrs left for me to complete this task from current date/time.

2. Scenario

Task Received: 20-06-2019 21:00:00

If the Task is completed less than or more than SLA hrs.

Result:I need to find out whether it is "Out of SLA" or "Within SLA" using task completed date/time.

Thanks in advance.
This post has been edited by vajeeth: Jun 20 2019, 10:54 AM
Go to the top of the page
 
ADezii
post Jun 20 2019, 01:57 PM
Post#2



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I am not exactly sure if I understand the nature of your request, but I'll give it a shot nonetheless.
  2. As I see it , you need to continually add 1 hour to the Base Date/Time [Task Received]. Each time an hour is added, you need to check the new Data/Time Value. If the Day of the week for this value falls between a Monday and Friday, and if the Hour Value falls between 6:00 A.M. and 11:00 P.M., you need to increment a new Counter.
  3. Continue this Looping process until the Counter equals the Hours for the specific Level of Importance.
  4. This is done via a Function that uses a Calculated Field that calls a Public Function where all the Logic is contained. This Function returns the SLA End Date/Time for each Record.
  5. Sample Data (tblData):
    Task ReceivedImportance
    6/20/2019 9:00:00 PMHigh
    6/21/2019 5:00:00 PMMedium
    6/24/2019 11:00:00 AMLow
  6. Create the following Query:
    SQL
    SELECT tblData.[Task Received], tblData.Importance, fCalcSLA([Task Received], [Importance]) AS SLA_End_Date FROM tblData;
  7. Function Definition:
    CODE
    Public Function fCalcSLA(dteRDate As Date, strImportance As String) As Date
    Dim bytHrs As Byte
    Dim intCtr As Integer
    Dim intHrs As Integer
    Dim dteNewDateTime As Date
    Dim intWrkHrs As Integer
    Const conHR_START = 6
    Const conHR_END = 23

    Select Case strImportance
      Case "High"
        bytHrs = 17
      Case "Medium"
        bytHrs = 34
      Case "Low"
        bytHrs = 51
      Case Else    'should never occur
    End Select

    intHrs = 1

    Do Until intWrkHrs = bytHrs
      dteNewDateTime = DateAdd("h", intHrs, dteRDate)
        'Increment Work Hour only if it falls on Monday thru Friday
        If Weekday(dteNewDateTime) <> 1 And Weekday(dteNewDateTime) <> 7 Then
          'Increment Hour only if it falls between 6:00 AM and 11:00 PM
          If Hour(dteNewDateTime) > conHR_START And Hour(dteNewDateTime) <= conHR_END Then
            intWrkHrs = intWrkHrs + 1
          End If
        End If
        intHrs = intHrs + 1
    Loop

    fCalcSLA = dteNewDateTime
    End Function
  8. Query OUTPUT:
    Task ReceivedImportanceSLA_End_Date
    6/20/2019 9:00:00 PMHigh6/21/2019 9:00:00 PM
    6/21/2019 5:00:00 PMMedium6/25/2019 5:00:00 PM
    6/24/2019 11:00:00 AMLow6/27/2019 11:00:00 AM
  9. Hope this helps.

P.S. - This approach makes some very strict assumptions: neither the [Task Received] nor the [Importance] Fields can be NULL and the [Importance] Field can only contain three Values (High, Medium, Low).
This post has been edited by ADezii: Jun 20 2019, 02:00 PM
Go to the top of the page
 
WildBird
post Jun 20 2019, 05:27 PM
Post#3


UtterAccess VIP
Posts: 3,677
Joined: 19-August 03
From: Auckland, Little Australia


Have you considered public holidays in your scenarios?

I wrote code a while back to deal with SLAs' and needed a holiday table.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
ADezii
post Jun 20 2019, 05:44 PM
Post#4



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


@WildBird:
What exactly is a SLA?
This post has been edited by ADezii: Jun 20 2019, 05:44 PM
Go to the top of the page
 
WildBird
post Jun 20 2019, 07:11 PM
Post#5


UtterAccess VIP
Posts: 3,677
Joined: 19-August 03
From: Auckland, Little Australia


SLA = Service Level Agreement
Last place I worked, it was basically the IT department and their helpdesk tickets. A level 1 was supposed to be solved in 4 hours for example, a Level 2, 8 hours etc. Was a bit more complex as some systems were supported 24/7, others Monday to Friday 06:00 to 18:00, others 08:00 to 17:00. Regardless, SLA is a Service Level agreement, meaning a company is supposed to be able to provide a solution etc in or under the time specified.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
ADezii
post Jun 21 2019, 05:39 AM
Post#6



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


Thanks for the thorough explanation. In this context, does my Code make sense?
Go to the top of the page
 
vajeeth
post Jun 23 2019, 07:59 PM
Post#7



Posts: 51
Joined: 29-July 16
From: Chennai, India


Hi ADezii,

Yes, it works perfectly. Thanks for your help. Also sorry for late reply. I was in travel so couldn't check the post.
Go to the top of the page
 
vajeeth
post Jun 23 2019, 08:01 PM
Post#8



Posts: 51
Joined: 29-July 16
From: Chennai, India


Hi WildBird,

Yes, but i thought we cant make that happen through VBA. If it is possible, can you provide me the link. Thanks in advance.
Go to the top of the page
 
WildBird
post Jun 23 2019, 08:46 PM
Post#9


UtterAccess VIP
Posts: 3,677
Joined: 19-August 03
From: Auckland, Little Australia


Queries are quicker, but code can be easier to document and modify. Issue with SLA stuff is it is usually very specific to each workplace. As management can get penalised if they dont meet SLA's I have found they always have very complex rules around them, so it makes it hard to be able to codify things at times.


Also, you havent mentioned if you are using a holiday table or not, as that will make a difference, or even if you take into account weekends for example.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
vajeeth
post Jun 23 2019, 10:25 PM
Post#10



Posts: 51
Joined: 29-July 16
From: Chennai, India


Hi WildBird,

Yes I can understand that. Holidays may change according to countries. I can make Holiday and weekend tables if needed.
This post has been edited by vajeeth: Jun 23 2019, 10:32 PM
Go to the top of the page
 
vajeeth
post Jun 23 2019, 10:32 PM
Post#11



Posts: 51
Joined: 29-July 16
From: Chennai, India


Hi ADezii,

I just came to find one issue.

The task was received on 24-06-2019 23:30:00 which is after the shift end time for the day. For this the SLA End time shows 25-06-2019 23:30:00.

But the expected result is 26-06-2019 06:30:00.
This post has been edited by vajeeth: Jun 23 2019, 10:33 PM
Go to the top of the page
 
WildBird
post Jun 24 2019, 12:04 AM
Post#12


UtterAccess VIP
Posts: 3,677
Joined: 19-August 03
From: Auckland, Little Australia


I dont have any code with me, I left it at my old job, plus it was SQL Server code, not Access.

Anyway, the code ADezii had looked like it returned whole hours, not the time.

If an incident occurred at 24-06-2019 23:33:27 then you would want the return value to be ending with ":33:27". It should just be adding the number of hours to the actual time, and skipping for weekends and holidays.




--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
ADezii
post Jun 24 2019, 11:36 AM
Post#13



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
I just came to find one issue.
The task was received on 24-06-2019 23:30:00 which is after the shift end time for the day. For this the SLA End time shows 25-06-2019 23:30:00.
But the expected result is 26-06-2019 06:30:00.

I was under the assumption that a Task would have to be received within the range of 6:00 AM to 11:00 PM and on the whole hour. Obviously this is not the case, so now the Logic would have to be changed.
Go to the top of the page
 
vajeeth
post Jun 24 2019, 11:31 PM
Post#14



Posts: 51
Joined: 29-July 16
From: Chennai, India


Hi ADezii,

Task can be received at any time even on weekends. But the SLA end time has to be adjusted based on that.

Example:

> If the Task is received after shift hours (23:00) in week days. Then the SLA starts on next day Shift starting hour (06:00).
> If the Task received on week ends, then the SLA starts from Monday shift starting hour (06:00)
Go to the top of the page
 
Minty
post Jun 25 2019, 05:40 AM
Post#15



Posts: 312
Joined: 5-July 16
From: UK - Wiltshire


Like Wildbird I have programmed SLA achievement / job response times based on 3 different objectives that changed over weekends.
This was done is SQL server, as it was easier to work with temp tables within a stored procedure process.

It definitely can be done in VBA but it it requires clear definitions of the business rules.
A function is probably the best way to go as described by ADezii
Go to the top of the page
 
ADezii
post Jun 25 2019, 07:13 AM
Post#16



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I have made a couple of Revisions to the Code, essentially the following:
    1. Changed the Constant Declarations for Start and End Times from whole number values to actual times (#6:00 PM# & #11:00#). Comparisons are now made to actual Time Values as opposed to whole Time Numbers.
    2. Once a Date/Time Values passes the Weekday (Mon. - Fri.) and Time Range (#6:00 PM# to #11:00 PM#) Checks, there is an additional check to see if the addition of the 1 hour would push the new Date/Time Value past 11:00 PM. If this is the case, then the Work Hours Counter is not incremented.
  2. Sample Data:
    Task ReceivedImportance
    6/24/2019 11:30:00 PMHigh
    6/20/2019 9:00:00 PMHigh
    6/21/2019 5:00:00 PMMedium
    6/24/2019 11:00:00 AMLow
  3. Revised Function Definition:
    CODE
    Public Function fCalcSLA2(dteRDate As Date, strImportance As String) As Date
    Dim bytHrs As Byte
    Dim intCtr As Integer
    Dim intHrs As Integer
    Dim dteNewDateTime As Date
    Dim intWrkHrs As Integer
    Const conHR_START = #6:00:00 AM#       'revised 6/25/2019
    Const conHR_END = #11:00:00 PM#        'revised 6/25/2019

    Select Case strImportance
      Case "High"
        bytHrs = 17
      Case "Medium"
        bytHrs = 34
      Case "Low"
        bytHrs = 51
      Case Else    'should never occur
    End Select

    intHrs = 1

    Do Until intWrkHrs = bytHrs
      dteNewDateTime = DateAdd("h", intHrs, dteRDate)
        'Increment Work Hour only if it falls on Monday thru Friday
        If Weekday(dteNewDateTime) <> 1 And Weekday(dteNewDateTime) <> 7 Then
          'Increment Hour only if it falls between 6:00 AM and 11:00 PM
          If TimeValue(dteNewDateTime) > conHR_START And TimeValue(dteNewDateTime) <= conHR_END Then
            'See if the addition of the 1 Hour would push the Time > 11:00 P.M., if not then Increment
            If TimeValue(DateAdd("h", 1, dteNewDateTime)) <= conHR_END Then        'added 6/25/2019
              intWrkHrs = intWrkHrs + 1
            End If
          End If
        End If
        intHrs = intHrs + 1
    Loop

    fCalcSLA2 = dteNewDateTime
    End Function
  4. Query OUTPUT:
    Task ReceivedImportanceSLA_End_Date
    6/20/2019 9:00:00 PMHigh6/21/2019 9:00:00 PM
    6/21/2019 5:00:00 PMMedium6/25/2019 5:00:00 PM
    6/24/2019 11:00:00 AMLow6/27/2019 11:00:00 AM
    6/24/2019 11:30:00 PMHigh6/26/2019 6:30:00 AM
  5. I have also attached a Revised Demo for your Review. Look it over carefully and see if there are any any issues that may arrive. This was a little trickier that I thought! pullhair.gif

This post has been edited by ADezii: Jun 25 2019, 07:17 AM
Attached File(s)
Attached File  SLA_2.zip ( 25.59K )Number of downloads: 0
 
Go to the top of the page
 
vajeeth
post Jun 25 2019, 08:15 PM
Post#17



Posts: 51
Joined: 29-July 16
From: Chennai, India


Thanks ADezii, You are awesome. I have work around with couple of scenarios myself and all works fine as expected. woohoo.gif dance.gif
This post has been edited by vajeeth: Jun 25 2019, 08:16 PM
Go to the top of the page
 
WildBird
post Jun 25 2019, 11:07 PM
Post#18


UtterAccess VIP
Posts: 3,677
Joined: 19-August 03
From: Auckland, Little Australia


I would write a number of test cases and expected results and run it against that. Having a test table means you can easily check it and see if anything else breaks if you change the code.

My numbers, and its late here and just eyeballing it.

28/06/2019 10:45:00 PM, High
Code shows
2/07/2019 6:45:00 AM

My calcs show its a Friday, and 10:45 PM. Gives 15 minutes before 11 PM, so 16.75 hrs to go.

Following Monday 6 AM plus 16.75 is 10:45 PM isnt it? Code is an hour out by my reckoning.

Happy to be shown its wrong in my calcs though.

Cheers

EDIT. Just checked
Task Received Importance
28/06/2019 10:45:00 PM High
28/06/2019 11:45:00 PM High

Test these, I get the same answer for both. 02 July 6:45 AM.



--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
WildBird
post Jun 25 2019, 11:18 PM
Post#19


UtterAccess VIP
Posts: 3,677
Joined: 19-August 03
From: Auckland, Little Australia


By test table, have TaskReceivedTime, Importance, ExpectedSLA

Have the first and last columns as date time, and the Importance as text.

Add any number of records and use different time stamps, so Fridays, Weekends, Mondays etc. Manually calculate the ExpectedSLA and put that in.

Then run a query against this, and compare the results to the ExpectedSLA column. If they dont match, there is something wrong.

Other things, hardcoding hours is probably not best idea. Better doing it more granular, as what happens if High priority becomes 16.5 hrs? Better off using minutes (unlikely to need more granular than minutes, i.e seconds)


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
ADezii
post Jun 26 2019, 08:21 AM
Post#20



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


@WildBird:
  1. First and foremost, thank you for your keen insight and debugging skills. I think the major problems here are twofold:
    1. Granularity, as long as I am dealing with a 'whole hour' concept, the Code will never work under all circumstances.
    2. I am essentially trying to Code something that I have no knowledge of. This can work under some circumstances but not this one.
  2. In any event, when I have a chance I'll dump most of this Code, rethink the problem, and hopefully make another attempt at a solution. I feel as though I at least owe this to the OP (Original Poster).
  3. Thanks again, without your intervention these problems probably would have gone unnoticed.

This post has been edited by ADezii: Jun 26 2019, 08:22 AM
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 09:37 PM