Generate working schedules

• michielbijnen

  Ten Centuries

  Points: 1308

  More actions

  May 6, 2014 at 10:06 am

  #290973

  Hi,

  Hope someone can help me with this:

  I would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.

  I have got 3 relevant tables:

  1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)

  2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)

  3. Table Z with resource data.

  An example to clarify (for fake employee 100):

  Table X:

  Resource: 100

  Starting date: 2012-03-01 (from this date the schedule will be effective)

  Daynumber: 4 (2012-03-01 was a Thursday)

  Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):

  Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30

  Record 2: same but daynumber is 2

  Record 3: same but daynumber is 3 etc.

  ...

  Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00

  Record 9: same as record 2 but daynumber is 9.

  etc.

  ...

  Record 14: same as record 7 but day is 14 (= last day)

  The weekend days show as 00:00 for the hours (same as day 8 in example)

  I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.

  I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...

  I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):

  2014-05-01 = day 4 (= actual daynumber)

  2014-05-02 = day 5

  2014-05-03 = day 6

  ...

  2014-05-10 = day 13

  2014-05-11 = day 14

  2014-05-12 = day 1

  2014-05-13 = day 2

  2014-05-14 = day 3

  ...

  2014-05-24 = day 13

  2014-05-25 = day 14

  2014-05-26 = day 1

  2014-05-27 = day 2

  ...

  2014-05-31 = day 6

  With this done I can compare the actual daynumber with the daynumber in Table Y.

  The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.

  I think I have to redo the whole exercise from scratch, but I wonder what the best way is to get this solved.

  I am stuck!

  Thanks!