cms9651 (12/25/2012)

---

dwain.c (12/24/2012)

---

My first suggestion to you would be to remove the tbl_ from your table names as I find that convention totally annoying. You're not programming C or C# here, so there is no need to prefix the object type on the name. SSMS does a pretty good job of segregating objects and doesn't get confused on that point.

Perhaps this query will get you started:

;WITH GroupsOfWeeks AS (

SELECT *

,n=1+(((ROW_NUMBER() OVER (ORDER BY Dates)-1)/5) % 5)

FROM [dbo].[tbl_dates]

)

SELECT Dates, Holidays, DayDates, DayHolidays, names

FROM GroupsOfWeeks

INNER JOIN [dbo].[tbl_names] ON n = id

ORDER BY Dates

It distributes the names across alternating weeks, with each person assigned to 5 consecutive days in your dates table. Since there are 251 rows in this table, George gets to work an extra day.

Thank you very much for your suggestion.

I tried your query and I have this output.

The problem is when a legal holiday occurs in the week you see the output:

First week of the year:

2013-01-01 holiday

2013-01-02

2013-01-03

2013-01-04

Is a three day week and in this case count as a complete week... I need this output it's possible?

DatesDayDatesnames

2013-01-02mercoledìGeorge

2013-01-03giovedìGeorge

2013-01-04venerdìGeorge

2013-01-07lunedìLaura

2013-01-08martedìLaura

Will the company then compensate economically for any difference of days worked.

Cheers.

WITH GroupsOfWeeks AS (

SELECT *

,n=1+(((ROW_NUMBER() OVER (ORDER BY Dates)-1)/5) % 5)

FROM [dbo].[dates]

)

SELECT Dates, DayDates, [names]

FROM GroupsOfWeeks

INNER JOIN [dbo].[names] ON n = id

ORDER BY Dates

DatesDayDatesnames

2013-01-02mercoledìGeorge

2013-01-03giovedìGeorge

2013-01-04venerdìGeorge

2013-01-07lunedìGeorge

2013-01-08martedìGeorge