Calculer Différence datettime

Question

Bonjour,
j'ai deux tables:
buisness_hours:dans la quelle de définie les heures de travail pour les jours de la semaine.
Buisness_holidays:dans la quelle de définie les jours non travaillé.
l'objectif c'est de créer une procédure sql pour calculer la différence entre deux datetime donnée en minute on tenant compte de l'horaire du jour et les jours non ouvrable.
vous trouverez ci-joint les deux tables(capture)

Table buisness_hours:

Table Holidays:

merci d'avance

Answer 1

[For French-impaired there is an English summary at the end.]

Pour un problème comme ça, it faut avoir une table de calendrier. Voici un example, pour créer une telle table:

WITH dates AS (
   SELECT dateadd(DAY, number, '20210101')  AS date
   FROM   master.dbo.spt_values
   WHERE  type = 'P'
     AND  number < 365
), starttimes AS (
   SELECT date, 
          CASE WHEN datename(WEEKDAY, date) = 'Sunday' THEN NULL
               WHEN date IN ('20210101', '20210601', '20210402', '20210405',
                             '20210501', '20210513', '20210606', '20210625', 
                             '20210626', '20211106', '20211224', '20211225',
                             '20211231') THEN NULL
               ELSE '07:00'
          END AS starttime
    FROM  dates
)
INSERT calendar (date, starttime, endtime)
   SELECT date, starttime, 
          dateadd(HOUR, IIF(datename(WEEKDAY, date) = 'Saturday', 6, 8), starttime)
   FROM   starttimes

Dans cet example j'ai usé les jour des fètes suèdois. Vous avez changer pour les jour dans votre pais.

J'use le fonction datename, parce-que je le trouve plus facile de comprendre de datepart que nombrer les dates dependent le setting to DATEFIRST. Mais si vous avez un installation français de SQL Server, vous avez change le noms de jour.

Avec cette table, vous pouvez écrire un query comme en cet example:

DECLARE @startdatetime datetime2(0) = '20210401 14:00',
        @enddatetime   datetime2(0) = '20210406 10:00'

; WITH CTE AS (
   SELECT startdate = convert(date, @startdatetime),
          starttime = convert(time(0), @startdatetime),
          enddate   = convert(date, @enddatetime),
          endtime   = convert(time(0), @enddatetime)
)
SELECT SUM(CASE WHEN c.date = CTE.startdate THEN
                     CASE WHEN CTE.starttime < c.starttime
                             THEN datediff(MINUTE, c.starttime, c.endtime)
                          WHEN CTE.starttime < c.endtime
                             THEN datediff(MINUTE, CTE.starttime, c.endtime)
                          ELSE 0
                     END
                WHEN c.date = CTE.enddate THEN
                     CASE WHEN CTE.endtime < c.starttime
                             THEN 0
                          WHEN CTE.endtime < c.endtime
                             THEN datediff(MINUTE, c.starttime, CTE.endtime)
                          ELSE datediff(MINUTE, c.starttime, c.endtime)
                     END
               ELSE datediff(MINUTE, c.starttime, c.endtime)
          END)
FROM   calendar c
JOIN   CTE ON c.date BETWEEN CTE.startdate AND CTE.enddate

CREATE TABLE [dbo].[business_hours](
[work_day] varchar NULL,
[open_time] varchar NULL,
[close_time] varchar NULL

Non jamais usez varchar pour dates ou temps! Toujour usez les propre types, comme j'ai fait ci-dessus.

.......................................................................

For this problem we need a calendar table, and I have script to populate it for one year. Beware that it uses Swedish holidays. I use datename rather than datepart to get days of the week, because I find datepart more difficult to understand.

Once we have a calendar table, we can use the query I propose.

Finally, I point out that using varchar for date and time is something you should never do. Always use the proper data types!