Posts

Showing posts from February, 2010

SQL SERVER Employee Scheduling System

Determining day based on the start time and end time can be trick especially in sql.Scenario like this is encountered when working on a employee or any other scheduling system.Sample below takes current time and gives back the employees that are available according to the employeeschedule table.Code is self explanatory and is why it is minimally commented.

If you need a nice formatted sql,get it from here.
http://code.google.com/p/sql-scheduling-availability-system/downloads/list


IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'Employees')
DROP TABLE [Employees]

GO

CREATE TABLE [dbo].[Employees] (
[EmpID] [INT],
[FirstName] [VARCHAR](50),
[LastName] [VARCHAR](50),
[PhoneNumber] [VARCHAR](15))

GO

IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'EmployeeSchedule')
DROP TABLE [EmployeeSchedule]

GO

CREATE TABLE [dbo].[EmployeeSchedule] (
[EmployeeScheduleID] [INT],
[WeekID] [INT],
[DayID] [INT],
[EmpID] [INT],
[StartTime] [SMALLDATETIME],
[EndTIme] [SMAL…