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    
 
   
ADezii
post Jun 26 2019, 10:47 AM
Post#21



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


@vajeeth:
I took mostly all of the suggestions mentioned by WildBird in Posts 18 & 19 and came up with yet another Demo. Rather than dump the existing Code, I kept it in place and analyzed minutes rather than hours. Now, it appears to be very accurate but I'm sure that you and WildBird will be better judges as to its accuracy. The only downside that I see right now is that, since it literally analyzes every minute, the execution of the Code is much slower. Once I am 100% sure that it is accurate under all circumstances, which hopefully comes, than I'll worry about increasing the processing time.
Attached File(s)
Attached File  SLA_3.zip ( 26.32K )Number of downloads: 3
 
Go to the top of the page
 
vajeeth
post Jun 27 2019, 04:28 AM
Post#22



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


Hi ADezii,

Yes, I will check on the code and revert back within tomorrow since I am in travelling I don't have much power in my laptop.
Sorry for the delay.
Go to the top of the page
 
vajeeth
post Jun 27 2019, 08:41 PM
Post#23



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


Hi ADezii,

Now we get 1 Mins less SLA End time if we don't round off the mins. Please look the below examples which I have tried.

Task Received Importance SLA_End_Date Expected
24-06-2019 23:38:01 High 25-Jun-19 22:59:01 25-Jun-19 23:00:00
26-06-2019 05:48:18 Medium 27-Jun-19 22:59:18 27-Jun-19 23:00:00
28-06-2019 23:46:01 High 01-Jul-19 22:59:01 01-Jul-19 23:00:00


Sorry for the messy table, I dont know how to make table in post. iconfused.gif
Go to the top of the page
 
ADezii
post Jun 28 2019, 08:52 AM
Post#24



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


  1. The Code and Logic was never designed to work with a seconds component in the [Task Received] Date/Time Value. I have adjusted the Code so that now, if the seconds component < 30 it will round it down to the nearest minute, and if the seconds are >= 30 it will round it up to the nearest minute. Examples are posted below for the [Task Received] Field:
    CODE
    #6/24/2019 11:38:01 PM# ==> rounds seconds down to #6/24/2019 11:38:00 PM#
    #6/26/2019 5:48:48 AM# ==> rounds seconds up to #6/26/2019 5:49:00 AM#
    etc...
  2. Download the Revision, I think we're getting close. laugh.gif

QUOTE
I dont know how to make table in post.

I have also Uploaded a Utility that will generate the necessary BBCodes for a Table or Query. Simply click on the Command Button and enter a valid Query or Table Name in the InputBox() Dialog (qryTest will work for this Demo). The necessary BBCodes will be generated in the Form of a Text File (BBCodes.txt) in the same Folder as your DB. This Text File will then be opened in Notepad. Simply Copy the contents of this File and Paste it into your Post. The Table will automatically be generated. Sample OUTPUT posted for your Review:
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 1:00:00 PMMedium6/26/2019 1:00:00 PM
6/24/2019 11:30:00 PMHigh6/25/2019 11:00:00 PM
6/24/2019 11:38:01 PMHigh6/25/2019 11:00:00 PM
6/26/2019 5:00:00 AMMedium6/27/2019 11:00:00 PM
6/26/2019 5:48:18 AMMedium6/27/2019 11:00:00 PM
6/28/2019 10:45:00 PMHigh7/1/2019 10:45:00 PM
6/28/2019 11:45:00 PMHigh7/1/2019 11:00:00 PM
6/28/2019 11:46:01 PMHigh7/1/2019 11:00:00 PM
7/5/2019 10:20:37 PMLow7/10/2019 10:21:00 PM


This post has been edited by ADezii: Jun 28 2019, 09:04 AM
Attached File(s)
Attached File  SLA_4.zip ( 25.5K )Number of downloads: 2
Attached File  BBCodes.zip ( 25.45K )Number of downloads: 2
 
Go to the top of the page
 
vajeeth
post Jul 1 2019, 11:21 PM
Post#25



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


Hi Adezii,

Good day!!!

I think I haven't explained clearly last time.

The round of must happen only if the Task received time is before/after shift time.

For example:
> If mail is received on 7/5/2019 10:20:37 PM with Low importance. Then the SLA end time remains as 7/10/2019 10:20:37 PM. (Within Shift time)
> If mail is received on 7/5/2019 05:20:37 PM with Low importance. Then the SLA end time will be 7/09/2019 11:00:00 PM. (Before Shift time)
> If mail is received on 6/28/2019 11:46:01 PM PM with High importance. Then the SLA end time will be 7/1/2019 11:00:00 PM. (After Shift time)
This post has been edited by vajeeth: Jul 1 2019, 11:29 PM
Go to the top of the page
 
ADezii
post Jul 2 2019, 07:01 AM
Post#26



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


QUOTE
> If mail is received on 7/5/2019 05:20:37 PM with Low importance. Then the SLA end time will be 7/09/2019 11:00:00 PM. (Before Shift time)

I assume you mean: 7/5/2019 05:20:37 AM?
Go to the top of the page
 
ADezii
post Jul 2 2019, 08:46 AM
Post#27



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


I adjusted the Code in the fRoundSeconds() Function. If the Time Value within [Task Received] is outside of the Normal Range (< #6:00 AM# Or > #11:00 PM#) then the Seconds are Round Up or Down as required. See if this does the trick:
CODE
Public Function fRoundSeconds(dteDate As Date) As Date
'Round Seconds only if the Task Received Time is < 6:00 A.M. or > 11:00 P.M.
If TimeValue(dteDate) < #6:00:00 AM# Or TimeValue(dteDate) > #11:00:00 PM# Then
  If Second(dteDate) >= 30 Then
    fRoundSeconds = DateAdd("s", 60 - Second(dteDate), dteDate)
  Else
    fRoundSeconds = DateAdd("s", -Second(dteDate), dteDate)
  End If
Else
  'Time within Range, so do not round
  fRoundSeconds = dteDate
End If
End Function
Go to the top of the page
 
vajeeth
post Jul 3 2019, 03:08 AM
Post#28



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


Hi ADezii,

You are amazing and thank you so much!!! Finally the results are perfect. notworthy.gif woohoo.gif dance.gif
Go to the top of the page
 
ADezii
post Jul 3 2019, 07:07 AM
Post#29



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


yw.gif Much of the credit belongs to WildBird who picked up on the initial inconsistencies and pointed me in the right direction. Good Luck with your Project!
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    23rd July 2019 - 08:19 AM