Number weeks of work

  • Number weeks of work

    Hi there.

    I should check that the the number weeks of work for the five names is the same for all... that is, there can not be a person who works more or less of other...

    I create a table `tbl_dates` where the primary key is the date and another field used for the holiday.

    How do distribute equally these five names for the working weeks in the year?

    Can you help me? Any suggestions?

    thank you in advance

    -- ----------------------------

    -- Table structure for [dbo].[tbl_dates]

    -- ----------------------------

    DROP TABLE [dbo].[tbl_dates]

    GO

    CREATE TABLE [dbo].[tbl_dates] (

    [Dates] date NOT NULL ,

    [Holidays] date NULL ,

    [DayDates] varchar(20) NULL ,

    [DayHolidays] varchar(20) NULL

    )

    GO

    -- ----------------------------

    -- Records of tbl_dates

    -- ----------------------------

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-02', N'2013-01-01', N'mercoledì', N'martedì');

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-03', N'2013-04-01', N'giovedì', N'lunedì');

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-04', N'2013-04-25', N'venerdì', N'giovedì');

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-07', N'2013-05-01', N'lunedì', N'mercoledì');

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-08', N'2013-08-15', N'martedì', N'giovedì');

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-09', N'2013-11-01', N'mercoledì', N'venerdì');

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-10', N'2013-12-25', N'giovedì', N'mercoledì');

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-11', N'2013-12-26', N'venerdì', N'giovedì');

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-14', N'2013-12-31', N'lunedì', N'martedì');

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-15', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-16', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-17', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-18', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-21', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-22', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-23', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-24', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-25', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-28', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-29', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-30', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-01-31', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-01', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-04', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-05', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-06', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-07', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-08', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-11', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-12', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-13', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-14', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-15', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-18', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-19', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-20', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-21', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-22', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-25', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-26', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-27', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-02-28', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-01', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-04', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-05', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-06', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-07', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-08', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-11', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-12', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-13', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-14', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-15', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-18', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-19', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-20', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-21', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-22', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-25', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-26', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-27', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-28', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-03-29', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-02', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-03', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-04', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-05', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-08', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-09', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-10', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-11', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-12', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-15', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-16', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-17', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-18', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-19', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-22', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-23', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-24', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-26', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-29', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-04-30', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-02', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-03', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-06', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-07', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-08', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-09', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-10', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-13', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-14', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-15', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-16', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-17', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-20', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-21', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-22', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-23', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-24', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-27', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-28', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-29', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-30', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-05-31', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-03', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-04', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-05', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-06', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-07', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-10', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-11', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-12', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-13', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-14', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-17', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-18', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-19', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-20', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-21', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-24', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-25', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-26', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-27', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-06-28', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-01', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-02', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-03', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-04', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-05', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-08', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-09', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-10', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-11', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-12', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-15', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-16', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-17', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-18', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-19', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-22', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-23', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-24', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-25', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-26', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-29', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-30', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-07-31', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-01', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-02', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-05', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-06', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-07', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-08', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-09', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-12', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-13', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-14', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-16', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-19', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-20', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-21', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-22', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-23', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-26', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-27', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-28', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-29', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-08-30', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-02', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-03', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-04', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-05', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-06', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-09', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-10', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-11', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-12', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-13', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-16', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-17', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-18', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-19', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-20', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-23', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-24', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-25', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-26', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-27', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-09-30', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-01', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-02', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-03', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-04', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-07', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-08', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-09', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-10', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-11', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-14', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-15', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-16', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-17', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-18', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-21', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-22', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-23', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-24', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-25', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-28', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-29', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-30', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-10-31', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-04', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-05', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-06', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-07', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-08', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-11', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-12', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-13', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-14', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-15', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-18', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-19', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-20', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-21', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-22', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-25', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-26', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-27', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-28', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-11-29', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-02', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-03', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-04', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-05', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-06', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-09', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-10', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-11', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-12', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-13', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-16', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-17', null, N'martedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-18', null, N'mercoledì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-19', null, N'giovedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-20', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-23', null, N'lunedì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-27', null, N'venerdì', null);

    GO

    INSERT INTO [dbo].[tbl_dates] ([Dates], [Holidays], [DayDates], [DayHolidays]) VALUES (N'2013-12-30', null, N'lunedì', null);

    GO

    -- ----------------------------

    -- Indexes structure for table tbl_dates

    -- ----------------------------

    -- ----------------------------

    -- Primary Key structure for table [dbo].[tbl_dates]

    -- ----------------------------

    ALTER TABLE [dbo].[tbl_dates] ADD PRIMARY KEY ([Dates])

    GO

    -- ----------------------------

    -- Table structure for [dbo].[tbl_names]

    -- ----------------------------

    DROP TABLE [dbo].[tbl_names]

    GO

    CREATE TABLE [dbo].[tbl_names] (

    [names] varchar(50) NULL ,

    [id] int NOT NULL IDENTITY(1,1)

    )

    GO

    DBCC CHECKIDENT(N'[dbo].[tbl_names]', RESEED, 5)

    GO

    -- ----------------------------

    -- Records of tbl_names

    -- ----------------------------

    SET IDENTITY_INSERT [dbo].[tbl_names] ON

    GO

    INSERT INTO [dbo].[tbl_names] ([names], [id]) VALUES (N'George', N'1');

    GO

    INSERT INTO [dbo].[tbl_names] ([names], [id]) VALUES (N'Laura', N'2');

    GO

    INSERT INTO [dbo].[tbl_names] ([names], [id]) VALUES (N'Martin', N'3');

    GO

    INSERT INTO [dbo].[tbl_names] ([names], [id]) VALUES (N'Arnold', N'4');

    GO

    INSERT INTO [dbo].[tbl_names] ([names], [id]) VALUES (N'Betty', N'5');

    GO

    SET IDENTITY_INSERT [dbo].[tbl_names] OFF

    GO

    -- ----------------------------

    -- Indexes structure for table tbl_names

    -- ----------------------------

    -- ----------------------------

    -- Primary Key structure for table [dbo].[tbl_names]

    -- ----------------------------

    ALTER TABLE [dbo].[tbl_names] ADD PRIMARY KEY ([id])

    GO

  • cms9651 (12/23/2012)


    Number weeks of work

    Hi there.

    I should check that the the number weeks of work for the five names is the same for all... that is, there can not be a person who works more or less of other...

    I create a table `tbl_dates` where the primary key is the date and another field used for the holiday.

    How do distribute equally these five names for the working weeks in the year?

    Can you help me? Any suggestions?

    thank you in advance

    Not enough info. Are these 5 folks supposed to work a full week while the others are off? Are these 5 folks supposed to work just one day and then not again for 5 business days? O ????

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • thank you for reply.

    one of five people have to work a full week, while others are off.

    everyone has to work the same number of weeks for year.

    know what I mean?

  • cms9651 (12/24/2012)


    thank you for reply.

    one of five people have to work a full week, while others are off.

    everyone has to work the same number of weeks for year.

    know what I mean?

    is this some form of "scheduling"?

    last time I counted there were 52 weeks in a year

    divided by 5 workers = 10.4 weeks each

    so if I read your request correctly ...I dont see how everyone can work same number of weeks in a year?

    can be done by number of days (ie 73 per worker) except in a leap year 🙂

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • J Livingston SQL (12/24/2012)


    cms9651 (12/24/2012)


    thank you for reply.

    one of five people have to work a full week, while others are off.

    everyone has to work the same number of weeks for year.

    know what I mean?

    is this some form of "scheduling"?

    last time I counted there were 52 weeks in a year

    divided by 5 workers = 10.4 weeks each

    so if I read your request correctly ...I dont see how everyone can work same number of weeks in a year?

    can be done by number of days (ie 73 per worker) except in a leap year 🙂

    Ok I understand your explication... but in my case I counted 50 weeks in a year

    divided by 5 workers = 10 weeks each because for two week for year is vacation 🙂

  • cms9651 (12/24/2012)


    thank you for reply.

    one of five people have to work a full week, while others are off.

    everyone has to work the same number of weeks for year.

    know what I mean?

    Sure... so what do you want to do about 3 and 4 day work weeks? Do those count as a full week?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yes, Sir.

    In my case I counted 50 weeks in a year

    divided by 5 workers = 10 weeks each because for two week for year is vacation.

    1. I need everyone to work an equal number of weeks (10 weeks for year);

    2. Every week work is five consecutive working days;

    e.g.

    George working in the week 07, 08, 09, 10 and 11 january 2013

    the next turn of work for George is 11, 12, 13, 14 and 15 february 2013 because previous week:

    a. Laura 14-15-16-17-18 january 2013

    b. Martin 21-22-23-24-25 january 2013

    c. Arnold 28-29-30-31 january 2013 and 01 february 2013

    d. Betty 04-05-06-07-08 february 2013

    In my case employees are off every weekend.

  • 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.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • 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

  • 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

    Have you tried adding a WHERE clause before ORDER BY that excludes holidays?


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (12/25/2012)

    Have you tried adding a WHERE clause before ORDER BY that excludes holidays?

    Yes, I tried and this is the output wrong you see:

    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

    WHERE holidays IS NULL

    ORDER BY Dates

    DatesDayDatesnames

    2013-01-15martedìLaura

    2013-01-16mercoledìMartin

    2013-01-17giovedìMartin

    2013-01-18venerdìMartin

    2013-01-21lunedìMartin

    2013-01-22martedìMartin

    2013-01-23mercoledìArnold

    2013-01-24giovedìArnold

    2013-01-25venerdìArnold

    2013-01-28lunedìArnold

    2013-01-29martedìArnold

    2013-01-30mercoledìBetty

    2013-01-31giovedìBetty

  • I'll be honest with you. My problem is that it is difficult for me to interpret what is right, when all you can tell me is that the result set is "wrong." Explaining why it is wrong probably won't help me either.

    If you can, what you should do is list out the first 20 dates and who they are assigned to, when the result set is correct.

    My strength is being able to interpret input and expected output, and then supplying an appropriate transformation.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • You have right, I'm sorry... 🙂

    The output of last query -adding a WHERE clause before ORDER BY that excludes holidays- is wrong because excludes the first nine valid dates of January.

    This is because the date table is structured as:

    DatesHolidaysDayDatesDayHolidays

    2013-01-022013-01-01mercoledìmartedì

    2013-01-032013-04-01giovedìlunedì

    2013-01-042013-04-25venerdìgiovedì

    2013-01-072013-05-01lunedìmercoledì

    2013-01-082013-08-15martedìgiovedì

    2013-01-092013-11-01mercoledìvenerdì

    2013-01-102013-12-25giovedìmercoledì

    2013-01-112013-12-26venerdìgiovedì

    2013-01-142013-12-31lunedìmartedì

    Adding a WHERE clause before ORDER BY -WHERE holidays IS NULL- this is the output:

    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

    WHERE holidays IS NULL

    ORDER BY Dates

    DatesDayDatesnames

    2013-01-15martedìLaura

    2013-01-16mercoledìMartin

    2013-01-17giovedìMartin

    2013-01-18venerdìMartin

    2013-01-21lunedìMartin

    2013-01-22martedìMartin

    2013-01-23mercoledìArnold

    2013-01-24giovedìArnold

    2013-01-25venerdìArnold

    2013-01-28lunedìArnold

    2013-01-29martedìArnold

    2013-01-30mercoledìBetty

    2013-01-31giovedìBetty

    I need this other output:

    DatesDayDatesnames

    2013-01-02mercoledìGeorge

    2013-01-03giovedìGeorge

    2013-01-04venerdìGeorge

    2013-01-07lunedìLaura

    2013-01-08martedìLaura

    Is a three day week and in this case count as a complete week for simplified every Monday I need change the name of employee.

    Can you help me?

    Merry Christmas

  • I see what the problem is. I didn't really look at what you'd stored in the Holidays column. IMHO - that is all wrong. You should be putting that into a separate table. As it is, at best it is a waste of space.

    Be that as it may, instead of construcing the JOIN criteria using ROW_NUMBER(), you can do it with DATEPART.

    DECLARE @CountOfEmp INT = (SELECT COUNT(*) FROM [dbo].[tbl_names])

    ;WITH GroupsOfWeeks AS (

    SELECT *

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

    ,n=1+(DATEPART(week, Dates)-1)%@CountOfEmp

    FROM [dbo].[tbl_dates]

    )

    SELECT Dates, Holidays, DayDates, DayHolidays, names

    FROM GroupsOfWeeks

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

    WHERE Dates NOT IN (

    SELECT Holidays

    FROM [dbo].[tbl_dates]

    WHERE Holidays IS NOT NULL)

    ORDER BY Dates

    I did of course ask for the first 20 rows and you only provided the first 5 or so. However this result matches those rows and I think will work for the rest.

    One question though. Why didn't you include 2013-01-01 as the first row in your dates table? That is a weekday, so to be consistent I think you probably should have, even though I disagree with the overall design as I stated above. This would mean you'd need to shift your holidays back one row. Or not, since my above query doesn't really care where they appear in the list.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • CELKO (12/25/2012)


    I forget the part of about having a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7) day of the week.

    You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.

    WHERE sale_day LIKE '2012W26-[67]'

    I love it! But I must've missed something in my SQL studies. What is that data type?

    Merry Christmas to you Joe!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply