Discussion :

[Taz 31]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[mikedavem]

Hello,

Quel est le besoin?

++

[Taz 31]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SQL SERVER

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

Taz

[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

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.

++

[Lyche]

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

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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,

[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 :-)
++

[Taz 31]

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.

[Lyche]

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

[Taz 31]

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

[mikedavem]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

++

[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,

[aieeeuuuuu]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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

[mikedavem]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :



++

[Lyche]

En effet, merci pour la démo!!

Cordialement,

[Taz 31]

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

[elsuket]

Bonjour,

Ce devrait être quelque chose dans ce style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

@++