Discussion :

[jubourbon]

Bonjour à tous,

J'ai une table avec des données datées. ex:
1 donnée1 31/08/2010
2 donnee2 31/08/2010

je voudrais faire une requête qui me retourne le nombre d'enregistrement par date, mais qui me retourne 0 si il n'y a pas d'enregistrement.
ex:
1 donnée1 31/08/2010
2 donnee2 31/08/2010
3 donnée3 02/09/2010
4 donnee4 03/09/2010

résultat:
31/08/2010 2
01/09/2010 0
02/09/2010 1
03/09/2010 1

Pour le moment ma requête ressemble à cela:

Code :

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

1
2
3
select date, count(id) 
from matable 
group by date

Merci d'avance pour votre aide.

[elsuket]

Bonjour,

Je considère que vous travaillez sous SQL Server 2005 ou 2008.
Considérons la table suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE TABLE dbo.jubourbon
(
	valeur tinyint
	, data varchar(16)
	, date_valeur date
)
GO
 
INSERT INTO dbo.jubourbon VALUES (1, 'donnée1', '20100831')
INSERT INTO dbo.jubourbon VALUES (2, 'donnée2', '20100831')
INSERT INTO dbo.jubourbon VALUES (3, 'donnée3', '20100902')
INSERT INTO dbo.jubourbon VALUES (3, 'donnée4', '20100903')
GO

La requête dont vous avez besoin nécessite une expression de table commune récursive qui calcule toutes les dates de tous les jours compris entre la plus petite et la plus grande date de votre table.
Il suffit ensuite de faire une demi-jointure, et on peut écrire :

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
;WITH 
	CTE AS
	(
			SELECT	MIN(date_valeur) AS min_date_valeur
				, MAX(date_valeur) AS max_date_valeur
			FROM	dbo.jubourbon
		UNION ALL
			SELECT	DATEADD(day, 1, min_date_valeur)
				, max_date_valeur
			FROM	CTE
			WHERE	min_date_valeur < max_date_valeur
	)
SELECT		C.min_date_valeur AS date_valeur
		, SUM
		(
			CASE
				WHEN J.date_valeur IS NULL THEN 0
				ELSE 1
			END
		) AS occurences
FROM		CTE AS C
LEFT JOIN	dbo.jubourbon AS J ON C.min_date_valeur = J.date_valeur
GROUP BY	C.min_date_valeur

Qui donne le résultat escompté

@++

[SQLpro]

Lourdingue nicolas !!!!!, voici plus simple :

Code :

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

1
2
3
CREATE TABLE matable
("date" DATE,
 id     INT)

Code :

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

1
2
3
4
5
6
7
8
9
WITH 
T AS (SELECT "date", count(id) AS N
      FROM   matable
      GROUP  BY "date")
SELECT *
FROM   T
UNION ALL
SELECT NULL, 0
WHERE  NOT EXISTS(SELECT * FROM T)

A +

[jubourbon]

Merci pour cette super réponse,

Cependant je n'obtient pas tt à fait le bon résultat. Je souhaite effectuer cette manipulation seulement sur les enregistrement de la dernière semaine passée. Donc je fais la chose suivante:

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
 
 
;WITH 
	CTE AS
	(
			SELECT	MIN(date_valeur) AS min_date_valeur
				, getdate() AS max_date_valeur
			FROM	dbo.jubourbon
                 where date_valeur>dateadd(day,-7,getdate())
		UNION ALL
			SELECT	DATEADD(day, 1, min_date_valeur)
				, max_date_valeur
			FROM	CTE
			WHERE	min_date_valeur < max_date_valeur
	)
SELECT		C.min_date_valeur AS date_valeur
		, SUM
		(
			CASE
				WHEN J.date_valeur IS NULL THEN 0
				ELSE 1
			END
		) AS occurences
FROM		CTE AS C
LEFT JOIN	dbo.jubourbon AS J ON C.min_date_valeur = J.date_valeur
GROUP BY	C.min_date_valeur

Dans ce cas j'obtiens bien un tableau avec tous les jours mais par contre seul la première date à la valeur 1 et le reste 0.

Je ne comprend pas trop pourquoi.

[jubourbon]

Merci pour ta réponse, mais je n'obtiens pas tt a fait ce que je cherche.

Code :

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

1
2
3
4
5
6
7
8
9
WITH 
T AS (SELECT "date", count(id) AS N
      FROM   matable
      GROUP  BY "date")
SELECT *
FROM   T
UNION ALL
SELECT NULL, 0
WHERE  NOT EXISTS(SELECT * FROM T)

Cette requête ne m'affiche pas le 01/09 avec la valeur 0, elle n'affiche que les date avec un enregistrement.

[jubourbon]

Je ne comprend pas le select null,0

[SQLpro]

Commencez par respecter la charte de postage. Il est impossible de vous aider dans le vague.

http://www.developpez.net/forums/d96...vement-poster/

Postez le DDL et les INSERT de vos tables/exemples ainsi que la réponse demandée.

A +

[jubourbon]

Ok, désolé, je pensais que ma première description était assez réaliste et claire. Donc je reprend:

Voici ma table emails:

Code :

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

1
2
3
4
5
6
7
CREATE TABLE [dbo].[emails](
	[modifiedon] [datetime] NULL,
	[modifiedbyname] [varchar](50) NULL
)CREATE TABLE [dbo].[emails](
	[modifiedon] [datetime] NULL,
	[modifiedbyname] [varchar](50) NULL
)

Voici un jeu de données:

Code :

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

1
2
3
4
5
INSERT INTO [Test].[dbo].[emails]([modifiedon] ,[modifiedbyname]) VALUES ('2010-03-09','Dupuis, Jean')
INSERT INTO [Test].[dbo].[emails]([modifiedon] ,[modifiedbyname]) VALUES ('2010-02-09','Dupuis, Jean')
INSERT INTO [Test].[dbo].[emails]([modifiedon] ,[modifiedbyname]) VALUES ('2010-02-09','Dupuis, Jean')
INSERT INTO [Test].[dbo].[emails]([modifiedon] ,[modifiedbyname]) VALUES ('2010-02-09','Aloua, Martine')
INSERT INTO [Test].[dbo].[emails]([modifiedon],[modifiedbyname]) VALUES ('2010-31-08','Dupuis, Jean')

Je cherche à obtenir une requête me permettant d'avoir le résultat suivant:
- Le nombre de mails envoyé par 'Dupuis, Jean' par jour depuis 7 jours
- Les jours ou il n'envoie pas de mail je veux un resultat 0 comme suit:

Date Nombre de Mail
31/08/2010 1
01/09/2010 0
02/09/2010 2
03/09/2010 1

Voici ma requête actuel:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
WITH 
TResult AS (SELECT modifiedon, count(*) AS N
      FROM   emails
      where modifiedbyname='Dupuis, Jean'
      and modifiedon>DATEADD(day,-8,getdate())
      GROUP  BY modifiedon)
SELECT *
FROM   TResult
UNION ALL
SELECT NULL,0
WHERE  NOT EXISTS(SELECT * FROM TResult)

Mais comme vous pourrez le tester, je n'arrive pas à avoir 0 le premier septembre.

D'avance merci pour votre aide

[SQLpro]

Vous avez donc fait perdre du temps à tout le monde !!!!!!!!!!!!

A l'avenir pensez à poster l'intégralité de votre demande en respectant la charte de postage. Il est assez désagréable de tenter d'aider quelqu'un et de perdre du temps pour des conneries.
Mon temps étant facturé 1250 € HT par jour (pour 8h), voyez ce que économiquement vous me gâchez !

D'autant plus que cela change profondément les conditions, car dans ce cas il faut changer de stratégie. En effet dans une BD on ne trouve que ce que l'on y met. En l'occurrence trouver des dates qui n'y figure pas, n'est pas possible de manière simple.
Pour résoudre ce problème, il suffit de rajouter dans votre base une table de toutes les dates comme je l'indique dans cet article : http://sqlpro.developpez.com/cours/gestiontemps/

Dans votre cas :

Code :

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

1
2
3
4
5
6
7
SELECT PJR_DATE AS modifiedon, count(modifiedon) AS N
FROM   emails AS E
       RIGHT OUTER JOIN T_PLN_JOUR_PJR AS D
             ON E.modifiedon = PJR_DATE
                AND modifiedbyname='Dupuis, Jean'
WHERE  PJR_DATE BETWEEN DATEADD(day,-7,getdate()) AND getdate()
GROUP  BY PJR_DATE

Pour faire vos tests :

Code :

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

1
2
3
4
5
6
7
8
create table T_PLN_JOUR_PJR
(   PJR_DATE            DATE                  not null
    constraint PK_T_PLN_JOUR_PJR primary key (PJR_DATE));
GO
INSERT INTO T_PLN_JOUR_PJR 
SELECT COALESCE(DATEADD(day, 1, MAX(PJR_DATE)), '2010-01-01')
FROM   T_PLN_JOUR_PJR;
GO 1000

A +

[jubourbon]

Merci pour ces informations.

J'avais déjà pensé à cette stratégie auparavant, malheureusement, je n'ai pas les droits pour créer ou modifier les tables de ma base.
En revanche, un calendrier contient toutes les dates. Dans la méthode que propose elsuket il me semble qu'on parcourt toutes les dates, mais je ne parviens pas à l'appliquer avec les conditions que je précise dans mon precedent post.

[SQLpro]

J'avais déjà pensé à cette stratégie auparavant, malheureusement, je n'ai pas les droits pour créer ou modifier les tables de ma base.

Aucune importance, vous pouvez sans doute créer une autre base et mettre cette table dedans, voire demander à votre DBA de la mettre dans master et vous autoriser à la lire en SELECT !

Ceci dit, sachez que la règle de Codd (fondateur des bases de données) N°9 - Indépendance logique des données précise que l'ajout d'objet dans une base n'a pas lieu de perturber le système (les applis) ce que d'ailleurs tous les développeurs font en ajoutant des tables temporaires de manière stupide alors que des tables persistantes sont beaucoup plus adaptées et performante !
Donc, si c'est la position de votre DBA, c'est idiot. Si c'est celle de votre éditeur, ce sont des ignares !
A lire : http://sqlpro.developpez.com/SGBDR/R...egles_codd.pdf

A +

[jubourbon]

Merci pour vos informations.