calculating total time but excluding overlapping time

[DavidCunnah]

Hi,



I have a database consisting of two tables. One is "articles" and the other is "tasks". To put it simply, I would like to find how much time the article spends in tasks, but one article can have many tasks, and they often (but not always) overlap. Tasks have a start and end date field.

I can just sum the task times to get total time spent on tasks, but as they overlap this isn't really helpful.

Any thoughts? Been trying to get my head around this for a while, and suspect it won't be straightforward.

[recyan]

Post some dummy data, for the Articles Table & Tasks Table
&
the results you expect your query to show from that.
Am sure, some one should be able to help you.

Thanks

[DavidCunnah]

OK, so take the articles table:

Article ID:
Author:
Start Date
End Date


and the Tasks table:

Task ID:
Start Date:
End Date:
Article ID:

The Article ID is the key in the article table, and is related to the task table through a one to many relationship. As an example, the following article:

Article ID: 01234
Author: J Blogs
Start Date: 01/01/2012
End Date: 10/01/2012

May have a few tasks attached as such:

Task ID: ---- 01------------ 02----------- 03
Start Date: 01/01/2012-- 01/01/2012 -- 08/01/2012
End Date: -05/01/2012 -- 03/01/2012--- 09/01/2012
Article ID: 01234---------- 01234-------- 01234

The above articles was on a task for 7 of the 10 days it existed for, but the sum of all the task time is 10 days. I want to get the 7 days - i.e. how much time it was out of the office on a task for, not the total task time.

What I would like the table to show is:

Article ID :
Time out of office :

[recyan]

1) Check out attached db & see if it gives some guidelines.

Table tblArticlesTasksDates has been created initially, with ArticleID & TaskDates as fields & together set as primary keys.
Alternatively, its creation can be handled in the function.

Run the Macro1 .

2) Was wondering why we have StartDate & EndDate in the Article table.

Thanks

[DavidCunnah]

1) Check out attached db & see if it gives some guidelines.

Table tblArticlesTasksDates has been created initially, with ArticleID & TaskDates as fields & together set as primary keys.
Alternatively, its creation can be handled in the function.

Run the Macro1 .

2) Was wondering why we have StartDate & EndDate in the Article table.

Thanks

You sir are a star! We have start and end date in the article as they are only in our element of the system for sometime, before being whisked away elsewhere, and it is a way of tracking how long they are with us for. The purpose of the exercise being ultimately to see where the bottleneck is - on 'tasks' or in the office.

Qudos to Recryan!

[recyan]

Glad you found it helpful. Wish, I could have found a simpler way or some one chips in with a simpler way.
Note :
1) I have set the field type for the ID's as Text. In case you have them as Number, you'll have to handle the declaration in the Module1 (Function) accordingly (Presently, Dim TheArticleID As String as an example, in the code).
2) My VBA skills are pretty low . Just check it thoroughly, for exception handling, etc.

Thanks

[recyan]

Perhaps, another alternative ( no additional table)

The logic tried to be used here :
1) We get the MinStartDate & MaxEndDate for each ArticleID
2) We then check each of the date from MinStartDate to MaxEndDate in each of the Task record for that ArticleID, to check whether it lies in the Date range in that record.
3) If it is found in any record, we exit the loop & move on to check the next date.
4) If it is not found, we increment the i counter in the else.
5) In the end, we compare the i counter with the Number of Task records for that ArticleID. If it is the same, it means the Date does not exist in any of the records. We subtract 1 from the Original Total No off Tasks.
6) We repeat this for each date for that article.


Save this in the previously attached db & Run it.

Public Function GetArticlesNoOffDays()
    Dim dbs As Database
    Set dbs = CurrentDb
    
    ' Loop through the records from Table tblTasks to get MinStartDate & MaxEndDate for an ArticleID
    Dim strsqlTasks As String
    Dim rsta As DAO.Recordset
    Dim rstb As DAO.Recordset
    Dim TheArticleID As String
    Dim TheStartDate As Date
    Dim TheEndDate As Date
    Dim strsqlSelectTasks As String
    
    strsqlTasks = "SELECT ArticleID, Min(StartDate) AS MinOfStartDate, Max(EndDate) AS MaxOfEndDate FROM tblTasks GROUP BY tblTasks.ArticleID;"
    Set rsta = dbs.OpenRecordset(strsqlTasks, dbOpenDynaset)

    Do While Not rsta.EOF
        TheArticleID = rsta![ArticleID]
        TheStartDate = rsta![MinOfStartDate]
        TheEndDate = rsta![MaxOfEndDate]
        TheMaxNoOffDays = DateDiff("d", TheStartDate, TheEndDate) + 1
        TheNoOffDays = TheMaxNoOffDays
        
        'Debug.Print TheArticleID
        'Debug.Print "The Start Date : " & TheStartDate
        'Debug.Print "The End Date : " & TheEndDate
        'Debug.Print "The Max No Off Days : " & TheMaxNoOffDays
        'Debug.Print "The Current Total No Off Days : " & TheNoOffDays

        Do While TheStartDate <= TheEndDate
            ' i is the counter set to check if a Date is not there in all the records
            Dim i
            i = 0
            
            strsqlSelectTasks = "select ArticleID,StartDate,EndDate from tblTasks where ArticleID = " & TheArticleID & ";"
            Set rstb = dbs.OpenRecordset(strsqlSelectTasks, dbOpenDynaset)
            
            Dim strsqlSelectArticleTasksCount
            Dim rstc As DAO.Recordset
            Dim TheNoOffRecords
            
            strsqlSelectArticleTasksCount = "select Count(*) as TheNoOffTasksPerArticle from tblTasks where ArticleID = " & TheArticleID & ";"
            Set rstc = dbs.OpenRecordset(strsqlSelectArticleTasksCount, dbOpenDynaset)
            
            TheNoOffRecords = rstc![TheNoOffTasksPerArticle]

            Do While Not rstb.EOF
                TaskStartDate = rstb![StartDate]
                TaskEndDate = rstb![EndDate]
                If ((TheStartDate >= TaskStartDate) And (TheStartDate <= TaskEndDate)) Then
                    Exit Do
                Else
                    'Build a test here, if the No of times Not Between the Dates is equal to the record count, then
                    ' subtract 1 later from TheNoOffDays
                    i = i + 1
                End If
            rstb.MoveNext
            Loop
            
            If i = TheNoOffRecords Then
                TheNoOffDays = TheNoOffDays - 1
            End If
            TheStartDate = DateAdd("d", 1, TheStartDate)
        Loop

        Debug.Print TheArticleID & " - " & TheNoOffDays
    rsta.MoveNext
    Loop
    
    MsgBox "Completed successfully."
End Function 

Have not thought about efficiency of present vis-a-vis previous currently?

Thanks

[DavidCunnah]

Thanks again recryan

The first example you posted worked well - there are actually a variety of different types of tasks, but these ones were the only ones which overlapped so I needed this algorithm to make sense of them.

I think you underestimate your VB skills - I haven't done any in 10 years and managed to pick up your code and run with it (my first dabble in macros in Access) so you must be onto something!

Thanks again!

[recyan]

Was hoping some one would trash both the suggested solutions & instead suggest a simpler alternative.
All the same, Thanks for your encouragement about the VBA.

Thanks