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