Situatie
Se intampla uneori sa avem nevoie sa aflam cate zile lucratoare se afla pe un interval de timp.
Pt aceast lucru am creat o functie ce primeste ca parametri doua date calendaristice si, pt acel interval de timp, intoarce numarul de zile lucratoare.
Se tine cont si de capetele intervalului.
Solutie
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— SELECT [dbo].[BD_fcnWorkingDays] (‘20100401′,’20100430’)
ALTER FUNCTION [dbo].[BD_fcnWorkingDays] (@StartDate DATETIME, @EndDate DATETIME)
RETURNS INT AS
BEGIN
DECLARE @Days INT
SELECT @Days = DATEDIFF(Day,@StartDate,@EndDate)
IF @Days>0 BEGIN
DECLARE @NonWDays INT, @CDate DATETIME
SELECT @NonWDays=0
SELECT @CDate=@StartDate
WHILE (@CDate<=@EndDate)
BEGIN
IF (DATEPART(weekday,@CDate) IN (2,3,4,5,6))
SELECT @NonWDays=@NonWDays+1
SELECT @CDate=DATEADD(DAY,1,@CDate)
END
SELECT @Days=@NonWDays
END
RETURN @Days
END
Leave A Comment?