Eclater des heures

Version imprimable

03/03/2015, 12h07
Taz 31

Eclater des heures

Code:

SQL SERVER 2008

- Eclater des heures

J'ai une date de début 01/01/2015 00:00:00, une heure de fin 01/01/2015 23:59:59.
Est-il possible d'éclater cette tranche horaire par période de 30 minutes?

Cordialement.

Taz
03/03/2015, 12h09
mikedavem

Hello,

Quel est le besoin?

++
03/03/2015, 13h02
Taz 31

Eclater des heures.

Code:

SQL SERVER

Je veux pouvoir comptabilser le nombre d'agents disponibles par tranche horaire de 1/2 heure.

Taz
03/03/2015, 14h37
mikedavem

On pourrait avoir un exemple de ta table avec un jeu de données.

Comment sont stockées tes agents dans ta base de données et la corrélation avec les intervalles de date que tu donnes

Citation:

J'ai une date de début 01/01/2015 00:00:00, une heure de fin 01/01/2015 23:59:59.
Je veux pouvoir comptabilser le nombre d'agents disponibles par tranche horaire de 1/2 heure.

++

Est-ce que ce code pourrait répondre à ton besoin?
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 DECLARE @DateEntree DateTime = '2001-01-01' --Date de début de génération , @DateFin DateTime SET @DateFin = DATEADD( DD, 1, @DateEntree ); select @DateFin, @DateEntree ;WITH DateCTE AS ( SELECT CAST(@DateEntree AS DateTime) AS DateValue UNION ALL SELECT DATEADD(MI,30,DateValue) FROM DateCTE WHERE DATEADD(MI,30,DateValue) < @DateFin ) select * from DateCTE
les données sont générées pour 1 journée à raison de 1ligne toutes les 30minutes.
Il n'y a que le 23h59 qui n'est pas réellement faisable en l'état, (surtout que j'ai pas forcément cherché à optimiser) Après, tout peut se faire avec un poil de temps passé dessus ;)

Cordialement,

03/03/2015, 15h32
mikedavem

C'est la raison pour laquelle je lui demandais de préciser un peu plus le contexte :-)

En fonction de ce qui est à faire l'approche peut être différente .. générer dynamiquement des lignes horaires est envisageable pour un jeu de données relativement faible ...
S'il doit générer ceci pour un nombre de lignes important (et j'en suis à peu près sûr ici) , ca sera une autre histoire :-)
++
03/03/2015, 15h33
Taz 31

Eclater des heures.

Nom agent DH_DEBUT_SPEC DH_FIN_SPEC LIBELLE CODE_FCTOCCUPEE
MOI STEPHANE 28/02/2015 05:00:00 28/02/2015 07:30:00 AST 1 PLONGEUR
DUP CHRISTOPHE 28/02/2015 07:00:00 28/02/2015 20:00:00 GARDE SPV PLONGEUR
LAN THOMAS 28/02/2015 07:00:00 28/02/2015 14:00:00 DISP SPEC V PLONGEUR
MOI STEPHANE 28/02/2015 07:30:00 01/03/2015 07:30:00 GARDE SPP PLONGEUR
LAN THOMAS 28/02/2015 17:30:00 01/03/2015 12:00:00 DISP SPEC V PLONGEUR
GEN MATTHIEU 28/02/2015 19:00:00 01/03/2015 07:00:00 AST RO NU PLONGEUR
BOU NICOLAS 01/03/2015 00:30:00 01/03/2015 07:30:00 DISP SPEC P PLONGEUR
BOU NICOLAS 01/03/2015 07:30:00 02/03/2015 07:30:00 GARDE SPP PLONGEUR
CHI JULIEN 01/03/2015 07:30:00 02/03/2015 07:30:00 GARDE SPP PLONGEUR
GRE JOCELYN 01/03/2015 07:30:00 02/03/2015 07:30:00 GARDE SPP PLONGEUR
LAC PIERRE 01/03/2015 07:30:00 02/03/2015 07:30:00 GARDE SPP PLONGEUR
DUP CHRISTOPHE 01/03/2015 16:30:00 01/03/2015 19:30:00 Astreinte 1 PLONGEUR
LAN THOMAS 01/03/2015 19:00:00 02/03/2015 07:00:00 Astreinte 1 PLONGEUR
GEN MATTHIEU 01/03/2015 19:30:00 02/03/2015 07:00:00 AST RO NU PLONGEUR
DU CHRISTOPHE 01/03/2015 20:00:00 02/03/2015 07:00:00 DISP SPEC V PLONGEUR

Ceci est la requête que j'édite, beaucoup d'agents mais avec des heures de début et de fin différentes. Mon but est de dire combien j'avais d'agents le 01/01/2015 de 16 h, puis de 16 h à 16 h 30 ...etc .

Cordialement.
03/03/2015, 15h38
Lyche

Citation:

Envoyé par mikedavem

C'est la raison pour laquelle je lui demandais de préciser un peu plus le contexte :-)

En fonction de ce qui est à faire l'approche peut être différente .. générer dynamiquement des lignes horaires est envisageable pour un jeu de données relativement faible ...
S'il doit générer ceci pour un nombre de lignes important (et j'en suis à peu près sûr ici) , ca sera une autre histoire :-)
++

Oui, pour le côté perf, je suis totalement d'accords. Après, il n'est pas trop difficile de reprendre la CTE pour insérer dans une table une plus grand quantité de données.

OU alors une table d'heures avec un petit produit cartésien sur une dimDate pourrait fonctionner aussi. Dans tous les cas, il faut un jeu de données pour au moins une journée ;)
03/03/2015, 16h19
Taz 31

Eclater des heures.

J'ai du mal à comprendre votre charabia vu que cela fait qu'un peu plus d'un an que je fait des stats avec SQL server mais en gros c'est bien cela, ce calcul se ferait sur une journée avec un récupération données à j-1

Pour être honnête je serais parti directement sur une table de calendrier avec un ensemble de dates prédéfini.
Pour la solution on peut effectivement utiliser ta CTE Lyche en gardant à l'esprit qu'on sera limité par la valeur max du paramètre MAXRECURSION (32 767) si on veut générer un grand nombre de lignes d'un seul coup.
Pour le cas particulier 23:59:59 on pourrait modifier ta CTE ainsi:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 DECLARE @DateEntree DateTime = '2001-01-01' --Date de début de génération , @DateFin DateTime SET @DateFin = DATEADD( DD, 2, @DateEntree ); select @DateFin, @DateEntree ;WITH DateCTE AS ( SELECT CASE WHEN CAST(@DateEntree AS TIME) = '00:00:00' THEN DATEADD(SS, 59, DATEADD(MI,-1,@DateEntree)) ELSE @DateEntree END AS DateValue UNION ALL SELECT CASE WHEN CAST(DATEADD(MI,30,DateValue) AS TIME) = '00:00:00' THEN DATEADD(SS, 59, DATEADD(MI,29,DateValue)) WHEN CAST(DATEADD(MI,30,DateValue) AS TIME) = '00:29:59' THEN DATEADD(SS, -59, DATEADD(MI,31,DateValue)) ELSE DATEADD(MI,30,DateValue) END FROM DateCTE WHERE DATEADD(MI,30,DateValue) <= @DateFin ) select * from DateCTE
++

03/03/2015, 16h29
Lyche

La requête que j'ai posté au dessus permet de générer, de façon dynamique, l'ensemble des données horaires par tranche de 30minutes pour une journée saisie en paramètre.

Ce que nous évoquions (plutôt ce que mikedavem me faisait remarquer) c'est que le code en question peut poser des problèmes de performance si les données sont trop nombreuses à générer. Ce à quoi j'ai répondu qu'une table avec la liste des heures d'une journée pouvait être envisageable.

derrière, tu parles d'une simple sortie de 24h, donc je pense que ça peut suffir. Cependant, gardes toujours en tête, que passer par une table te faciliterait probablement la maintenance d'un code que tu ne maitrises apparemment pas encore :)

Cordialement,

Bonjour,

Pour générer dynamiquement les données, on peut aussi s'appuyer sur les tables systèmes, (ou sur n'importe quelle table en fait)
C'est souvent plus performant qu'une récursive :
Code:

1 2 3 4 5 6 7 8 9 ;WITH CTE AS ( SELECT TOP(DATEDIFF(MINUTE, @DateEntree, @DateFin) / 30) DATEADD(MINUTE, 30 * (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) - 1), @DateEntree) AS DTE FROM sys.objects A, sys.objects B ) SELECT DTE FROM CTE

03/03/2015, 17h55
Lyche

Le plan d'exécution des deux requêtes ne te donne pas raison sur le coût de ma CTE et de ta requête ;)

1 pièce(s) jointe(s)

Citation:

Le plan d'exécution des deux requêtes ne te donne pas raison sur le coût de ma CTE et de ta requête

Attention à l'estimation faite par l'optimiseur pour une CTE récursive qui n'est pas fiable. Par conséquent la comparaison des plans d'exécution ici risque de ne pas être pertinente.

Une 3ème solution sur base ensembliste (le top est à adapter avec le nombre de lignes final) :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 ;with counter_calendar as ( select 0 as low union all select top 18000 row_number() over (order by (select 0)) from master..spt_values as t cross join master..spt_values as t2 where t.[type] = N'P' ) select CASE WHEN CAST(DATEADD(MI,30 * low ,@DateEntree) AS TIME) = '00:00:00' THEN DATEADD(MI, -1, DATEADD(MI,30 * low ,@DateEntree)) ELSE DATEADD(MI,30 * low ,@DateEntree) END AS calendar_entry from counter_calendar where DATEADD(MI,30 * low ,@DateEntree) <= @DateFin order by low
Sur un petit jeu de données comme je disais, la différence n'est pas notable. En revanche si on augmente le nombre d'entrées à générer, par exemple sur 365 jours soit plus de 17000 entrées :

Pièce jointe 170710

++

04/03/2015, 09h29
Lyche

En effet, merci pour la démo!!

Cordialement,
20/08/2015, 12h19
Taz 31

Eclater des heures.

Je suis désolé mais je ne comprends pas votre raisonnement par % à ma table constitué de :
un matricule
une ID_Centre,
un DH_Debut au format jj/mm/aa hh:mm:ss,
un DH_Fin au format jj/mm/aa hh:mm:ss,
un état planning

Cela fait 2 ans environ que je fais du SQL SERVER, mais là, je reconnais que je cale.
Je ne sais pas où placer mon DH_Debut et mon DH_Fin dans la requête par % au requête que vous m'avez envoyé, surtout que je n'ai pas appris comment déclarer les variables. Par contre, je comprend finalement, que ce n'est pas le nombre d'agents que je vais compter mais le nombre de tranche horaire de 30 mn où un agent apparait. Est-ce que mon raisonnement est correct, sachant que le tableau que je vais généré, devra faire apparaitre 48 colonnes corespondant à mes différentes tranches pour autant de lignes pour chaque jour du mois.

Cordialement.

Taz

Bonjour,

Ce devrait être quelque chose dans ce style :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH
	CTE AS
	(
			SELECT	DATEADD(day, -1, CAST(CAST(GETDATE() AS date) AS datetime)) AS start_date
				, DATEADD(minute, 30, DATEADD(day, -1, CAST(CAST(GETDATE() AS date) AS datetime))) AS end_date
		UNION ALL
			SELECT	DATEADD(minute, 30, start_date)
				, DATEADD(minute, 30, end_date)
			FROM	CTE
			WHERE	end_date < CAST(GETDATE() AS date)
	)
SELECT		C.start_date
		, C.end_date
		, COUNT(*) AS monAgregation
FROM		CTE AS C
INNER JOIN	dbo.VotreTable AS VT
			ON VT.DH_Debut >= C.start_date
			AND VT.DH_Fin < C.end_date
GROUP BY	C.start_date, C.end_date
ORDER BY	C.start_date, C.end_date

@++ ;)