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.

Habilidades: SQL

Veja mais: stored, stored procedures, scheduling, queries, Project Scheduling , Appointments, appointments s, building project sql, table queries, queries sql, project sp1, sql server scheduling application, SQL Server Stored Procedures, sql appointments, sql appointments tables, sql multiple tables, employee time sql, time table scheduling, time table component, tables stored sql

ID do Projeto: #4218394