question

MohamedGADER-3104 avatar image
0 Votes"
MohamedGADER-3104 asked ErlandSommarskog answered

Calculer Différence datettime

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:83580-buisness-hours.png


Table Holidays:


83696-holidays.png





merci d'avance

sql-server-generalsql-server-transact-sql
buisness-hours.png (4.4 KiB)
holidays.png (12.4 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Give an example with two given datetimes and the output you expect.

0 Votes 0 ·

Date: samedi 03-04-2021 07:00:00 -->lundi 06-04-2021 07:30:00

03-04-2021::nbre d'heure de travail Samedi :07-->13
04-04-2021::Dimanche non ouvrable
05-04-2021::Lundi non ouvrable
06-04-2021::nbre d'heure de travail Samedi :07-->15

la procédure doit envoyer une resultat:390 minutes (6heures,30minutes)

0 Votes 0 ·

Sorry,Can you describe your calculation rules in English?

Echo

0 Votes 0 ·

Hi @MohamedGADER-3104,

Welcome to microsoft TSQL Q&A forum!The common language of this forum is English.

In addition, when you post a question, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result. So that we’ll get a right direction and make some test.

Regards
Echo

0 Votes 0 ·

salut


USE [TYPE]
GO

/ Object: Table [dbo].[business_hours] Script Date: 02/04/2021 07:08:43 /
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[business_hours](
[work_day] [varchar](10) NULL,
[open_time] [varchar](8) NULL,
[close_time] [varchar](8) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO







USE [TYPE]
GO

/ Object: Table [dbo].[holidays] Script Date: 02/04/2021 07:10:00 /
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[holidays](
[holiday] [varchar](50) NULL,
[month] [varchar](50) NULL,
[annee] [varchar](50) NULL,
[jour] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO




0 Votes 0 ·

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

[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](10) NULL,
[open_time] [varchar](8) NULL,
[close_time] [varchar](8) 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!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.