Le calcul des jours à l'exclusion des fins de semaine (lundi au vendredi) dans SQL Server

Je recommande toujours une Calendrier de table, alors il suffit d'utiliser:

SELECT  COUNT(*)
FROM    dbo.CalendarTable
WHERE   IsWorkingDay = 1
AND     [Date] > @StartDate
AND     [Date] <= @EndDate;

Depuis SQL n'a pas connaissance de jours fériés par exemple le nombre de jours ouvrés entre deux dates ne représentent pas toujours le nombre de jours de travail. C'est pourquoi un calendrier de table est un must pour la plupart des bases de données. Ils ne prennent pas beaucoup de mémoire et de simplifier beaucoup de requêtes.

Mais si ce n'est pas une option, alors vous pouvez générer un tableau de dates relativement facilement à la volée et l'utilisation de cette

SET DATEFIRST 1;
DECLARE @StartDate DATETIME = '20131103', 
        @EndDate DATETIME = '20131104';

-- GENERATE A LIST OF ALL DATES BETWEEN THE START DATE AND THE END DATE
WITH AllDates AS
(   SELECT  TOP (DATEDIFF(DAY, @StartDate, @EndDate))
            D = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.Object_ID), @StartDate)
    FROM    sys.all_objects a
            CROSS JOIN sys.all_objects b
)
SELECT  WeekDays = COUNT(*)
FROM    AllDates
WHERE   DATEPART(WEEKDAY, D) NOT IN (6, 7);

---

MODIFIER

Si vous devez calculer la différence entre les deux colonnes de date, vous pouvez toujours utiliser votre calendrier de table de la manière suivante:

SELECT  t.ID,
        t.Date1,
        t.Date2,
        WorkingDays = COUNT(c.DateKey)
FROM    TestTable t
        LEFT JOIN dbo.Calendar c
            ON c.DateKey >= t.Date1
            AND c.DateKey < t.Date2
            AND c.IsWorkingDay = 1
GROUP BY t.ID, t.Date1, t.Date2;

Exemple sur SQL-Violon

À ajouter à GarethD réponse - j'ai mis en place le SQL pour les états-unis, version du Calendrier de la table, avec tous les jours fériés et les week-ends ensemble de is_working_day = false... pour quelqu'un qui serait comme le SQL, c'est ici:

declare @start_dt as date = '1/1/2009';     -- Date from which the calendar table will be created.
declare @end_dt as date = '1/1/2030';       -- Calendar table will be created up to this date (not including).
create table CalendarTable (
date_id date primary key,
date_year smallint,
date_month tinyint,
date_day tinyint,
weekday_id tinyint,
weekday_nm varchar(10),
month_nm varchar(10),
day_of_year smallint,
quarter_id tinyint,
first_day_of_month date,
last_day_of_month date,
start_dts datetime,
end_dts datetime,
week_number_of_month int,
is_working_day bit,
)
while @start_dt < @end_dt
begin
insert into CalendarTable(
date_id, date_year, date_month, date_day, 
weekday_id, weekday_nm, month_nm, day_of_year, quarter_id, 
first_day_of_month, last_day_of_month, 
start_dts, end_dts, week_number_of_month, is_working_day
)   
values(
@start_dt, year(@start_dt), month(@start_dt), day(@start_dt), 
datepart(weekday, @start_dt), datename(weekday, @start_dt),     datename(month, @start_dt), datepart(dayofyear, @start_dt), datepart(quarter,     @start_dt),
dateadd(day,-(day(@start_dt)-1),@start_dt), dateadd(day,-(day(dateadd(month,1,@start_dt))),dateadd(month,1,@start_dt)), 
cast(@start_dt as datetime), dateadd(second,-1,cast(dateadd(day, 1, @start_dt) as datetime)), DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_dt), 0), @start_dt) +1, 0
)
set @start_dt = dateadd(day, 1, @start_dt)
end
GO
-- Set all non-weekend days as business days
update CalendarTable set is_working_day = 1 where weekday_id not in (1,7)
GO
-- New Years Day
update CalendarTable set is_working_day = 0 where date_month = 1 and date_day = 1
GO
-- Memorial Day (last Monday of May)
WITH Memorial_Day AS
(
SELECT date_id, date_year, date_day,
ROW_NUMBER() OVER (PARTITION BY date_year ORDER BY date_day desc) AS RowNumber
FROM CalendarTable
where date_month = 5 and weekday_id = 2
)   
update CalendarTable set is_working_day = 0 where date_id in (SELECT date_id FROM Memorial_Day 
where rownumber = 1)
GO
-- Independence Day
update CalendarTable set is_working_day = 0 where date_month = 7 and date_day = 4
GO
-- Labor Day (first Monday in September)
WITH Labor_Day AS
(
SELECT date_id, date_year, date_day,
ROW_NUMBER() OVER (PARTITION BY date_year ORDER BY date_day) AS RowNumber
FROM CalendarTable
where date_month = 9 and weekday_id = 2
)   
update CalendarTable set is_working_day = 0 where date_id in (SELECT date_id FROM Labor_Day 
where rownumber = 1)
GO
-- Thanksgiving (fourth Thursday in November)
WITH Thanksgiving AS
(
SELECT date_id, date_year, date_day,
ROW_NUMBER() OVER (PARTITION BY date_year ORDER BY date_day) AS RowNumber
FROM CalendarTable
where date_month = 11 and weekday_id = 5
)   
update CalendarTable set is_working_day = 0 where date_id in (SELECT date_id FROM Thanksgiving 
where rownumber = 4)
GO
-- Day After Thanksgiving (fourth Friday in November)
WITH DayAfterThanksgiving AS
(
SELECT date_id, date_year, date_day,
ROW_NUMBER() OVER (PARTITION BY date_year ORDER BY date_day) AS RowNumber
FROM CalendarTable
where date_month = 11 and weekday_id = 6
)   
update CalendarTable set is_working_day = 0 where date_id in (SELECT date_id FROM DayAfterThanksgiving 
where rownumber = 4)
GO
-- Christmas Day
update CalendarTable set is_working_day = 0 where date_month = 12 and date_day = 25
GO

Question intéressante. Il est toujours important de comprendre les cas d'utilisation. Si à compter de dimanche à lundi, voulons-nous dire qu'il n'y est un jour comme si nous avions jusqu'à la fermeture des affaires le lundi. Ou aurions-nous envie de dire il n'y a pas de jours comme s'il n'y avait pas de jours avant le lundi a commencé. Dans notre cas, nous avons besoin de compter deux jours (début et fin) s'ils ont été les jours de la semaine, parce que je travaillais dans une application de paie de l'estimation de certaines charges à payer. Et toute la journée le week-end jours plus tard être représenté par des vacances, d'heures supplémentaires et d'enregistrements.

Quand je suis sorti de mon calendrier, j'ai réalisé que lorsque le comptage à partir de samedi ou dimanche seulement nous pouvions commencer à compter à partir de lundi. Et aussi lors de la comptabilisation d'un samedi ou un dimanche, je pouvais juste arrêter de compter quand nous sommes arrivés à vendredi. J'ai donc écrit une fonction qui ajuste le début et date de fin, le nombre de semaines en divisant par 7, multiplié par 5 jours de la semaine par semaine et ensuite ajoutée le reste. J'ai dû en compte le cas lorsque nous avons commencé à compter sur un week-end, mais n'a jamais eu à lundi.

-- =============================================
-- Auteur: Todd P Payne
-- Date de création: 9/1/2018
-- Description: Compte le nombre de jours ouvrés entre deux dates
- Contrairement à DateDiff StartDate et EndDate inclusive
- À PARTIR de lundi, du 1er janvier au lundi 1er Jan sera de retour 1
-- =============================================
CRÉER la FONCTION [dbo].[ufnCountWeekdays]
(
- Ajouter les paramètres de la fonction ici
@StartDate DateTime,
@EndDate DateTime
)
RETOURNE INT
COMME
COMMENCER
-- Déclarer la variable de retour ici
DECLARE @CountofWeekDays INT = NULL;
DECLARE @TempDate DateTime;

-- Could CountBackwords 
IF @StartDate > @ENDDate
BEGIN
SET @TempDate = @StartDate; 
SET @StartDate = @EndDate;
SET @EndDate = @TempDate;
END
--Start on Weekend Never get to Monday Case
IF (DatePart(dw,@StartDate) = 7 AND DateDiff(Day,@StartDate,@EndDate) < 2)
OR (DatePart(dw,@StartDate) = 1 AND DateDiff(Day,@StartDate,@EndDate) < 1 )
BEGIN 
SET @CountOfWeekDays = 0 -- Never got to a WeekDay
END
--NORMAL CASE
ELSE BEGIN 
-- IF Sat Start just pretend Start Counting on Monday 
IF (DatePart(dw,@StartDate) = 7) SET @StartDate = dateadd(Day, 2, @StartDate);
-- IF Sun Start just Start to Counting on Monday 
IF (DatePart(dw,@StartDate) = 1) SET @StartDate = dateadd(Day, 1, @StartDate);
-- Sat End just Stop counting on Friday
IF (DatePart(dw,@EndDate) = 7) SET @EndDate = DateAdd(Day, -1, @EndDate);
-- Sun END
if (DatePart(dw,@EndDate) = 1) SET  @EndDate = DateAdd(Day, -2, @EndDate);
--Find the total number of days we need to count
Declare @DaysToCount INT = DateDiff(Day,@StartDate, @EndDate)+1; --include start
-- five days for each full week plus any other weekdays 
-- remember no worries about starting or ending on weekends
Set @CountofWeekDays  = Floor(@DaysToCount/7)*5 + (@DaysToCount % 7)
END     
--Check to see if we are counting backwards
IF @TempDate = @EndDate SET @CountofWeekDays = -1 * @CountofWeekDays;
RETURN @CountofWeekDays;

FIN
ALLER

Heureux De Codage

Todd P Payne