MS SQL Server
  1. MS SQL Server
  3. Evénement de correspondance trompeur aux heures de bureau dans TSQL
Intereting Posts
Sélectionnez N lignes avant et N lignes après l'logging Comment agréger des sets similaires dans SQL Méthode efficace de concaténation de plusieurs strings à partir de plusieurs loggings liés en strings uniques par string d'loggings Renommer une colonne existante sans time d'arrêt Le server SQL ne returnne pas toutes les lignes Coincé avec une requête SQL quelque peu complexe Comment puis-je m'assurer que les lignes ne sont pas chargées deux fois avec EF / LINQ Création de noms de tables qui sont des mots / mots-keys réservés dans MS SQL Server Accès à la porte SQL Que signifient les catégories dans les Jobs de l'Agent SQL Server? Récupérer les valeurs de la table de configuration dans la requête where clause Comment former la string de connection pour une instance SQL Server Express? Insérer des numéros dans une table en séquence mais sans IDENTITY Est-ce que joindre change le sorting de la table d'origine? LinqToSql Query time de return trop de performance trop longtime

Evénement de correspondance trompeur aux heures de bureau dans TSQL

J'ai un problème délicat avec les sets de timespans.

Par exemple: Dans une grande entreprise, une voiture tombe en panne et est soumise à l'équipe mécanique interne. J'ai le time, quand il est soumis et libéré. Qu'est-ce que je cherche, combien de time a été la voiture dans le garage avec des gens possible de travailler dessus?

J'ai donc deux tables.

1.) Une table @e avec eventstart et endtimes. 2.) Une table @o où officehours pour chaque jour de la semaine. Le jour de la semaine commence le lundi.

Ce dont j'ai besoin, c'est le time supplémentaire passé pendant les heures de bureau.

J'ai fait un set avec des données d'échantillon, pour un test facile: 

declare @o table ([id] [int], [weekday] [smallint], [starttime] [time](7), [endtime] [time](7)) insert into @o([id], [weekday], [starttime], [endtime]) values (1, 1, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)), (2, 2, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)), (3, 3, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)), (4, 4, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)), (5, 5, CAST(N'09:00:00' AS Time), CAST(N'14:00:00' AS Time)), (6, 6, CAST(N'12:00:00' AS Time), CAST(N'15:00:00' AS Time)), (7, 7, CAST(N'12:00:00' AS Time), CAST(N'15:00:00' AS Time)) declare @e table ( [startspan] [datetime], [endspan] [datetime] ) INSERT INTO @e ([startspan], [endspan]) VALUES (CAST(N'2015-05-06 15:08:59' AS DateTime), CAST(N'2015-05-13 09:52:09' AS DateTime)), (CAST(N'2015-05-05 20:46:17' AS DateTime), CAST(N'2015-05-08 11:34:50' AS DateTime)), (CAST(N'2015-05-02 14:42:23' AS DateTime), CAST(N'2015-05-05 17:22:30' AS DateTime)), (CAST(N'2015-05-01 09:07:36' AS DateTime), CAST(N'2015-05-04 08:31:35' AS DateTime)), (CAST(N'2015-05-01 00:16:00' AS DateTime), CAST(N'2015-05-04 12:58:27' AS DateTime)), (CAST(N'2015-04-30 19:14:25' AS DateTime), CAST(N'2015-05-05 20:29:48' AS DateTime)), (CAST(N'2015-04-24 12:48:34' AS DateTime), CAST(N'2015-04-27 16:15:22' AS DateTime)), (CAST(N'2015-04-22 13:05:29' AS DateTime), CAST(N'2015-04-27 11:13:28' AS DateTime)), (CAST(N'2015-04-18 11:01:17' AS DateTime), CAST(N'2015-04-20 15:44:41' AS DateTime)), (CAST(N'2015-04-18 09:49:51' AS DateTime), CAST(N'2015-04-20 12:18:42' AS DateTime))

Mon approche actuelle serait de split tous les events avec un eventid en jours simples avec start + end, puis de calculer le time pour chaque événement et le groupe sur eventid et de totaliser le time. Ce serait une charge de sql. Si quelqu'un a une approche différente ou des idées, c'est très apprécié.

Pour éviter la question: j'utilise MS SQL-Server 2014, j'ai environ 10 millions d'events.

Merci pour toute aide ou idées que vous pourriez avoir.

Métanormal

À mon avis, une simple requête comme celle-ci fera l'affaire 

 ;with alldates as ( select convert(datetime, '2015-04-01') as d union all select DATEADD(day, 1, d) from alldates where d < '2015-06-01' ), oh as ( select alldates.d , alldates.d + CONVERT(datetime, #o.starttime) as starttime , alldates.d + CONVERT(datetime, #o.endtime) as endtime ,datediff(minute, alldates.d + CONVERT(datetime, #o.starttime), alldates.d + CONVERT(datetime, #o.endtime)) as tothours from alldates inner join #o on DATEPART(weekday, alldates.d) = #o.id ) select #e.[startspan], #e.[endspan], SUM( datediff(minute, case when cast(#e.[startspan] as date)=oh.d then #e.[startspan] else oh.starttime end, case when cast(#e.[endspan] as date)=oh.d then #e.[endspan] else oh.endtime end) ) as totminutes from oh inner join #e on cast(#e.[startspan] as date) <= oh.d and cast(#e.[endspan] as date) >= oh.d --where #e.startspan = '2015-05-06 15:08:59' group by #e.[startspan], #e.[endspan] order by 1

Il y a une limitation. Vous devez définir les dates de début et de fin du CTE alldates comme étant les dates d'événement les plus récentes et les plus récentes dans votre système. Pour datatables de cette taille, vous devriez envisager de créer une table alldates avec toutes les dates, et mieux encore, créer toute la table de calendar similaire au oh CTE. De cette façon, vous devrez utiliser uniquement le dernier SELECT pour get le résultat souhaité.

Résultats 

 startspan endspan totminutes ----------------------- ----------------------- ----------- 2015-04-18 09:49:51.000 2015-04-20 12:18:42.000 989 2015-04-18 11:01:17.000 2015-04-20 15:44:41.000 1123 2015-04-22 13:05:29.000 2015-04-27 11:13:28.000 1508 2015-04-24 12:48:34.000 2015-04-27 16:15:22.000 1227 2015-04-30 19:14:25.000 2015-05-05 20:29:48.000 1695 2015-05-01 00:16:00.000 2015-05-04 12:58:27.000 1782 2015-05-01 09:07:36.000 2015-05-04 08:31:35.000 984 2015-05-02 14:42:23.000 2015-05-05 17:22:30.000 1480 2015-05-05 20:46:17.000 2015-05-08 11:34:50.000 528 2015-05-06 15:08:59.000 2015-05-13 09:52:09.000 2264 (10 row(s) affected)

Beaucoup de merci à CHA et l'autre input.

La réponse de Cha était presque parfaite, j'ai dû prendre quelques promotions avec un time négatif. et il y avait une colonne-typo avec le jour de la semaine] et id.

Et maintenant, ça fonctionne parfaitement.

J'ai ajouté un identifiant aux events, qu'ils ont à l'origine (bien sûr ;-).

Voici le code d'exemple de travail complet: 

 declare @o table ([oid] [int], [weekday] [smallint], [starttime] [time](7), [endtime] [time](7)) insert into @o([oid], [weekday], [starttime], [endtime]) values (1, 1, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)), (2, 2, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)), (3, 3, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)), (4, 4, CAST(N'09:00:00' AS Time), CAST(N'17:00:00' AS Time)), (5, 5, CAST(N'09:00:00' AS Time), CAST(N'14:00:00' AS Time)), (6, 6, CAST(N'12:00:00' AS Time), CAST(N'15:00:00' AS Time)), (7, 7, CAST(N'12:00:00' AS Time), CAST(N'15:00:00' AS Time)) declare @e table ( [eid] [int], [startspan] [datetime], [endspan] [datetime] ) INSERT INTO @e ([eid], [startspan], [endspan]) VALUES (1,CAST(N'2015-05-06 15:08:59' AS DateTime), CAST(N'2015-05-13 09:52:09' AS DateTime)), (2,CAST(N'2015-05-05 20:46:17' AS DateTime), CAST(N'2015-05-08 11:34:50' AS DateTime)), (3,CAST(N'2015-05-02 14:42:23' AS DateTime), CAST(N'2015-05-05 17:22:30' AS DateTime)), (4,CAST(N'2015-05-01 09:07:36' AS DateTime), CAST(N'2015-05-04 08:31:35' AS DateTime)), (5,CAST(N'2015-05-01 00:16:00' AS DateTime), CAST(N'2015-05-04 12:58:27' AS DateTime)), (6,CAST(N'2015-04-30 19:14:25' AS DateTime), CAST(N'2015-05-05 20:29:48' AS DateTime)), (7,CAST(N'2015-04-24 12:48:34' AS DateTime), CAST(N'2015-04-27 16:15:22' AS DateTime)), (8,CAST(N'2015-04-22 13:05:29' AS DateTime), CAST(N'2015-04-27 11:13:28' AS DateTime)), (9,CAST(N'2015-04-18 11:01:17' AS DateTime), CAST(N'2015-04-20 15:44:41' AS DateTime)), (10,CAST(N'2015-04-18 09:49:51' AS DateTime), CAST(N'2015-04-20 12:18:42' AS DateTime)) set datefirst 1 ;with alldates as ( select convert(datetime, '2015-01-01') as d union all select DATEADD(day, 1, d) from alldates where d < '2015-07-01' ) , oh as ( select alldates.d , alldates.d + CONVERT(datetime, o.[starttime]) as starttime , alldates.d + CONVERT(datetime, o.endtime) as endtime ,datediff(minute, alldates.d + CONVERT(datetime, o.starttime), alldates.d + CONVERT(datetime, o.endtime)) as tothours from alldates inner join @o as o on DATEPART(weekday, alldates.d) = o.[weekday] ) select e.eid, e.[startspan], e.[endspan] ,sum( datediff(minute, case when cast(e.[startspan] as date)=oh.d then case when e.[startspan] < starttime then starttime else case when e.[startspan] < endtime then e.[startspan] else endtime end end else oh.starttime end, case when cast(e.[endspan] as date)=oh.d then case when e.[endspan] < endtime then case when e.[endspan] < starttime then starttime else e.[endspan] end else e.[endspan] end else oh.endtime end) ) totminutes from oh inner join @e as e on cast(e.[startspan] as date) <= oh.d and cast(e.[endspan] as date) >= oh.d --where e.startspan = '2015-05-01 09:07:36.000' group by e.[eid], e.[startspan], e.[endspan] order by e.[eid] option (maxrecursion 0)