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] [SMALLDATETIME] ,)

GO

--Create Test Employees
INSERT INTO [dbo].[Employees]
([EmpID],
[FirstName],
[LastName],
[PhoneNumber])
VALUES (1,
'Tester1',
'A1',
'111-111-1111')

GO

INSERT INTO [dbo].[Employees]
([EmpID],
[FirstName],
[LastName],
[PhoneNumber])
VALUES (2,
'Tester2',
'A2',
'112-112-1112')

GO

--Some Test scheduling data
--for the current week all days
INSERT INTO [dbo].[EmployeeSchedule]
([EmployeeScheduleID],
[WeekID],
[DayID],
[EmpID],
[StartTime],
[EndTIme])
VALUES (1,
Datepart(wk,Getdate()),
1,
1,
'1900-01-01 05:00:00',
'1900-01-01 17:00:00')

GO

INSERT INTO [dbo].[EmployeeSchedule]
([EmployeeScheduleID],
[WeekID],
[DayID],
[EmpID],
[StartTime],
[EndTIme])
VALUES (1,
Datepart(wk,Getdate()),
2,
1,
'1900-01-01 12:30:00',
'1900-01-01 00:30:00')

GO

INSERT INTO [dbo].[EmployeeSchedule]
([EmployeeScheduleID],
[WeekID],
[DayID],
[EmpID],
[StartTime],
[EndTIme])
VALUES (1,
Datepart(wk,Getdate()),
3,
1,
'1900-01-01 18:00:00',
'1900-01-01 03:00:00')

GO

INSERT INTO [dbo].[EmployeeSchedule]
([EmployeeScheduleID],
[WeekID],
[DayID],
[EmpID],
[StartTime],
[EndTIme])
VALUES (1,
Datepart(wk,Getdate()),
4,
1,
'1900-01-01 12:00:00',
'1900-01-01 8:00:00')

GO

INSERT INTO [dbo].[EmployeeSchedule]
([EmployeeScheduleID],
[WeekID],
[DayID],
[EmpID],
[StartTime],
[EndTIme])
VALUES (1,
Datepart(wk,Getdate()),
5,
1,
'1900-01-01 06:00:00',
'1900-01-01 15:00:00')

GO

INSERT INTO [dbo].[EmployeeSchedule]
([EmployeeScheduleID],
[WeekID],
[DayID],
[EmpID],
[StartTime],
[EndTIme])
VALUES (1,
Datepart(wk,Getdate()),
6,
1,
'1900-01-01 21:00:00',
'1900-01-01 05:00:00')

GO

INSERT INTO [dbo].[EmployeeSchedule]
([EmployeeScheduleID],
[WeekID],
[DayID],
[EmpID],
[StartTime],
[EndTIme])
VALUES (1,
Datepart(wk,Getdate()),
7,
1,
'1900-01-01 14:00:00',
'1900-01-01 21:00:00')

GO

DECLARE @weekday AS INTEGER

DECLARE @weekid AS INTEGER

DECLARE @InputTime DATETIME

SET @weekday = Datepart(dw,Getdate())

SET @weekid = Datepart(wk,Getdate())

SET @InputTime = '1900-01-02 ' + Convert(VARCHAR(10),Getdate(),108)

DECLARE @Schedule TABLE(
EMPID INT,
TodaysStart DATETIME,
TodaysEnd DATETIME,
YesterdaysStart DATETIME,
YesterdaysEnd DATETIME,
TodaysStartMins INT,
TodaysEndMins INT,
YesterdaysStartMins INT,
YesterdaysEndMins INT
)


--Get the start and end date time of today and yesterday
INSERT INTO @Schedule
(EMPID,
TodaysStart,
TodaysEnd,
YesterdaysStart,
YesterdaysEnd)
SELECT Isnull(todaysschedule.currempid,yesterdaysschedule.prevempid) AS empid,
Max(todaysschedule.todaysstart),
Max(todaysschedule.todaysend),
Max(yesterdaysschedule.yesterdaysstart),
Max(yesterdaysschedule.yesterdaysend)
FROM (SELECT empid AS prevempid,
starttime AS yesterdaysstart,
endtime AS yesterdaysend
FROM dbo.employeeschedule
WHERE WeekId = @weekid
AND dayid = CASE @weekday
WHEN 7
THEN 1
ELSE @weekday - 1
END
UNION
SELECT NULL,
NULL,
NULL) yesterdaysschedule,
(SELECT empid AS currempid,
starttime AS todaysstart,
endtime AS todaysend
FROM dbo.employeeschedule
WHERE WeekId = @weekid
AND dayid = @weekday
UNION
SELECT NULL,
NULL,
NULL) todaysschedule
WHERE Isnull(todaysschedule.currempid,yesterdaysschedule.prevempid) IS NOT NULL
GROUP BY Isnull(todaysschedule.currempid,yesterdaysschedule.prevempid)


--Increment today by 1 day
UPDATE @Schedule
SET TodaysStart = '1900-01-02 ' + Convert(VARCHAR(10),TodaysStart,108),
TodaysEnd = '1900-01-02 ' + Convert(VARCHAR(10),TodaysEnd,108),
YesterdaysStart = '1900-01-01 ' + Convert(VARCHAR(10),YesterdaysStart,108),
YesterdaysEnd = '1900-01-01 ' + Convert(VARCHAR(10),YesterdaysEnd,108)

--Populate minute columns which are used for determining if the end time falls into next day
UPDATE @Schedule
SET TodaysStartMins = Datepart(HOUR,TodaysStart) * 60 + Datepart(MINUTE,TodaysStart),
TodaysEndMins = Datepart(HOUR,TodaysEnd) * 60 + Datepart(MINUTE,TodaysEnd),
YesterdaysStartMins = Datepart(HOUR,YesterdaysStart) * 60 + Datepart(MINUTE,YesterdaysStart),
YesterdaysEndMins = Datepart(HOUR,YesterdaysEnd) * 60 + Datepart(MINUTE,YesterdaysEnd)

--This is the heart of all the things we did so far
--if start time < end time and end time is before 12 am
--then it is today otherwise tommorow so adding a day to the end time.
SELECT e.EmpID,
FirstName,
LastName,
TodaysStart AS TodaysStartTime,
CASE
WHEN ((TodaysStartMins < TodaysEndMins)
AND (TodaysEndMins < 1440))
THEN TodaysEnd
ELSE Dateadd(d,1,TodaysEnd)
END AS TodaysEndTime,
YesterdaysStart AS YesterdaysStartTime,
CASE
WHEN (YesterdaysStartMins < YesterdaysEndMins)
AND (YesterdaysEndMins < 1440)
THEN YesterdaysEnd
ELSE Dateadd(d,1,YesterdaysEnd)
END AS YesterdaysEndTime
FROM @Schedule s
INNER JOIN dbo.Employees e
ON e.EMPID = s.EMPID
WHERE (@InputTime BETWEEN YesterdaysStart AND CASE
WHEN ((YesterdaysStartMins < YesterdaysEndMins)
AND (YesterdaysEndMins < 1440))
THEN YesterdaysEnd
ELSE Dateadd(d,1,YesterdaysEnd)
END)
OR (@InputTime BETWEEN TodaysStart AND CASE
WHEN ((TodaysStartMins < TodaysEndMins)
AND (TodaysEndMins < 1440))
THEN TodaysEnd
ELSE Dateadd(d,1,TodaysEnd)
END)

GO

--Author : Prashanth Vakati
--License : Freeware/OpenSource
--Feedback /Suggestions Welcome.

Comments

Popular posts from this blog

Duplicate Folders Freeware - Not Duplicate Files

SSRS Compile Errors

Free SQL Server Management Studio Addin/Plugin/External Tool to get object information quickly