T-SQL obtenir le nombre de jours ouvrés entre 2 dates

Je veux calculer le nombre de jours ouvrés entre 2 dates. Par exemple, si je veux calculer le nombre de jours ouvrables entre 2013-01-10 et 2013-01-15, le résultat doit être de 3 jours de travail (je ne prends pas en compte le dernier jour dans cet intervalle et je soustrais les samedis et dimanches). J'ai le code suivant qui fonctionne dans la plupart des cas, à l'exception de celui dans mon exemple.

  SELECT (DATEDIFF(day, '2013-01-10', '2013-01-15')) 
    - (CASE WHEN DATENAME(weekday, '2013-01-10') = 'Sunday' THEN 1 ELSE 0 END)
    - (CASE WHEN DATENAME(weekday, DATEADD(day, -1, '2013-01-15')) = 'Saturday' THEN 1 ELSE 0 END)

Comment puis-je accomplir? Dois-je passer par tous les jours et de les vérifier? Ou est-il un moyen facile de le faire.

Comment allez-vous tenir compte des jours fériés qui n'est pas un week-end?
double possible de Nombre de jours de travail entre les deux dates

OriginalL'auteur Rocshy | 2013-02-07

14

S'il vous plaît, s'il vous plaît, veuillez utiliser un calendrier de table. SQL Server ne sait rien sur les jours fériés, événements d'entreprise, de catastrophes naturelles, etc. Un calendrier de table est assez facile à construire, prend une très faible quantité d'espace, et sera dans la mémoire si elle est référencée assez.

Voici un exemple qui crée un calendrier de table avec 30 ans de dates (2000 -> 2029), mais requiert seulement 200 KO sur le disque (136 KO si vous utilisez la compression de page). C'est presque assuré d'être inférieure à l'allocation de la mémoire nécessaire pour traiter un CTE ou d'un autre à l'exécution.
```
CREATE TABLE dbo.Calendar
(
  dt DATE PRIMARY KEY, -- use SMALLDATETIME if < SQL Server 2008
  IsWorkDay BIT
);

DECLARE @s DATE, @e DATE;
SELECT @s = '2000-01-01' , @e = '2029-12-31';

INSERT dbo.Calendar(dt, IsWorkDay)
  SELECT DATEADD(DAY, n-1, '2000-01-01'), 1 
  FROM
  (
    SELECT TOP (DATEDIFF(DAY, @s, @e)+1) ROW_NUMBER() 
      OVER (ORDER BY s1.[object_id])
      FROM sys.all_objects AS s1
      CROSS JOIN sys.all_objects AS s2
  ) AS x(n);

SET DATEFIRST 1;

-- weekends
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE DATEPART(WEEKDAY, dt) IN (6,7);

-- Christmas
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE MONTH(dt) = 12
  AND DAY(dt) = 25
  AND IsWorkDay = 1;

-- continue with other holidays, known company events, etc.
```
Maintenant la requête que vous êtes après est assez simple à écrire:
```
SELECT COUNT(*) FROM dbo.Calendar
  WHERE dt >= '20130110'
    AND dt <  '20130115'
    AND IsWorkDay = 1;
```
Plus d'infos sur les agendas:

http://web.archive.org/web/20070611150639/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Plus d'infos sur les groupes électrogènes sans boucles:

http://www.sqlperformance.com/tag/date-ranges

Aussi se méfier de ce genre de petites choses en s'appuyant sur la version anglaise de la sortie de DATENAME. J'ai vu plusieurs applications pause parce que certains utilisateurs avaient un autre paramètre de langue, et si vous êtes en s'appuyant sur WEEKDAY assurez-vous de configurer votre DATEFIRST paramètre de façon appropriée...

OriginalL'auteur Aaron Bertrand

Pour des trucs comme ça, j'ai tendance à tenir à jour un calendrier de table qui comprend également les jours fériés etc.

Le script que j'utilise pour ce est comme suit (Notez que je n'ai pas l'écrire @ je ne sais plus où je l'ai trouvé)

SET DATEFIRST 1
SET NOCOUNT ON
GO
--Create ISO week Function (thanks BOL)
CREATE FUNCTION ISOweek ( @DATE DATETIME )
RETURNS INT
AS 
BEGIN
DECLARE @ISOweek INT
SET @ISOweek = DATEPART(wk, @DATE) + 1 - DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104')
--Special cases: Jan 1-3 may belong to the previous year
IF ( @ISOweek = 0 ) 
SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4)) + '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1
--Special case: Dec 29-31 may belong to the next year
IF ( ( DATEPART(mm, @DATE) = 12 )
AND ( ( DATEPART(dd, @DATE) - DATEPART(dw, @DATE) ) >= 28 )
) 
SET @ISOweek = 1
RETURN(@ISOweek)
END
GO
--END ISOweek
--CREATE Easter algorithm function 
--Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689)
CREATE FUNCTION fnDLA_GetEasterdate ( @year INT )
RETURNS CHAR(8)
AS 
BEGIN
-- Easter date algorithm of Delambre
DECLARE @A INT ,
@B INT ,
@C INT ,
@D INT ,
@E INT ,
@F INT ,
@G INT ,
@H INT ,
@I INT ,
@K INT ,
@L INT ,
@M INT ,
@O INT ,
@R INT              
SET @A = @YEAR % 19
SET @B = @YEAR / 100
SET @C = @YEAR % 100
SET @D = @B / 4
SET @E = @B % 4
SET @F = ( @B + 8 ) / 25
SET @G = ( @B - @F + 1 ) / 3
SET @H = ( 19 * @A + @B - @D - @G + 15 ) % 30
SET @I = @C / 4
SET @K = @C % 4
SET @L = ( 32 + 2 * @E + 2 * @I - @H - @K ) % 7
SET @M = ( @A + 11 * @H + 22 * @L ) / 451
SET @O = 22 + @H + @L - 7 * @M
IF @O > 31 
BEGIN
SET @R = @O - 31 + 400 + @YEAR * 10000
END
ELSE 
BEGIN
SET @R = @O + 300 + @YEAR * 10000
END 
RETURN @R
END
GO
--END fnDLA_GetEasterdate
--Create the table
CREATE TABLE MyDateTable
(
FullDate DATETIME NOT NULL
CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED ,
Period INT ,
ISOWeek INT ,
WorkingDay VARCHAR(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y'
)
GO
--End table create
--Populate table with required dates
DECLARE @DateFrom DATETIME ,
@DateTo DATETIME ,
@Period INT
SET @DateFrom = CONVERT(DATETIME, '20000101')
--yyyymmdd (1st Jan 2000) amend as required
SET @DateTo = CONVERT(DATETIME, '20991231')
--yyyymmdd (31st Dec 2099) amend as required
WHILE @DateFrom <= @DateTo 
BEGIN
SET @Period = CONVERT(INT, LEFT(CONVERT(VARCHAR(10), @DateFrom, 112), 6))
INSERT  MyDateTable
( FullDate ,
Period ,
ISOWeek
)
SELECT  @DateFrom ,
@Period ,
dbo.ISOweek(@DateFrom)
SET @DateFrom = DATEADD(dd, +1, @DateFrom)
END
GO
--End population
/* Start of WorkingDays UPDATE */
UPDATE  MyDateTable
SET     WorkingDay = 'B' --B = Bank Holiday
--------------------------------EASTER---------------------------------------------
WHERE   FullDate = DATEADD(dd, -2, CONVERT(DATETIME, dbo.fnDLA_GetEasterdate(DATEPART(yy, FullDate)))) --Good Friday
OR FullDate = DATEADD(dd, +1, CONVERT(DATETIME, dbo.fnDLA_GetEasterdate(DATEPART(yy, FullDate))))
--Easter Monday
GO
UPDATE  MyDateTable
SET     WorkingDay = 'B'
--------------------------------NEW YEAR-------------------------------------------
WHERE   FullDate IN ( SELECT    MIN(FullDate)
FROM      MyDateTable
WHERE     DATEPART(mm, FullDate) = 1
AND DATEPART(dw, FullDate) NOT IN ( 6, 7 )
GROUP BY  DATEPART(yy, FullDate) )
---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------
OR FullDate IN ( SELECT MIN(FullDate)
FROM   MyDateTable
WHERE  DATEPART(mm, FullDate) = 5
AND DATEPART(dw, FullDate) = 1
GROUP BY DATEPART(yy, FullDate) )
OR FullDate IN ( SELECT MAX(FullDate)
FROM   MyDateTable
WHERE  DATEPART(mm, FullDate) = 5
AND DATEPART(dw, FullDate) = 1
GROUP BY DATEPART(yy, FullDate) )
--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------
OR FullDate IN ( SELECT MAX(FullDate)
FROM   MyDateTable
WHERE  DATEPART(mm, FullDate) = 8
AND DATEPART(dw, FullDate) = 1
GROUP BY DATEPART(yy, FullDate) )
--------------------XMAS(Move to next working day if on Sat/Sun)--------------------
OR FullDate IN ( SELECT CASE WHEN DATEPART(dw, FullDate) IN ( 6, 7 ) THEN DATEADD(dd, +2, FullDate)
ELSE FullDate
END
FROM   MyDateTable
WHERE  DATEPART(mm, FullDate) = 12
AND DATEPART(dd, FullDate) IN ( 25, 26 ) )
GO
---------------------------------------WEEKENDS--------------------------------------
UPDATE  MyDateTable
SET     WorkingDay = 'N'
WHERE   DATEPART(dw, FullDate) IN ( 6, 7 )
GO
/* End of WorkingDays UPDATE */
--SELECT * FROM MyDateTable ORDER BY 1
DROP FUNCTION fnDLA_GetEasterdate
DROP FUNCTION ISOweek
--DROP TABLE MyDateTable
SET NOCOUNT OFF

Une fois que vous avez créé la table, de trouver le nombre de jours de travail est simple comme bonjour:

SELECT  COUNT(FullDate) AS WorkingDays
FROM    dbo.tbl_WorkingDays
WHERE   WorkingDay = 'Y'
AND FullDate >= CONVERT(DATETIME, '10/01/2013', 103)
AND FullDate <  CONVERT(DATETIME, '15/01/2013', 103)

Noter que ce script contient royaume-UNI, les jours fériés, je ne sais pas dans quelle région vous êtes.

Juste une remarque sur votre première fonction, SQL-Server versions 2008 et, plus tard, prendre ISO_WEEK comme un paramètre dans DATEPART, ce sera plus efficace qu'un UDF.
Je suis d'accord, mais comme vous le savez, ce n'est exécuté qu'une fois, une fois le calendrier de table a été construite la fonction est ignorée et jamais utilisé, pour citer Clarke Gable “Franchement, ma chère, je ne donne pas une merde” lol
Votre requête à la fin utilise BETWEEN, et cela donne 4 lignes. L'OP a déclaré qu'ils avaient besoin de jeter le dernier jour de la gamme, de sorte que la requête doit être à l'aide de >= et <. Aussi pourquoi ne pas utiliser les formats de date (comme YYYYMMDD), de sorte que vous n'avez pas à faire tout ce qu'convertir des trucs? sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/... sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/...
si vous parcourez les questions que j'ai posées, vous trouverez une meilleure iso_week fonction

OriginalL'auteur HeavenCore

Voici une simple fonction qui compte les jours ouvrables, non compris le samedi et le dimanche (lors du décompte des jours fériés n'est pas nécessaire):

CREATE FUNCTION dbo.udf_GetBusinessDays (
@START_DATE DATE,
@END_DATE DATE
)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @NUMBER_OF_DAYS INT = 0;
DECLARE @DAY_COUNTER INT = 0;
DECLARE @BUSINESS_DAYS INT = 0;
DECLARE @CURRENT_DATE DATE;
DECLARE @DAYNAME NVARCHAR(9)
SET @NUMBER_OF_DAYS = DATEDIFF(DAY, @START_DATE, @END_DATE);
WHILE @DAY_COUNTER <= @NUMBER_OF_DAYS
BEGIN
SET @CURRENT_DATE = DATEADD(DAY, @DAY_COUNTER, @START_DATE)
SET @DAYNAME = DATENAME(WEEKDAY, @CURRENT_DATE)
SET @DAY_COUNTER += 1
IF @DAYNAME = N'Saturday' OR @DAYNAME = N'Sunday'
BEGIN
CONTINUE
END
ELSE
BEGIN
SET @BUSINESS_DAYS += 1
END
END
RETURN @BUSINESS_DAYS
END
GO

OriginalL'auteur SHennessy

C'est la méthode que j'ai l'habitude d'utiliser (si vous n'utilisez pas un calendrier de table):

DECLARE @T TABLE (Date1 DATE, Date2 DATE);
INSERT @T VALUES ('20130110', '20130115'), ('20120101', '20130101'), ('20120611', '20120701');
SELECT  Date1, Date2, WorkingDays
FROM    @T t
CROSS APPLY
(   SELECT  [WorkingDays] = COUNT(*)
FROM    Master..spt_values s
WHERE   s.Number BETWEEN 1 AND DATEDIFF(DAY, t.date1, t.Date2)
AND     s.[Type] = 'P'
AND     DATENAME(WEEKDAY, DATEADD(DAY, s.number, t.Date1)) NOT IN ('Saturday', 'Sunday')
) wd

Si comme moi, vous avez un tableau avec les jours fériés, vous pouvez ajouter ce dans de trop:

SELECT  Date1, Date2, WorkingDays
FROM    @T t
CROSS APPLY
(   SELECT  [WorkingDays] = COUNT(*)
FROM    Master..spt_values s
WHERE   s.Number BETWEEN 1 AND DATEDIFF(DAY, t.date1, t.Date2)
AND     s.[Type] = 'P'
AND     DATENAME(WEEKDAY, DATEADD(DAY, s.number, t.Date1)) NOT IN ('Saturday', 'Sunday')
AND     NOT EXISTS
(   SELECT  1
FROM    HolidayTable ht
WHERE   ht.Date = DATEADD(DAY, s.number, t.Date1)
)
) wd

Le ci-dessus ne fonctionnera que si vos dates sont dans 2047 jours les uns des autres, si vous êtes susceptible d'être le calcul des plus grandes plages de dates vous pouvez utiliser ceci:

SELECT  Date1, Date2, WorkingDays
FROM    @T t
CROSS APPLY
(   SELECT  [WorkingDays] = COUNT(*)
FROM    (   SELECT  [Number] = ROW_NUMBER() OVER(ORDER BY s.number)
FROM    Master..spt_values s
CROSS JOIN Master..spt_values s2
) s
WHERE   s.Number BETWEEN 1 AND DATEDIFF(DAY, t.date1, t.Date2)
AND     DATENAME(WEEKDAY, DATEADD(DAY, s.number, t.Date1)) NOT IN ('Saturday', 'Sunday')
) wd

OriginalL'auteur GarethD

J'ai fait de mon code dans SQL SERVER 2008 (MS SQL) . Il fonctionne très bien pour moi. J'espère que ça va vous aider.

     DECLARE  @COUNTS int,                       
@STARTDATE  date,
@ENDDATE date
SET @STARTDATE ='01/21/2013' /*Start date in mm/dd/yyy */
SET @ENDDATE ='01/26/2013' /*End date in mm/dd/yyy */
SET @COUNTS=0
WHILE (@STARTDATE<=@ENDDATE)
BEGIN
/*Check for holidays*/
IF  ( DATENAME(weekday,@STARTDATE)<>'Saturday' and  DATENAME(weekday,@STARTDATE)<>'Sunday')                                
BEGIN 
SET @COUNTS=@COUNTS+1
END
SET @STARTDATE=DATEADD(day,1,@STARTDATE)
END
/* Display the no of working days */
SELECT @COUNTS

OriginalL'auteur KESAVAN PURUSOTHAMAN

Vous devez vous connecter pour publier un commentaire.