RE: udf in computed column
From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 01/10/05
- Next message: R.D: "RE: limit records"
- Previous message: Otto Miros: "Yes/No insted of True/False"
- In reply to: CJR: "udf in computed column"
- Next in thread: Hugo Kornelis: "Re: udf in computed column"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 10 Jan 2005 02:57:03 -0800
Why create a computed column for this at all?
It's easy to do the computation in a query if you use a Calendar table:
http://www.google.co.uk/groups?hl=en&lr=&selm=mKOdnUP-T-uktNPdRVn-tw%40giganews.com
It's harder with your PublicHolidays table because it doesn't contain
weekends and because HolidayDate apparently isn't unique (it isn't declared
as such anyway).
--
David Portas
SQL Server MVP
--
"CJR" wrote:
>
> Can anyone please advise if there are any obvious problems with the
> udfGetWorkingDays
> function and its use in the computed column in the AbsenceDetails table?
>
> thanks
>
>
> IF EXISTS
>
> (
>
> SELECT *
>
> FROM dbo.sysobjects
>
> WHERE id = OBJECT_ID(N'[dbo].[EmployeeTypes]')
>
> AND OBJECTPROPERTY(id, N'IsUserTable') = 1
>
> )
>
> DROP TABLE [dbo].[EmployeeTypes]
>
> GO
>
> CREATE TABLE dbo.EmployeeTypes
>
> (
>
> EmployeeTypeId INT NOT NULL,
>
> Description VARCHAR(50),
>
> CONSTRAINT PK_EmployeeTypes PRIMARY KEY NONCLUSTERED (EmployeeTypeId)
>
> )
>
> GO
>
> INSERT dbo.EmployeeTypes(EmployeeTypeId,Description) VALUES(1,'Permanent')
>
> INSERT dbo.EmployeeTypes(EmployeeTypeId,Description) VALUES(2,'Fixed
> Contract')
>
> INSERT dbo.EmployeeTypes(EmployeeTypeId,Description) VALUES(3,'Temp
> Contract')
>
> GO
>
> IF EXISTS
>
> (
>
> SELECT *
>
> FROM dbo.sysobjects
>
> WHERE id = OBJECT_ID(N'[dbo].[Employees]')
>
> AND OBJECTPROPERTY(id, N'IsUserTable') = 1
>
> )
>
> DROP TABLE [dbo].[Employees]
>
> GO
>
> CREATE TABLE dbo.Employees
>
> (
>
> EmployeeId INT NOT NULL,
>
> EmployeeRef VARCHAR(20) NOT NULL,
>
> FirstName VARCHAR(50) NOT NULL,
>
> LastName VARCHAR(50) NOT NULL,
>
> StartDate DATETIME NULL,
>
> EmployeeType INT NOT NULL DEFAULT(1),
>
> ReportsTo INT NULL DEFAULT(NULL),
>
> SetString VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
>
> Depth INT NULL,
>
> Created DATETIME NOT NULL DEFAULT(GETDATE()),
>
> Modified DATETIME NOT NULL DEFAULT(GETDATE()),
>
> Deleted CHAR(1) NOT NULL DEFAULT('N'),
>
> CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED (EmployeeId) WITH
> FILLFACTOR=20,
>
> CONSTRAINT U_Employees_EmployeeRef UNIQUE NONCLUSTERED (EmployeeRef) WITH
> FILLFACTOR=20,
>
> CONSTRAINT FK_Employees_EmployeeTypes_EmployeeType FOREIGN KEY
> (EmployeeType) REFERENCES EmployeeTypes (EmployeeTypeId),
>
> CONSTRAINT FK_Employees_Employees_ReportsTo FOREIGN KEY (ReportsTo)
> REFERENCES Employees (EmployeeId),
>
> CONSTRAINT Ck_Employees_Deleted CHECK (Deleted IN('N','Y'))
>
> )
>
> CREATE CLUSTERED INDEX IX_SetString ON dbo.Employees(SetString) WITH
> FILLFACTOR=20
>
> CREATE NONCLUSTERED INDEX IX_Depth ON dbo.Employees(Depth) WITH
> FILLFACTOR=20
>
> GO
>
> 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
>
>
>
- Next message: R.D: "RE: limit records"
- Previous message: Otto Miros: "Yes/No insted of True/False"
- In reply to: CJR: "udf in computed column"
- Next in thread: Hugo Kornelis: "Re: udf in computed column"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
