USE tempdb;

IF OBJECT_ID('dbo.#Employee','U') IS NOT NULL
DROP TABLE dbo.#Employee
CREATE TABLE #Employee (
	Empcode VARCHAR(10) PRIMARY KEY,
	Firstname VARCHAR(24) NULL,
	Surname VARCHAR(24) NULL
)

IF OBJECT_ID('dbo.#Working_Pattern','U') IS NOT NULL
DROP TABLE dbo.#Working_Pattern
CREATE TABLE #Working_Pattern (
	StartDate DATETIME PRIMARY KEY,
	Employee VARCHAR(10) NOT NULL,
	Monday CHAR(1) NULL,
	Tuesday CHAR(1) NULL,
	Wednesday CHAR(1) NULL,
	Thursday CHAR(1) NULL,
	Friday CHAR(1) NULL,
	Saturday CHAR(1) NULL,
	Sunday CHAR(1) NULL
)

INSERT INTO #Employee (Empcode, Firstname, Surname)
SELECT '0001','John','Smith' UNION ALL
SELECT '0002','Dave','Roberts' 

INSERT INTO #Working_Pattern (StartDate, Employee, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)
SELECT '2012/01/01','0001','Y','Y','Y','Y','Y','N','N' UNION ALL
SELECT '2012/01/08','0001','N','Y','Y','Y','Y','Y','N' UNION ALL
SELECT '2012/01/22','0001','N','N','N','Y','Y','Y','N' UNION ALL
SELECT '2012/03/01','0001','Y','Y','Y','Y','Y','Y','N' UNION ALL
SELECT '2012/09/08','0001','N','Y','Y','N','Y','Y','N' UNION ALL
SELECT '2009/01/01','0002','Y','Y','Y','Y','N','N','N' 

SELECT *
FROM #Employee

SELECT *
FROM #Working_Pattern

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SELECT @StartDate = '2012-01-09', @EndDate = '2012-01-30'

;WITH cteTally AS (SELECT TOP (DATEDIFF(DD,@StartDate,@EndDate) + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
                     FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
               CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))b(N)
               CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))c(N)
               CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))d(N)),
                     
cteCalendarTable AS (SELECT DATEADD(DD,N,@StartDate) AS [Date], DATENAME(DW,DATEADD(DD,N,@StartDate)) AS WeekDay
                            FROM cteTally),

ScheduleID AS (SELECT ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY StartDate) AS ID, *
                 FROM #Working_Pattern),

ScheduleRange AS (SELECT a.StartDate AS StartDate, ISNULL(DATEADD(DD,-1,b.StartDate), DATEADD(DD,DATEDIFF(DD,0,GETDATE()),0)) AS EndDate, a.Employee,
                    CASE c.WeekDay
                    WHEN 'Monday' THEN a.Monday
                    WHEN 'Tuesday' THEN a.Tuesday
                    WHEN 'Wednesday' THEN a.Wednesday
                    WHEN 'Thursday' THEN a.Thursday
                    WHEN 'Friday' THEN a.Friday
                    WHEN 'Saturday' THEN a.Saturday
                    WHEN 'Sunday' THEN a.Sunday
                    END AS Schedule,
                    c.WeekDay
                    FROM ScheduleID a
                    LEFT JOIN ScheduleID b
                     ON a.ID = b.ID - 1
                    AND a.Employee = b.Employee
                  CROSS APPLY (VALUES ('Monday'), ('Tuesday'), ('Wednesday'), ('Thursday'), ('Friday'), ('Saturday'), ('Sunday'))c([WeekDay])
                  WHERE a.StartDate BETWEEN (SELECT TOP 1 StartDate
                                             FROM #Working_Pattern
                                            WHERE StartDate <= @StartDate
                                              AND Employee = a.Employee
                                            ORDER BY StartDate DESC)
                                      AND (SELECT TOP 1 StartDate
                                             FROM #Working_Pattern
                                            WHERE StartDate <= @EndDate
                                              AND Employee = a.Employee
                                             ORDER BY StartDate DESC))

SELECT a.Date, b.Employee, b.Schedule, b.WeekDay
  FROM cteCalendarTable a
 INNER JOIN ScheduleRange b 
    ON a.Date BETWEEN b.StartDate AND b.EndDate
   AND a.WeekDay = b.WeekDay
 ORDER BY b.Employee, a.Date

DATEADD(DD,DATEDIFF(DD,0,GETDATE()),3650))

DECLARE @StartDT DATETIME = '2010-01-01'
    ,@EndDT      DATETIME = '2012-12-31'

;WITH Tally (n) AS (
        SELECT TOP (DATEDIFF(day, @StartDT, @EndDT) + 1)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
        FROM (VALUES ($),($),($),($),($),($),($),($),($),($)) a(n1)
        CROSS JOIN (VALUES ($),($),($),($),($),($),($),($),($),($)) b(n1)
        CROSS JOIN (VALUES ($),($),($),($),($),($),($),($),($),($)) c(n1)),
    FirstPattern AS (
        SELECT StartDate, Employee
            ,Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
            ,Firstname, Surname
            ,n=ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY StartDate)
        FROM #Working_Pattern
        JOIN #Employee ON Empcode = Employee),
    WorkPatterns AS (
        SELECT StartDate
            ,Employee
            ,Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
            ,Firstname, Surname
        FROM (
            SELECT StartDate=CASE WHEN StartDate < @StartDT THEN @StartDT ELSE StartDate END
                ,Employee
                ,Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
                ,Firstname, Surname
            FROM FirstPattern
            WHERE n = 1
            UNION 
            SELECT StartDate
                ,Employee
                ,Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
                ,Firstname, Surname
            FROM FirstPattern
            WHERE StartDate >= @StartDT) a)
SELECT Empcode, Firstname, Surname
    ,[WorkDate]=DATEADD(day, n, @StartDT)
    ,WorkDay=DATENAME(weekday, DATEADD(day, n, @StartDT))
    ,Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
FROM #Employee a
CROSS APPLY Tally b 
CROSS APPLY (
    SELECT TOP 1 StartDate
        ,Employee
        ,Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
    FROM WorkPatterns
    WHERE Empcode = Employee AND StartDate <= DATEADD(day, n, @StartDT)
    ORDER BY StartDate) c 
WHERE SUBSTRING(Sunday+Monday+Tuesday+Wednesday+Thursday+Friday+Saturday
    ,DATEPART(weekday, DATEADD(day, n, @StartDT)), 1) = 'Y'