Generating working schedules

See more:

SQL-Server

T-SQL

Hi,


Hope someone can help me (a novice on T-SQL) 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.
...
...
Record 14: same as record 7 but day is 14 (= last day for this employee)

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!

P.S.
I just read that PARTITION BY does not have any effect when using CROSS APPLY because CROSS APPLY works on a row-to-row basis. Despite this I am still stuck ...