This works but some of the others on the forum may be able to make it better.

Here is how the code works: First we declare and set our start and end date. The second part of the code is the cteTally and cteCalendarTable which i have included in the code so it is self contained if you all ready have a calendar table you do not need these 2 parts of the code. (If not ill have links at the bottom for all the resources so you can see how the code works.) the second part, ScheduleID, is to make up for the lack of an id we can use to self join the pattern table to get start and end dates. if your schedule table gets a slight redesign we do not need this either. The third part is the real meat of the code, we normalize the structure of the schedule table and get the start and end dates for each schedule as well as get the day of the week and whether the employee works as rows instead of columns.

the BETWEEN is to make sure we dont calculate this information for every single entry in the table and can be omitted if you only have a few entries. however if you have allot of entries this may speed things up quite a bit.

The final query joins the calendar table (which because both the tally and calendar table are CTE's i have limited the calendar table to only the dates between the start and end date, if you have a calendar table in your database you would limit the range of the query here as well with a where clause)

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

http://weblogs.sqlteam.com/jeffs/archive/2008/04/23/unpivot.aspx Information on UnPivot methods

http://www.sqlservercentral.com/articles/T-SQL/62867/ Tally Tables and what they are

http://www.sqlservercentral.com/scripts/Date/68389/ Calendar table script.

http://www.sqlservercentral.com/articles/Test-Driven+Development/71075/ One simple use of a calendar table.

EDIT:

Added link to calendar table creation script.