Je veux calculer le nombre de jours ouvrables entre deux dates données. Par exemple, si je veux calculer les jours ouvrables entre 2013-01-10 et 2013-01-15, le résultat doit être de 3 jours ouvrables (je ne prends pas en count le dernier jour de cet intervalle et je soustrais les samedis et Les dimanches). J'ai le code suivant qui fonctionne pour la plupart des cas, sauf celui de 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 cela? Dois-je passer par tous les jours et les vérifier? Ou y a-t-il un moyen facile de le faire.
S'il vous plaît, s'il vous plaît, s'il vous plaît utiliser une table de calendar. SQL Server ne connaît rien des jours fériés nationaux, des events d'entreprise, des catastrophes naturelles, etc. Une table de calendar est assez facile à build, prend un espace extrêmement réduit et sera en memory si elle est suffisamment référencée.
Voici un exemple qui crée une table de calendar avec 30 ans de dates (2000 -> 2029) mais nécessite seulement 200 Ko sur le disque (136 Ko si vous utilisez la compression de page). C'est presque garanti d'être inférieur à la subvention de memory requirejse pour traiter certains CTE ou autre set à 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 searchz est assez simple à écrire:
SELECT COUNT(*) FROM dbo.Calendar WHERE dt >= '20130110' AND dt < '20130115' AND IsWorkDay = 1;
Plus d'infos sur les tables de calendar:
Plus d'infos sur les groupes électrogènes sans loops:
http://www.sqlperformance.com/tag/date-ranges
Méfiez-vous également des petites choses comme s'appuyer sur la sortie anglaise de DATENAME
. J'ai vu plusieurs applications se casser parce que certains users avaient un paramètre de langue différent, et si vous WEEKDAY
sur WEEKDAY
soyez sûr que vous définissez votre paramètre DATEFIRST
manière appropriée …
Pour des choses comme cela, j'ai tendance à maintenir une table de calendar qui comprend également les jours fériés, etc.
Le script que j'utilise pour ceci est comme suit (notez que je n'ai pas l'écrire @ j'oublie 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, find le nombre de jours ouvrables est simple:
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)
Notez que ce script inclut les jours fériés au Royaume-Uni, je ne sais pas dans quelle région vous êtes.
Voici une fonction simple qui count les jours ouvrables non compris le samedi et le dimanche (lorsque le comptage des congé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
C'est la méthode que j'utilise normalement (lorsque vous n'utilisez pas une table de calendar):
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 une table avec des vacances, vous pouvez append ceci:
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
Ce qui précède ne fonctionnera que si vos dates sont dans les 2047 jours de l'un l'autre, si vous êtes susceptible de calculer des plages de dates plus importantes, 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
J'ai fait mon code dans SQL SERVER 2008 (MS SQL). Ça fonctionne bien pour moi. J'espère que ça t'aidera.
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