Click here to Skip to main content
15,793,624 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

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!


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 ...
chaau 7-May-14 18:50pm    
It would be great if you show us your existing query
Sunasara Imdadhusen 27-May-14 7:11am    
Have you written code for the same

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900