Appointments/Scheduling Queries and Stored Proceedures

Building a scheduling component for an application. So need some queries and stored procedures for SQL Server 2008 R2. The specs attached is for the first phase of this series. If work is well done, expect more to follow to build on it. Project is dynamic in nature, so tablenames and columns names are stores in settings table where the can be retrieved based on ID. would like to check Resource Availability given a ServiceID as well as given desired start and end time, determine if resources are available or already in use. SP1 Given ServiceIDs = 2,3 RequestedStartDateTime RequestedEndDateTime Resource_Selection_Rule (optional): ‘EmployeesID = 10 and VehicleID = 101’ Return: True or False SP2: Resource Availability Given ResourceID RequestedStartDateTime RequestedEndDateTime Return: True or False SP workings: 1. Check General Availability 2. Check service Availability 3. Using Service ID, Check each resource general availability using table s_ServiceResourcesAvailability (eg vehicles only available in afternoons on MWF) 4. Using SeviceID’s (e.g. Consultation), determine the required Resources by looking up table s_Service_to_Resource. It might be just one service needing one or more resource, or Multiple services needing one or more resource. 5. Using the resource (e.g. employee) columnID, we check availability. In checking availability, we check content of columns FromColumnID and ToColumnID for each resource. Can use sp_getrelatedvalue to achieve. I attached some screen shots with tables Last page would be of specs would be done in a very similar way to existing SP that I would provide.