|
Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software
to create a Holiday table and a scalar function for workdays calculation and test it.
USE AdventureWorks
GO
CREATE TABLE dbo.Holiday (
CountryCode CHAR(2) NOT NULL,
[HolidayDate] [SMALLDATETIME] NOT NULL,
CONSTRAINT [PK_holidayDate] PRIMARY KEY
CLUSTERED ( [CountryCode],[HolidayDate] ))
GO
INSERT dbo.Holiday
VALUES('US','2008-01-01')
INSERT dbo.Holiday
VALUES('US','2008-02-18')
INSERT dbo.Holiday
VALUES('US','2008-05-26')
INSERT dbo.Holiday
VALUES('US','2008-07-04')
INSERT dbo.Holiday
VALUES('US','2008-09-01')
INSERT dbo.Holiday
VALUES('US','2008-10-13')
INSERT dbo.Holiday
VALUES('US','2008-11-27')
INSERT dbo.Holiday
VALUES('US','2008-12-25')
GO
-- UDF - Scalar-valued
CREATE FUNCTION fnWorkDays
(@StartDate DATETIME,
@EndDate DATETIME)
RETURNS INT
AS
BEGIN
RETURN
(SELECT (DATEDIFF(dd,@StartDate,@EndDate) + 1) -
(DATEDIFF(wk,@StartDate,@EndDate) * 2) -
(CASE
WHEN DATENAME(dw,@EndDate) = 'Saturday' THEN 1
ELSE 0
END) - (CASE
WHEN DATENAME(dw,@StartDate) = 'Sunday' THEN 1
ELSE 0 END) - COUNT(* )
FROM dbo.Holiday
WHERE HolidayDate BETWEEN @StartDate AND @EndDate)
END
GO
DECLARE @StartDate DATETIME,
@EndDate DATETIME
SET @StartDate = getdate() - 200
SET @EndDate = getdate() - 100
SELECT WorkDays = dbo.fnWorkDays(@StartDate,@EndDate)
GO
/* WorkDays
71 */
DECLARE @StartDate DATETIME,
@EndDate DATETIME
SET @StartDate = getdate() + 100
SET @EndDate = getdate() + 400
SELECT WorkDays = dbo.fnWorkDays(@StartDate,@EndDate)
GO
/* WorkDays
215 */
Related article:
How do I determine a public holiday in Sql server?
|
