Finding a total duration from a set of given dates 3

[TheInsider]

Hello,
What I am looking for here may simply be an algorithm, so I could have posted it in another forum, however I am coding this in Access VBA, so I am placing it in this forum.

My problem is that I have a table of Start and End dates, which pertain to a client's employment history. I need a total of time spent working at all jobs for each client. To throw a wrench in the gears (to make this difficult)... if a client has two jobs at the same time, the overlapping or consecutive months do not get added twice.

for example, if a client had 3 placements; A, B, and C...

A = Placement #1 (7 months)
B = Placement #2 (7 months)
C = Placement #3 (4 months)
| = 1 Month

AAAAAAA||||||||||
|||BBBBBBB|||||||
|||||||||||||CCCC

(where farthest left is of the string of A's, B's, or C's is the Start date, and the farthest right is the End Date)

So Placement A and B overlap. The total time for Placement A and B should be 10 months + 4 months for placement C, totalling 14 months.

I already know how to calculate the difference between 2 dates. I have programmer's logic block right now, so I can't think of an easy way to do this with the possibility of an unlimited number of placements.


Rob Marriott
rob@career-connections.net

 

[raskew]

As I understand it, you want to identify the overlapping periods of placement, then you'll use your own code to determine months of placement.

For example, tblPlacements shown below, contains four periods of placement:

(1) 12/8/99 - 7/21/00
(2) 8/15/00 - 11/2/00
(3) 11/5/00 - 12/29/00
(4) 1/1/01 - 2/10/01


PlacementID EmployeeID StartDate EndDate
(AutoID-Key) (Long) (date) (date)
1 12345 12/8/1999 2/14/2000
2 12345 2/1/2000 7/21/2000
3 12345 6/1/2000 6/30/2000
4 12345 8/15/2000 9/29/2000
5 12345 9/1/2000 11/2/2000
6 12345 11/5/2000 12/29/2000
7 12345 1/1/2001 1/30/2001
8 12345 1/5/2001 2/10/2001


This code will identify them for you:

Function ProcessPlacements()
'*******************************************
'Name: ProcessPlacements (Function)
'Purpose: Loop through rs, identifying
' overlapping time periods
'*******************************************

Dim db As Database
Dim rs As Recordset
Dim dstartdate As Date
Dim denddate As Date
Dim strSQL As String
Dim n As Integer, i As Integer
Dim datehold As String


Set db = CurrentDb
strSQL = "SELECT PlacementID, EmployeeID, StartDate, EndDate " _
& "FROM tblPlacements ORDER BY StartDate, EndDate;"
Set rs = db.OpenRecordset(strSQL)

'get record count
rs.MoveLast
n = rs.RecordCount
rs.MoveFirst

If n > 0 Then
dstartdate = rs!startdate
denddate = rs!enddate
rs.MoveNext
For i = 1 To n - 1
If rs!startdate >= dstartdate And rs!startdate <= denddate Then
If rs!enddate > denddate Then
'Note: theMax is user-defined function
denddate = theMax(rs!enddate, denddate)
End If
rs.MoveNext
Else
datehold = &quot;From #&quot; &amp; dstartdate &amp; &quot;# to #&quot; &amp; denddate &amp; &quot;#&quot;
Debug.Print datehold
dstartdate = rs!startdate
denddate = rs!enddate
rs.MoveNext
End If
Next i
End If
datehold = &quot;From #&quot; &amp; dstartdate &amp; &quot;# to #&quot; &amp; denddate &amp; &quot;#&quot;
Debug.Print datehold
rs.Close
db.Close
Set db = Nothing

'open debug window
docmd.RunCommand acCmdDebugWindow

End Function
'***********************************
Function theMax(thefirst As Variant, thesecond As Variant) As Variant
'returns the greater of two values

theMax = IIf(thefirst <= thesecond, thesecond, thefirst)

End Function
'***********************************

 

[Quehay]

Let me throw this out and see if it sticks on the wall(your original question seems to be vague regarding 'client' vs 'employee' --is there a typo?):

You have a table for the Client info: tbl_Clients or tbl_Emps

You have a table for job instances: tbl_Contracts

tbl_Contracts has the following fields:

Cntrct_StartDt
Cntrct_CloseDt

A query joining tbl_Clients with tbl_Contracts
would yield a record for each contract. If you have one query qry_AbsoluteStartDt that pulls all start dates for a given Client (employee?) and uses the MIN function you'll get the absolute start date for all concurrent contracts.

Do the same again with a qry_AbsoluteEndDt that uses MAX function to get the close date for the overlapping contracts. Summarize these in a query that brings the AbsDate values from the previous two and do the usual DateDiff functions to get the true start to finish for all the concurrent projects.

*Remember that the DateDiff will give skewed results if there are a slew of holidays in a given period (weekends are consistent), e.g., Nov. 1 - Jan. 10.

Please post your results and ultimate solution!

 

[MichaelRed]

Quehay,

Your approach as described would, I think, not be valid for instances where there were gaps in the &quot;employment&quot;? Otherwise a simple query using the SQL keywords Max and Min should do it with ease - even using the simplistic table structure in the original sample data, as in:

SELECT tblEmpAct.EmpId, Min(tblEmpAct.StDt) AS EmpSt, Max(tblEmpAct.EndDt) AS EmpEnd, DateDiff("d",[EmpSt],[EmpEnd]) AS EmpDays
FROM tblEmpAct
GROUP BY tblEmpAct.EmpId;

yields 430 days - but this ignores the two days at the end of 2000 where the emp was not assigned to any job.

(please excuse my not using the same field names, I am just in a hurry)



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[TheInsider]

Hello,
Thanks for all of your input - I really appreciate it! I will try raskew's function. To answer Quehay's question, this database is for a government assistance/employment program, so everyone in the database will be a client. The people will come in unemployed, and we will begin tracking their job search progress. Hopefully they will get a job.

When they have a job or jobs, I need to be able to display the total amount of time (in months to 2 decimal places) spent working. Factoring in holidays is not required here.

As MichaelRed posted; Quehay's method would work as long as there are no time gaps between jobs. Unfourtunately, I would say in this case that 99% of the clients will have time gaps between jobs.

I have another database to work on today, but I will post the results as soon as I can (hopefully, by the end of tomorrow). Rob Marriott
rob@career-connections.net

 

[MichaelRed]

raskew,
Yours works, however it requires the traversing of the recordset three times (.MoveLast, .MoveFirst, and .MoveNext for the record count). I minimally modified the process to only traverse the recordset a single time. I also added the 'calculation of months to two decimal places', and made this the return value of the proedure.

Rob,
You did not indicate exactly HOW you will instantiate the function, which can materially affect the processing time required. The function - as coded in this thread - probably needs to be viewed as an example/work in progress and not as a &quot;canned soloution&quot;. Neither raskew or I have included the &quot;EmpId&quot; or any output of the Total Time perior involved, or any percentage (or ratio) of the time working w/ respect to the time enrolled. I would think these additional items need to be addressed in your overall app.





MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[TheInsider]

MichaelRed,
I'll keep this in mind, as the function will be plugged into an already slow and complex query containing many sub-queries - a rollup of many statistics. I wish I could play around with it right now, but I just received enough work on another database to keep me busy for the next 48 hours, so I won't be able to claim success today. =( Rob Marriott
rob@career-connections.net

 

[raskew]

MichaelRed,

Eighteen to twenty years ago, it would have been significant to know that a procedure only looped once, rather than three times. Today, in our world of Windows glut, it's not even worth consideration. If the choice is pragmatic vs. perfectionist, I'll choose pragmatic every time.

Michael, go wash your hands again, and again, and again, and again…(p.s., has anyone checked to see if we left the oven on?); oh golly, did you turn off the iron? Better go back and check!!

 

[TheInsider]

Hi,
Just posting to update anyone who posted in this thread - I know people don't like it when the person asking the question just drops off the face of the planet without letting you know if they made any progress. I haven't been able to work on this project yet... I have a couple more days work to do on another database first, but I will post my results when I get back to that project. Rob Marriott
rob@career-connections.net

 

[TheInsider]

Hey,
If anyone still cares... I was finally able to get back to this project! I came up with a sloppy function, but hey - it was a sloppy database. The database was designed by an amateur and has caused me alot of headaches. Anyways, I am sure that this is not an efficient solution. I haven't had time to optimize the code, but it seems to work. Oh, the only thing to note is that I forgot the proper equation to get an accurate difference in months, so for the time being I used DateDiff(&quot;d&quot;, Date1, Date2) / 30. If someone could give me the proper calculation, that would be great! It's very similar to raskew's function.

Public Function MonthsWorked(SIN As String) As Single
    Dim precCurrent As Recordset, pdatStart As Date, pdatEnd As Date, _
        pintCounter As Integer
    
    MonthsWorked = 0
    
    Set precCurrent = CurrentDb.OpenRecordset( _
        "SELECT * FROM CLIENT_WORKING_INFORMATION_tbl WHERE SIN = '" & SIN _
        & "' ORDER BY ActualStartDate")
    
    If (precCurrent.RecordCount <> 0) Then
        precCurrent.MoveLast
        precCurrent.MoveFirst
        
        pdatStart = precCurrent![ActualStartDate]
        pdatEnd = IIf(IsNull(precCurrent![WorkingEndDate]), Date, _
                        precCurrent![WorkingEndDate])
        
        If (precCurrent.RecordCount = 1) Then
            MonthsWorked = DateDiff(&quot;d&quot;, pdatStart, pdatEnd) / 30
        Else
            precCurrent.MoveNext
            
            While Not precCurrent.EOF
                If ((precCurrent![ActualStartDate] >= pdatStart) And _
                    (precCurrent![ActualStartDate] <= pdatEnd)) Then
                        If (IIf(IsNull(precCurrent![WorkingEndDate]), Date, _
                            precCurrent![WorkingEndDate]) > pdatEnd) Then _
                                pdatEnd = IIf(IsNull(precCurrent![WorkingEndDate]), Date, _
                                                precCurrent![WorkingEndDate])
                Else
                    MonthsWorked = MonthsWorked + (DateDiff(&quot;d&quot;, pdatStart, pdatEnd) / 30)
                    pdatStart = precCurrent![ActualStartDate]
                    pdatEnd = IIf(IsNull(precCurrent![WorkingEndDate]), Date, _
                                    precCurrent![WorkingEndDate])
                End If
            
                precCurrent.MoveNext
            Wend
        
            MonthsWorked = MonthsWorked + (DateDiff(&quot;d&quot;, pdatStart, pdatEnd) / 30)
        End If
    End If
    
    precCurrent.CLOSE
End Function

Thanks for all of your help, Rob Marriott
rob@career-connections.net

 

[MichaelRed]

Rob,

The problem lies in the definition of &quot;accurate&quot;. Help/DateDiff will show a couple of options for &quot;Months&quot; (&quot;M&quot;, &quot;MM&quot. Look at these and decide for yourself. Read them carefully, as the difference is in the details.

Gnenerally, you solution would be acceptable unless you need calendar months - as opposed to 'effective whole months'.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[TheInsider]

Hi,
I couldn't find the &quot;MM&quot; DateDiff option in the Access 97 help. I need to display the number of calendar months between Date1 and Date2, to 2 decimal places. So I can't really use DateDiff(&quot;d&quot;,Date1,Date2)/30 because December, for example, has 31 days and February has 28. For example Jan 12th, 2001 and Feb 15th, 2001 should return 1.xx months ellapsed. I can definatley see the problem that can be caused due to different lengths in some months. I found the following calculation to accurately return a person's age:

Public Function Age(DOB, Optional vDate) As Byte
    If Not IsDate(vDate) Then vDate = Date

    If IsDate(DOB) Then
        Age = DateDiff("yyyy", DOB, vDate) + (DateSerial(Year(vDate), Month(DOB), Day(DOB)) > vDate)
    Else
        Age = Null
    End If
End Function

This would probably work great if modified to return the number of months instead of years. The only problem is that it will return an integer value (I mean the literal term integer). I'll may just end up using this function though. Rob Marriott
rob@career-connections.net

 

[MichaelRed]

Rob,

What 'definition' of &quot;calendar&quot; months - to two decimal places. This doesn't really 'compute' for variable length months, at least not in any commonly understood math.

Three days worth of Feb (Non-Leap Year) are 'worth considerably more than three days worth of Jan? So are you suggesting that any partial months need to be fractionalized to the number of days in the specific Month? Then add these?

So Jan 18 through Feb 18 is:

18/31 + 18/28 = 1.223502 (rounded to 1.22 Months)

Alternatively, to get the number of 12th's of the year&quot;

? Format(Datediff(&quot;d&quot;, #1/18/2001#, #18/2/2001#) * (12/365), &quot;###.##&quot
1.02


I think the issue here is understanding what is meant by &quot;calendar months to two decimal places&quot;!


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[TheInsider]

Hi,
I think that you are right. Our client requested that we have such a calculation. If a person starts on Jan 1st and ends on Jan 31, then you know that you have exactly 1 month - but this would consider 1 month to be 31 days. With Feb 1st to Feb 28th, they would also have completed 1 month - with 1 month equaling 28 days. So if I have Feb 16th to April 3rd; What do I consider a months to be - so that I can calculate this? I originally chose to take an average and use 30 days. They said that this was not accurate enough. I'm going to scrap the 2 decimal places and see if the Age function can be modified to work for months ellapsed. Rob Marriott
rob@career-connections.net