Automate scheduling of resources using requirements and availability

See more:

SQL

recursion

Using SQL server 2008

I need a recursive join to output records on the right side once for every day. Records on the left define the date, and are joined with the right by a range of matches.

Basically I'm trying to generate schedule recommendations based on resources who have availability that match requirements for the day.

I join the "requirements" to "preferences" to get every possible preference for every requirement of every day, and for every requirement, every preference that could possibly fulfill it gets joined.

The problem is that I don't want to repeat join on the right side within a given day. So I want to join a record on the right hand side, and not use that same record again until the next day.

Requirement Schema
------------

id, fkType, startDate, endDate, exception bit, monday bit, tuesday bit, wednesday bit, thursday bit, friday bit, saturday bit, sunday bit, weeklyRecur bit, monthlyRecur bit, yearlyRecur bit, recurInterval int

Preference Schema
-----------

id, fkResourceID, startDate, endDate, exception bit, monday bit, tuesday bit, wednesday bit, thursday bit, friday bit, saturday bit, sunday bit, weeklyRecur bit, monthlyRecur bit, yearlyRecur bit, recurInterval int

Preference-Type Line Schema
--------------------

fkType, fkPreference

Type Schema
-----

id, description

Fulfillment Schema
-----------

id, fkPrefernece, fkRequirement, fkType, fkResourceID, fulfillmentDate

The Views:

REQUIREMENTS AND PREFERENCES This is the main view so far

SELECT     TOP (100) PERCENT r.Date AS ShiftDate, r.shiftType, p.notes, SUM(CASE WHEN p.exception = 1 THEN 1 ELSE 0 END) AS exceptionCount, p.emp, r.id, 
                      t.type AS TypeName
FROM         dbo.SCHEDULE_REQUIREMENT_CALENDAR AS r LEFT OUTER JOIN
                      dbo.SCHEDULE_PREFERENECE_CALENDAR AS p ON r.Date = p.Date AND p.shiftPreference = r.shiftType LEFT OUTER JOIN
                      dbo.SCHEDULE_PREFERENCE_TYPE AS t ON t.id = r.shiftType
GROUP BY r.Date, r.shiftType, p.notes, p.notes, p.emp, r.id, t.type
ORDER BY ShiftDate

REQUIREMENTS

SELECT     TOP (100) PERCENT t.Date, a.id, a.shiftType, a.startDate, a.endDate, a.exception, a.monday, a.tuesday, a.wednesday, a.thursday, a.friday, a.saturday, a.sunday, 
                      a.recurInterval, a.notes
FROM         dbo.TimeDimension AS t LEFT OUTER JOIN
                      dbo.SCHEDULE_REQUIREMENT AS a ON (a.startDate <= t.Date AND (t.Date <= a.endDate OR
                      ISNULL(a.endDate, '') = '') OR
                      ISNULL(a.id, '') = '') AND (a.monday = 1 AND t.Day_Number_Of_Week = 2 OR
                      a.tuesday = 1 AND t.Day_Number_Of_Week = 3 OR
                      a.wednesday = 1 AND t.Day_Number_Of_Week = 4 OR
                      a.thursday = 1 AND t.Day_Number_Of_Week = 5 OR
                      a.friday = 1 AND t.Day_Number_Of_Week = 6 OR
                      a.saturday = 1 AND t.Day_Number_Of_Week = 7 OR
                      a.sunday = 1 AND t.Day_Number_Of_Week = 1) AND (a.recurInterval = 1 OR
                      ISNULL(a.recurInterval, '') = '' OR
                      a.recurInterval = 2 AND DATEDIFF(week, a.startDate, t.Date) % 2 = 0 AND a.recurInterval = 3 AND DATEDIFF(week, a.startDate, t.Date) % 3 = 0 AND 
                      a.recurInterval = 4 AND DATEDIFF(week, a.startDate, t.Date) % 4 = 0)
ORDER BY t.Date

PREFERENCES

SELECT     TOP (100) PERCENT t.Date, a.id, a.emp, a.shiftPreference, a.openToChange, a.startDate, a.endDate, a.exception, a.recurInterval, a.notes
FROM         dbo.TimeDimension AS t LEFT OUTER JOIN
                      dbo.SCHEDULE_PREFERENCE AS a ON (a.startDate <= t.Date AND (t.Date <= a.endDate OR
                      ISNULL(a.endDate, '') = '') OR
                      ISNULL(a.id, '') = '') AND (a.monday = 1 AND t.Day_Number_Of_Week = 2 OR
                      a.tuesday = 1 AND t.Day_Number_Of_Week = 3 OR
                      a.wednesday = 1 AND t.Day_Number_Of_Week = 4 OR
                      a.thursday = 1 AND t.Day_Number_Of_Week = 5 OR
                      a.friday = 1 AND t.Day_Number_Of_Week = 6 OR
                      a.saturday = 1 AND t.Day_Number_Of_Week = 7 OR
                      a.sunday = 1 AND t.Day_Number_Of_Week = 1) AND (a.recurInterval = 1 OR
                      ISNULL(a.recurInterval, '') = '' OR
                      a.recurInterval = 2 AND DATEDIFF(week, a.startDate, t.Date) % 2 = 0 AND a.recurInterval = 3 AND DATEDIFF(week, a.startDate, t.Date) % 3 = 0 AND 
                      a.recurInterval = 4 AND DATEDIFF(week, a.startDate, t.Date) % 4 = 0)
WHERE     (NOT EXISTS
                          (SELECT     1 AS Expr1
                            FROM          dbo.SCHEDULE_PREFERENCE AS x
                            WHERE      (emp = a.emp) AND (a.shiftPreference = shiftPreference) AND (startDate <= t.Date AND (t.Date <= endDate OR
                                                   ISNULL(endDate, '') = '') OR
                                                   ISNULL(id, '') = '') AND (monday = 1 AND t.Day_Number_Of_Week = 2 OR
                                                   tuesday = 1 AND t.Day_Number_Of_Week = 3 OR
                                                   wednesday = 1 AND t.Day_Number_Of_Week = 4 OR
                                                   thursday = 1 AND t.Day_Number_Of_Week = 5 OR
                                                   friday = 1 AND t.Day_Number_Of_Week = 6 OR
                                                   saturday = 1 AND t.Day_Number_Of_Week = 7 OR
                                                   sunday = 1 AND t.Day_Number_Of_Week = 1) AND (recurInterval = 1 OR
                                                   ISNULL(recurInterval, '') = '' OR
                                                   recurInterval = 2 AND DATEDIFF(week, startDate, t.Date) % 2 = 0 AND recurInterval = 3 AND DATEDIFF(week, startDate, t.Date) % 3 = 0 AND 
                                                   recurInterval = 4 AND DATEDIFF(week, startDate, t.Date) % 4 = 0) AND (exception = 1)))
ORDER BY t.Date