Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2007
    Location
    Ireland
    Posts
    13

    Unanswered: Consecutive days

    Hi,
    I am stumped as I do not understand how to get a query the will return groupings of consecutive days.

    I have to report on the number of consecutive days that a machine operator operates a machine. Does he/she work in a 2 consecutive day grouping, a 3 day grouping, a 4 day grouping or any other groupings of consecutive days and how many of these groupings occur for the operator.

    I reckon that if I can figure how to the report the groupings of consecutive days it is just a matter of using count() for each of the groupings.

    I am using MS Access 2000 and just in the learning curve at the moment. Is it possible to do this in SQL. Any pointers would be really be appreciated.

    Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends on your data, the way its laid out
    but you should be able to do it.

    the main issue I can see is waht compromises a working day, how do yu treate statutory holidays

    but Id want to see your table design/data before committing
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2007
    Location
    Ireland
    Posts
    13
    Hi healdem,
    Thank you for the reply.
    I have attached a .jpg of the relationship layout. Is this what you seek?

    It is a simple db, I am having trouble getting my head around the consecutive days query using MS Access 2000.

    Z.
    Attached Thumbnails Attached Thumbnails relationship.jpg  

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Can you also show some sample output of exactly what you want the report to show?

    Can you define what consecutive means? Is a Friday on followed by Saturday, Sunday and Monday off, followed by a Tuesday on consecutive? Even if the Monday is a public holiday?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Aug 2007
    Location
    Ireland
    Posts
    13
    Hi StarTrekker,

    Sample details

    Period : 1/1/8 - 30/6/8
    Single day operations : 20
    two day operation ; 3 (6 days)
    3 day operations ; 3 (9 days).

    Total days 35
    Percentage of total working days ; [I will let the computer work this out later ]


    Saturday/Sunday not involved but Friday/Monday would be classed as two consecutive days. Also with bank holidays Friday/Tuesday would also be considered as consecutive days.

    Z.

  6. #6
    Join Date
    Aug 2007
    Location
    Ireland
    Posts
    13
    Hi,
    Anyone any pointers to a possible solution, please?

    Z.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    All I can suggest at this point is that you're going to need to write a VBA routine to go through the data one record at a time and deduce what you need. But you will still have to have a list of holidays to work with.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you had a jobid associated with the machines use it'd pretty easy
    forgetting the problem of trying to identify consecutive dates how would you know then a piece of plant has been on the same site/job. ie in your data what is it that identifies the machinery in point x

    what is the target for this problems.. a form or a report?

    it would have been easier had you had a "on hire date" and a "off hire date" associated with a specific contract or job
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2007
    Location
    Ireland
    Posts
    13

    Thumbs up

    Thanks guys for coming back with suggestions.

    I have to write a report identifying a person operating over consecutive days, the machine is really of no consequence.

    I need to confine this to SQL really, I intend this project to be web based later. For now I just need to identify problem areas.

    I cannot find/understand SQL source code that allows me to manipulate the date data. I was thinking along the lines of (not SQL type code)
    for each person
    Check date
    Check next date
    if sequential
    group together
    Next date
    if not sequential Next person

    Nothing fancy, Person A 3 occurrences x 2Days, 2 occurrences x 3Days - something like that.

    Z.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Perhaps if you join the table to itself based on the preceeding row..
    Something like
    Code:
    FROM   my_table t1
     LEFT
      JOIN my_table t2
        ON t1.employee_id = t2.employee_id
       AND t1.some_date = DateAdd("dd", -1, t2.some_date)
    Haven't thought this through too much, but may be an avenue worth exploring.
    George
    Home | Blog

  11. #11
    Join Date
    Aug 2007
    Location
    Ireland
    Posts
    13
    Hi georgev,
    Many thanks for the pointer, sent me on direction that I did not know about earlier.

    I am not forgetting this project. The following code is working for me, I am now trying to proof code to return three consecutive days. A UNION is returning results but I am still trying refine this. I will post here upon resolution. A lot more reading and testing to go through.

    This a self join and does not take into consideration weekends and/or other types of holidays, just consecutively listed days.

    SELECT m.EmployeeNameId, m.workdate, c.workdate,
    FROM tblLoneWorkingDetails AS m INNER JOIN tblLoneWorkingDetails AS c ON (m.EmployeeNameId=c.EmployeeNameId) AND (m.WorkDate=DateAdd("d",-1,c.WorkDate));

    Thanks once again to everyone for the help.
    Z.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you wish to consider bank holidays etc then I think a calendar table is the only avenue left for you to explore.
    George
    Home | Blog

  13. #13
    Join Date
    Aug 2007
    Location
    Ireland
    Posts
    13
    Hi georgev,
    I like this idea but as someone that is bumping their way through this learning curve, is there a source or method for creating the calendar table. And as the dates/hols will change every year sould the table be created on the fly, and not a static table which would take up space in the database. Asking this question from a learning point of view. I am going to search through dbforums for information pertaining to dates/calendar and stuff.

    Thanks georgev for keeping this discussion active with new ideas.

    Z.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nah...
    first things firts
    I think what is needed is something which identifies a job, if the job spans more than one day.. jobsagoodun. ok you will need to identify non working days (weekends, bank holidays, the day after the All Ireland final....)
    no whether you continue as a daily hire out
    OR you have an on hire date & off hire date is up to you
    if the on hire date is different to the off hire date, by definition you have a machine that has been earning its keep for more than one day. it then comes down to identifying if the date spans a non working day. if it was me I'd cheat and book the plant out only on working days (but I'm like that).
    so if it was on the same job for the last two weeks I'd expect to see soemthing like
    JOB: 12324 onhire 22/09/08 offhire 26/09/08
    JOB: 12324 onhire 29/09/08 offhire 26/09/08
    JOB: 12324 onhire 06/10/08

    or you
    JOB: 12324 onhire 22/09/08 offhire 25/09/08
    and you have to work out what is a working day, whats a weekend, bankholiday....

    just my tuppeny haporth
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Aug 2007
    Location
    Ireland
    Posts
    13
    Hi All,
    happened upon a snippit of code on http://weblogs.sqlteam.com/jeffs/arc...two-dates.aspx

    The important issue for me is that this code is easy to understand and not convoluted. DateDiff has two intervals that I would like to highlight, they are 'w' and 'ww'.

    'w' counts weeks starting on Mondays whereas 'ww' counts weeks starting on Sundays. Checking through my results the 'ww' gives me the more accurate results.

    That said I think that I will have to set up a table to record bank holidays. Once this table is in place (has to be automated) I can add something like the suggestion on http://weblogs.sqlteam.com/jeffs/arc...two-dates.aspx

    My code as it stands so far...

    SELECT s.ID, s.[Estimated Completion Date], s.[Completion Date], DateDiff("d",s.[Estimated Completion Date],s.[Completion Date]) - DateDiff("ww",s.[Estimated Completion Date],s.[Completion Date])*2 AS [Estimate Overshoot in days]
    FROM [Structure Authorisation Form] AS s
    WHERE (((s.[Completion Date]) Is Not Null) AND ((DatePart("yyyy",[s].[Estimated Completion Date]))=2008))

    I will change [Estimated Completion Date]))=2008 to use DatePart or somthing like that.

    I am not working full time on this hence the reason for the long delays in the replies. But I will get this finished. So please have patience.

    Z.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •