Holidays
From: CJR (chris.richmond_at_cjr-solutions.co.uk)
Date: 12/23/04
- Next message: Paul fpvt2: "Re: How to clear memory usage after executing queries ?"
- Previous message: Michael Per: "Re: Permissions in tempdb"
- Next in thread: Max: "Re: Holidays"
- Reply: Max: "Re: Holidays"
- Reply: Paul: "RE: Holidays"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 23 Dec 2004 16:36:21 -0000
I am designing an HR system and have the following solution for calculating
duration of working days for absence.
Please advise any obvious problems with the udfGetWorkingDays function and
its use in the computed column in the AbsenceDetails table.
IF EXISTS
(
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[HolidayDescriptions]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
DROP TABLE [dbo].[HolidayDescriptions]
GO
CREATE TABLE dbo.HolidayDescriptions
(
HolidayDescriptionId INT NOT NULL,
HolidayDescription VARCHAR(100) NOT NULL,
Created DATETIME NOT NULL DEFAULT(GETDATE()),
Modified DATETIME NOT NULL DEFAULT(GETDATE()),
Deleted CHAR(1) NOT NULL DEFAULT('N'),
CONSTRAINT PK_HolidayDescriptions PRIMARY KEY NONCLUSTERED
(HolidayDescriptionId) WITH FILLFACTOR=20,
CONSTRAINT U_HolidayDescriptions_HolidayDescription UNIQUE NONCLUSTERED
(HolidayDescription) WITH FILLFACTOR=20,
CONSTRAINT Ck_HolidayDescriptions_Deleted CHECK (Deleted IN('N','Y'))
)
GO
IF EXISTS
(
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[PublicHolidays]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
DROP TABLE [dbo].[PublicHolidays]
GO
CREATE TABLE dbo.PublicHolidays
(
PublicHolidayId INT NOT NULL,
HolidayDescriptionId INT NOT NULL,
HolidayDate DATETIME NOT NULL,
Created DATETIME NOT NULL DEFAULT(GETDATE()),
Modified DATETIME NOT NULL DEFAULT(GETDATE()),
Deleted CHAR(1) NOT NULL DEFAULT('N'),
CONSTRAINT PK_PublicHolidays PRIMARY KEY NONCLUSTERED (PublicHolidayId) WITH
FILLFACTOR=20,
CONSTRAINT FK_PublicHolidays_HolidayDescriptions_HolidayDescriptionId
FOREIGN KEY (HolidayDescriptionId) REFERENCES HolidayDescriptions
(HolidayDescriptionId),
CONSTRAINT Ck_PublicHolidays_Deleted CHECK (Deleted IN('N','Y'))
)
GO
IF EXISTS
(
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[udfGetWorkingDays]')
AND OBJECTPROPERTY(id, N'IsScalarFunction') = 1
)
DROP FUNCTION [dbo].[udfGetWorkingDays]
GO
CREATE FUNCTION udfGetWorkingDays (@d1 datetime, @d2 datetime, @d1Half
char(1)='N', @d2Half char(1)='N')
RETURNS FLOAT
AS
/************************************************************************
* Name: udfGetWorkingDays
* Author: Chris Richmond
* Date: 22/12/2004
* Purpose: Find elapsed working days between two dates
* Called by:
* Calls:
* Parameters: @d1 - Start date
* @d2 - End date
* @d1Half - 'Y' (to denote half day) or 'N'
* @d2Half - 'Y' (to denote half day) or 'N'
* Output Parameters: @ElapsedDays - elapsed working days
- or NULL if invalid values
- passed for @d1Half / @d2Half
*
*************************************************************************
* Audit History
*
* Date Author Description
*------------------------------------------------------------------------
* 22/12/2004 Chris Richmond Created
*************************************************************************/
BEGIN
--@d1Half and @d2Half must be either 'Y' or 'N'
--so, RETURN NULL if this is not the case
IF (@d1Half NOT IN ('Y','N') AND @d1Half IS NOT NULL) RETURN NULL
IF (@d2Half NOT IN ('Y','N') AND @d2Half IS NOT NULL) RETURN NULL
DECLARE @ElapsedDays FLOAT,
@AbsDiff INT,
@count INT,
@tempResult INT
DECLARE @tblDays TABLE(DateId int identity(1,1), AbsDate datetime)
SET @AbsDiff = (SELECT DATEDIFF(d,@d1,@d2))
SET @count = 0
WHILE @count <= @AbsDiff
BEGIN
INSERT @tblDays(AbsDate) VALUES(DATEADD(d,@count,@d1))
SET @count = (SELECT @count + 1)
END
DELETE @tbldays
WHERE (DATENAME(dw,AbsDate) IN('Saturday','Sunday'))
OR (AbsDate IN(SELECT HolidayDate FROM PublicHolidays))
SELECT @TempResult = COUNT(*) FROM @tblDays
SET @ElapsedDays = CONVERT(FLOAT, @TempResult)
IF (@d1Half='Y' AND EXISTS(SELECT DateId FROM @tblDays WHERE AbsDate = @d1))
SET @ElapsedDays = (SELECT @ElapsedDays - 0.5)
IF (@d2Half='Y' AND EXISTS(SELECT DateId FROM @tblDays WHERE AbsDate = @d2))
SET @ElapsedDays = (SELECT @ElapsedDays - 0.5)
RETURN @ElapsedDays
END
GO
IF EXISTS
(
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[AbsenceTypes]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
DROP TABLE [dbo].[AbsenceTypes]
GO
CREATE TABLE dbo.AbsenceTypes
(
AbsenceTypeId INT NOT NULL,
AbsenceType VARCHAR(100) NOT NULL,
Created DATETIME NOT NULL DEFAULT(GETDATE()),
Modified DATETIME NOT NULL DEFAULT(GETDATE()),
Deleted CHAR(1) NOT NULL DEFAULT('N'),
CONSTRAINT PK_AbsenceTypes PRIMARY KEY NONCLUSTERED (AbsenceTypeId) WITH
FILLFACTOR=20,
CONSTRAINT U_AbsenceTypes_AbsenceType UNIQUE NONCLUSTERED (AbsenceType) WITH
FILLFACTOR=20,
CONSTRAINT Ck_AbsenceTypes_Deleted CHECK (Deleted IN('N','Y'))
)
GO
IF EXISTS
(
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[AbsenceDetails]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
DROP TABLE [dbo].[AbsenceDetails]
GO
CREATE TABLE dbo.AbsenceDetails
(
AbsenceDetailId INT NOT NULL,
AbsenceTypeId INT NOT NULL,
EmployeeId INT NOT NULL,
StartDate DATETIME NOT NULL,
StartHalfDay CHAR(1) NOT NULL DEFAULT('N'),
EndDate DATETIME NOT NULL,
EndHalfDay CHAR(1) NOT NULL DEFAULT('N'),
AbsentWorkDays AS
dbo.udfGetWorkingDays(StartDate,EndDate,StartHalfDay,EndHalfDay),
Created DATETIME NOT NULL DEFAULT(GETDATE()),
Modified DATETIME NOT NULL DEFAULT(GETDATE()),
Deleted CHAR(1) NOT NULL DEFAULT('N'),
CONSTRAINT PK_AbsenceDetails PRIMARY KEY NONCLUSTERED (AbsenceDetailId) WITH
FILLFACTOR=20,
CONSTRAINT FK_AbsenceDetails_AbsenceTypes_AbsenceTypeId FOREIGN KEY
(AbsenceTypeId) REFERENCES AbsenceTypes (AbsenceTypeId),
CONSTRAINT FK_AbsenceDetails_Employees_EmployeeId FOREIGN KEY (EmployeeId)
REFERENCES Employees (EmployeeId),
CONSTRAINT Ck_AbsenceDetails_StartHalfDay CHECK (StartHalfDay IN('N','Y')),
CONSTRAINT Ck_AbsenceDetails_EndHalfDay CHECK (EndHalfDay IN('N','Y')),
CONSTRAINT Ck_AbsenceDetails_Deleted CHECK (Deleted IN('N','Y'))
)
GO
Many thanks for any advise
Chris
- Next message: Paul fpvt2: "Re: How to clear memory usage after executing queries ?"
- Previous message: Michael Per: "Re: Permissions in tempdb"
- Next in thread: Max: "Re: Holidays"
- Reply: Max: "Re: Holidays"
- Reply: Paul: "RE: Holidays"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
