Calcul d'un minimum sur un minimum d'un délai ??

Le
olivier
Bonjour,
j'ai une table COURS (ID, MATIERES _ID, ELEVE_ID)
une table ELEVE (ID, NOM)
une table MATIERES (ID, LIBELLE)

j'ai pour chaque ELEVE un historique des COURS qu'il a suivit au cours de
l'année

j'aimerai avoir pour chaque type de MATIERES :

le délai minimum (en jours) entre le 1er et le dernier COURS
le délai moyen (en jours) entre le 1er et le dernier COURS
le délai maximum (en jours) entre le 1er et le dernier COURS

ex :

MATIERES MIN MOY MAX

1- ANGLAIS 10 20 30
2- MATHS 20 40 60
3- GEOGRAPHIE 2 9 18

merci car là, je sèche

Fichier de creation ci-dessous.


olivier.

USE [master]
GO
/****** Objet : Database [F_COLLEGE] Date de génération du script :
05/30/2008 11:28:18 ******/
CREATE DATABASE [F_COLLEGE] ON PRIMARY
( NAME = N'F_COLLEGE', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAF_COLLEGE.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'F_COLLEGE_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAF_COLLEGE_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'F_COLLEGE', @new_cmptlevel
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [F_COLLEGE].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO

/****** Objet : Table [dbo].[ELEVES] Date de génération du script :
05/30/2008 11:28:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ELEVES](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_ELEVES] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

/****** Objet : Table [dbo].[MATIERES] Date de génération du script :
05/30/2008 11:29:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MATIERES](
[id] [int] IDENTITY(1,1) NOT NULL,
[libelle] [nchar](100) NULL,
CONSTRAINT [PK_MATIERES] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


/****** Objet : Table [dbo].[COURS] Date de génération du script :
05/30/2008 11:29:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[COURS](
[id] [int] IDENTITY(1,1) NOT NULL,
[matiere_id] [int] NULL,
[eleve_id] [int] NULL,
[date_cours] [datetime] NULL,
CONSTRAINT [PK_COURS] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


USE F_COLLEGE

DECLARE @DATE_COURS DATETIME
SET @DATE_COURS = GETDATE()

INSERT INTO ELEVES (nom) VALUES ('PIERRE')
INSERT INTO ELEVES (nom) VALUES ('PAUL')
INSERT INTO ELEVES (nom) VALUES ('JACQUES')

INSERT INTO MATIERES (libelle) VALUES ('ANGLAIS')
INSERT INTO MATIERES (libelle) VALUES ('MATHS')
INSERT INTO MATIERES (libelle) VALUES ('GEO')

INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,1,DATEADD(DAY,1,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,1,DATEADD(DAY,3,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(2,1,DATEADD(DAY,6,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(2,1,DATEADD(DAY,10,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,2,DATEADD(DAY,25,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,2,DATEADD(DAY,12,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,1,DATEADD(DAY,14,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(3,3,DATEADD(DAY,34,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,3,DATEADD(DAY,102,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(3,1,DATEADD(DAY,19,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,2,DATEADD(DAY,36,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(3,1,DATEADD(DAY,15,@DATE_COURS))
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
Philippe Bouchardy
Le #11879361
Bonjour Olivier,



Je suis parti des hypothèses suivantes :



L'écart min est exprimé en jour et correspond à l'écart minimal entre la
date du 1er cours et la date du dernier cours d'un élève par matière.

L'écart max est exprimé en jour et correspond à l'écart maximal entre la
date du 1er cours et la date du dernier cours d'un élève par matière.

L'écart moyen est la somme des écarts entre la date du 1er cours et la date
du dernier cours des élèves d'une matière divisé par le nombre d'élèves de
la matiére.

Les élèves n'ayant pris qu'un seul cours sont exclus du résultat




Je vous propose la requête suivante :



select tab1.matiere_id,
min(datediff(day, tab1.cd, tab1.cf)) as mindelay,
max(datediff(day, tab1.cd, tab1.cf)) as maxdelay,
cast(sum(datediff(day, tab1.cd, tab1.cf)) / cast(count(*) as
decimal(9,2)) as decimal(9,2)) as avgdelay
from (select matiere_id, eleve_id, min(date_cours) as cd, max(date_cours)
as cf, count(*) as nb
from cours
group by matiere_id, eleve_id
having max(date_cours) <> min(date_cours)) tab1
group by matiere_id



En esperant être dans les cordes...



Cordialement,



Philippe


"olivier" news:
Bonjour,
j'ai une table COURS (ID, MATIERES _ID, ELEVE_ID)
une table ELEVE (ID, NOM)
une table MATIERES (ID, LIBELLE)

j'ai pour chaque ELEVE un historique des COURS qu'il a suivit au cours de
l'année

j'aimerai avoir pour chaque type de MATIERES :

le délai minimum (en jours) entre le 1er et le dernier COURS
le délai moyen (en jours) entre le 1er et le dernier COURS
le délai maximum (en jours) entre le 1er et le dernier COURS

ex :

MATIERES MIN MOY MAX

1- ANGLAIS 10 20 30
2- MATHS 20 40 60
3- GEOGRAPHIE 2 9 18

merci car là, je sèche...

Fichier de creation ci-dessous.


olivier.

USE [master]
GO
/****** Objet : Database [F_COLLEGE] Date de génération du script :
05/30/2008 11:28:18 ******/
CREATE DATABASE [F_COLLEGE] ON PRIMARY
( NAME = N'F_COLLEGE', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAF_COLLEGE.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'F_COLLEGE_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAF_COLLEGE_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'F_COLLEGE', @new_cmptlevel
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [F_COLLEGE].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO

/****** Objet : Table [dbo].[ELEVES] Date de génération du script :
05/30/2008 11:28:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ELEVES](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_ELEVES] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

/****** Objet : Table [dbo].[MATIERES] Date de génération du script :
05/30/2008 11:29:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MATIERES](
[id] [int] IDENTITY(1,1) NOT NULL,
[libelle] [nchar](100) NULL,
CONSTRAINT [PK_MATIERES] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


/****** Objet : Table [dbo].[COURS] Date de génération du script :
05/30/2008 11:29:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[COURS](
[id] [int] IDENTITY(1,1) NOT NULL,
[matiere_id] [int] NULL,
[eleve_id] [int] NULL,
[date_cours] [datetime] NULL,
CONSTRAINT [PK_COURS] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


USE F_COLLEGE

DECLARE @DATE_COURS DATETIME
SET @DATE_COURS = GETDATE()

INSERT INTO ELEVES (nom) VALUES ('PIERRE')
INSERT INTO ELEVES (nom) VALUES ('PAUL')
INSERT INTO ELEVES (nom) VALUES ('JACQUES')

INSERT INTO MATIERES (libelle) VALUES ('ANGLAIS')
INSERT INTO MATIERES (libelle) VALUES ('MATHS')
INSERT INTO MATIERES (libelle) VALUES ('GEO')

INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,1,DATEADD(DAY,1,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,1,DATEADD(DAY,3,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(2,1,DATEADD(DAY,6,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(2,1,DATEADD(DAY,10,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,2,DATEADD(DAY,25,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,2,DATEADD(DAY,12,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,1,DATEADD(DAY,14,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(3,3,DATEADD(DAY,34,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,3,DATEADD(DAY,102,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(3,1,DATEADD(DAY,19,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,2,DATEADD(DAY,36,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(3,1,DATEADD(DAY,15,@DATE_COURS))




olivier
Le #11879311
ouah !

bravo et merci.

olivier

"Philippe Bouchardy" news: %
Bonjour Olivier,



Je suis parti des hypothèses suivantes :



L'écart min est exprimé en jour et correspond à l'écart minimal entre la
date du 1er cours et la date du dernier cours d'un élève par matière.

L'écart max est exprimé en jour et correspond à l'écart maximal entre la
date du 1er cours et la date du dernier cours d'un élève par matière.

L'écart moyen est la somme des écarts entre la date du 1er cours et la
date du dernier cours des élèves d'une matière divisé par le nombre
d'élèves de la matiére.

Les élèves n'ayant pris qu'un seul cours sont exclus du résultat




Je vous propose la requête suivante :



select tab1.matiere_id,
min(datediff(day, tab1.cd, tab1.cf)) as mindelay,
max(datediff(day, tab1.cd, tab1.cf)) as maxdelay,
cast(sum(datediff(day, tab1.cd, tab1.cf)) / cast(count(*) as
decimal(9,2)) as decimal(9,2)) as avgdelay
from (select matiere_id, eleve_id, min(date_cours) as cd, max(date_cours)
as cf, count(*) as nb
from cours
group by matiere_id, eleve_id
having max(date_cours) <> min(date_cours)) tab1
group by matiere_id



En esperant être dans les cordes...



Cordialement,



Philippe


"olivier" news:
Bonjour,
j'ai une table COURS (ID, MATIERES _ID, ELEVE_ID)
une table ELEVE (ID, NOM)
une table MATIERES (ID, LIBELLE)

j'ai pour chaque ELEVE un historique des COURS qu'il a suivit au cours de
l'année

j'aimerai avoir pour chaque type de MATIERES :

le délai minimum (en jours) entre le 1er et le dernier COURS
le délai moyen (en jours) entre le 1er et le dernier COURS
le délai maximum (en jours) entre le 1er et le dernier COURS

ex :

MATIERES MIN MOY MAX

1- ANGLAIS 10 20 30
2- MATHS 20 40 60
3- GEOGRAPHIE 2 9 18

merci car là, je sèche...

Fichier de creation ci-dessous.


olivier.

USE [master]
GO
/****** Objet : Database [F_COLLEGE] Date de génération du script :
05/30/2008 11:28:18 ******/
CREATE DATABASE [F_COLLEGE] ON PRIMARY
( NAME = N'F_COLLEGE', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAF_COLLEGE.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'F_COLLEGE_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAF_COLLEGE_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'F_COLLEGE', @new_cmptlevel
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [F_COLLEGE].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO

/****** Objet : Table [dbo].[ELEVES] Date de génération du script :
05/30/2008 11:28:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ELEVES](
[id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](100) NULL,
CONSTRAINT [PK_ELEVES] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

/****** Objet : Table [dbo].[MATIERES] Date de génération du script :
05/30/2008 11:29:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MATIERES](
[id] [int] IDENTITY(1,1) NOT NULL,
[libelle] [nchar](100) NULL,
CONSTRAINT [PK_MATIERES] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


/****** Objet : Table [dbo].[COURS] Date de génération du script :
05/30/2008 11:29:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[COURS](
[id] [int] IDENTITY(1,1) NOT NULL,
[matiere_id] [int] NULL,
[eleve_id] [int] NULL,
[date_cours] [datetime] NULL,
CONSTRAINT [PK_COURS] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


USE F_COLLEGE

DECLARE @DATE_COURS DATETIME
SET @DATE_COURS = GETDATE()

INSERT INTO ELEVES (nom) VALUES ('PIERRE')
INSERT INTO ELEVES (nom) VALUES ('PAUL')
INSERT INTO ELEVES (nom) VALUES ('JACQUES')

INSERT INTO MATIERES (libelle) VALUES ('ANGLAIS')
INSERT INTO MATIERES (libelle) VALUES ('MATHS')
INSERT INTO MATIERES (libelle) VALUES ('GEO')

INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,1,DATEADD(DAY,1,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,1,DATEADD(DAY,3,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(2,1,DATEADD(DAY,6,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(2,1,DATEADD(DAY,10,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,2,DATEADD(DAY,25,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,2,DATEADD(DAY,12,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,1,DATEADD(DAY,14,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(3,3,DATEADD(DAY,34,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,3,DATEADD(DAY,102,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(3,1,DATEADD(DAY,19,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(1,2,DATEADD(DAY,36,@DATE_COURS))
INSERT INTO COURS (matiere_id,eleve_id,date_cours) VALUES
(3,1,DATEADD(DAY,15,@DATE_COURS))








Publicité
Poster une réponse
Anonyme