Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2016
    Posts
    3

    Unanswered: Merge continuous records - Please help

    Hi all,

    I have a table with dates of sickness for employees.

    However no 'one' record can cross over a month end boundry e.g. 25/01/2016 - 05/02/2016. They are recorded to the end of the month then a new record starts from 1st of the following month (this is not by choice this is due to the software we use).

    All the data below is being pulled out the software via SQL in a linked table...

    Example:
    If an employee had records from '01/01/2016 to 31/03/2016' and '01/05/2016 to 31/05/2016' the software would have records as follows:-
    EmployeeID | StartDate | EndDate | Working Days
    11111 | 01/01/2016 | 31/01/2016 | 21
    11111 | 01/02/2016 | 29/02/2016 | 21
    11111 | 01/03/2016 | 31/03/2016 | 23
    11111 | 01/05/2016 | 31/05/2016 | 22

    What I need is if the employee has a record with a START DATE that is the day after the END DATE of another record then join them together and add the days.

    I need it to show in a query as:-
    EmployeeID | StartDate | EndDate
    11111 | 01/01/2016 | 31/03/2016 | 65
    11111 | 01/05/2016 | 31/05/2016 | 22

    then move onto the next EmployeeID.

    This is so we can determine if someone has been on long term sick or not.

    Can anyone please help..please please please.

    Thanks in advance
    Chris

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Do you need to retain the original data?

    What you could do is run a pair of queries
    First one alters the endate where the enddate of one +1 is the startdate of another, use that other
    Second one deletes the inner row, the row whose startdate is later than the other with the sane end date
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2016
    Posts
    3
    Quote Originally Posted by healdem View Post
    Do you need to retain the original data?

    What you could do is run a pair of queries
    First one alters the endate where the enddate of one +1 is the startdate of another, use that other
    Second one deletes the inner row, the row whose startdate is later than the other with the sane end date
    Hi Healdem,

    This all needs doing outside of the linked SQL table as I only have Select / Read Only access to the data. Could i use VBA to Insert the data from the linked table into its own table?

    Appreciate your help.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is there a finite limit on the number of period rows there may be?

    if so then you coudl use the ideas here as the basis.
    if its open ended then I think you may well be best using a local table.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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