(SQL Server) Etat des lieux, semaine par semaine

**Kloun** · 13/07/2018, 14h56

Bonjour,
Après un titre pas très explicite, voici mon problème.
J'ai une table Projets/DateHisto. Dans cette table sont stockées des projets. Ces projets ont un statut.
A chaque mise à jours, une nouvelle ligne est créée avec une nouvelle DateHisto.
Donc, pour un même projet, il peut y avoir plusieurs lignes :

P1 - 04/05/2017 - ...
P1 - 05/05/2017 - ...
P1 - 10/07/2018 - ...
P2 - 25/06/2018 - ...
P3 - 02/10/2016 - ...

Je dois sortir le nombre de projet par statut, semaine par semaine et je dois avouer que je ne sais pas trop comment m'y prendre.
Des suggestions ?
Merci.

**aieeeuuuuu** · 13/07/2018, 15h11

Bonjour,

Regardez du coté de GROUP BY avec un COUNT

**Kloun** · 13/07/2018, 15h28

J'ai pas été assez clair

Dans mon exemple précédent, je veux avoir :
P3 - 02/10/2016 - ...
P1 - 04/05/2017 - ...
P1 - 05/05/2017 - ...
P2 - 25/06/2018 - ...
P1 - 10/07/2018 - ...

0 pour les semaines < 2016-40 (aucun projet)
1 pour les semaines 2016-40 => 2017-18 (P3)
2 pour les semaines 2017-19 => 2018-25 (P3 et P1)
3 ensuite (P3, P1 et P2)
(Il faut que j'ai des résultats même pour les semaines pour lesquelles je n'ai pas de données.)

**al1_24** · 13/07/2018, 15h32

Quel est ton besoin exact ?
Pour une semaine donnée, connaître les projets qui ont changé de statut durant cette semaine ou le dernier statut connu de chaque projet à cette période (d'ailleurs, début ou fin de semaine ?) ?

Envoyé par Kloun

...(Il faut que j'ai des résultats même pour les semaines pour lesquelles je n'ai pas de données.)

Cela implique l'usage d'une table calendrier pour connaître toutes les semaines. Le SGBD ne peut pas les inventer.

**Kloun** · 13/07/2018, 15h36

Semaine par semaine, connaître le nombre de projet.
En gros, il faut que je remplisse les trous (avec une vue ? en alimentant une table ? tout est ouvert) avec les valeurs connues pour un projet avant le "trou".

Edit : pas de problème pour créer une table calendrier (avec jour, semaine, mois, année)

**escartefigue** · 13/07/2018, 16h39

Bonjour,

A traduire en SQL server que je n'ai pas sous la main (notamment les fonctions de dates à adapter), mais le principe fonctionne :

-1- création d'un calendrier des semaines via une requête récursive :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
   with CTE1 (NOSEQ, DTLUN, DTDIM, NOSEM) as                        
       (select 0                                                    
             , date('2016-01-04')                                   
             , date('2016-01-10')                                   
             , '2016-01'                                            
        union all                                                   
        select NOSEQ+1                                              
             , date(DTLUN)+7 days                                   
             , date(DTDIM)+7 days                                   
             , extract(year from date(DTLUN)+7 days)                
               !!'-'!!                                              
               substr(digits(week_ISO(date(DTLUN)+7 DAYS)), 9, 2)   
        from  CTE1                                                  
        where NOSEQ < 207)

Ce qui donne (extrait) :

Code :

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

1
2
3
4
5
6
7
8
   0 2016-01-04 2016-01-10 2016-01
   1 2016-01-11 2016-01-17 2016-02
   2 2016-01-18 2016-01-24 2016-03
   3 2016-01-25 2016-01-31 2016-04
   4 2016-02-01 2016-02-07 2016-05
   5 2016-02-08 2016-02-14 2016-06
   6 2016-02-15 2016-02-21 2016-07
. . .

- 2- création du jeu d'essai

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
     , CTE2 (PROJ, DTHIST, STAT) AS   
      (select 'P1', '2017-05-04', 0   
       union all                      
       select 'P1', '2017-05-05', 1   
       union all                      
       select 'P1', '2018-07-10', 2   
       union all                      
       select 'P2', '2018-06-25', 1   
       union all                      
       select 'P3', '2016-02-10', 0   
       union all                      
       select 'P3', '2017-05-03', 0   
      )

On obtient le résultat conforme à votre attente :

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
NOSEM                   £1 
2016-01                  0 
2016-02                  0 
2016-03                  0 
2016-04                  0 
2016-05                  0 
2016-06                  1 
2016-07                  0 
. . .
2017-17                  0
2017-18                  3
2017-19                  0
. . .
2018-25                  0
2018-26                  1
2018-27                  0
2018-28                  1
. . .

**Kloun** · 13/07/2018, 16h57

Semaine 2016-07, je devrais avoir 1 puisque le projet P3 n'a pas eu d'update donc pas de changement de statut mais il existe toujours ...
C'est là qu'est l'os, hélas.

**escartefigue** · 13/07/2018, 18h56

Alors c'est que je n'ai pas compris votre besoin, je dois avouer que ce n'est pas très clair

**JeitEmgie** · 22/07/2018, 20h26

Le query serait plus simple à écrire si vous aviez (project, status, start_date, end_date)
or vous pouvez transformer vos données en utilisant

Code :

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

1
2
3
4
SELECT project, dthist as start_date, status,
lead(dthist, 1, (select max(dthist)+7 from data_source) over (PARTITION BY project ORDER BY dthist) AS end_date
from data_source

En combinant plusieurs CTE:
1 pour mettre les données en (project, status, start_date, end_date)
1 pour avoir tous les distinct status
1 pour générer tous les couples year,week entre la date min et la date max présentes dans le data source + une date de début ou fin de la semaine (à vous de voir ce qui vous convient)
1 pour faire le cross product entre tous les status et tous les triples year, week, test_date du précédent CTE
et à la fin vous n'avez plus qu'à faire le select year, week, status, count(data_source.project) sur le dernier CTE avec un LEFT JOIN sur le data_source avec une condition

Code :

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

1
2
test_date BETWEEN  data_source.start_date AND  data_source.end_date AND data_source.status = latest_cte.status

**aieeeuuuuu** · 21/08/2018, 14h52

Bonjour,

Essayez comme ceci :

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 Projet (id, dte, statut) AS (
     select 'P3', cast('20161002' AS date), 'analyse'
union all select 'P1', '20170504', 'analyse'
union all select 'P1', '20170505', 'developpement'
union all select 'P2', '20170625', 'analyse'
union all select 'P1', '20170710', 'test'
),
cal(jour) AS (
   SELECT  cast('20161001' AS date) AS jour
   UNION ALL
   SELECT dateadd(week, 1, jour)
   FROM cal
   WHERE jour <= '20170710' 
), tmp AS (
SELECT YEAR(jour) AS annee, datepart(week, jour) AS semaine, statut,id, ROW_NUMBER() OVER(PARTITION BY jour, id ORDER BY dte DESC) AS Rn
FROM cal 
LEFT JOIN projet
    ON projet.dte <= cal.jour
)
SELECT annee, semaine, statut, COUNT(id) AS NbProjet
FROM Tmp
WHERE Rn = 1
GROUP BY annee, semaine, statut
ORDER BY annee, semaine, statut

(SQL Server) Etat des lieux, semaine par semaine

Langage SQL

Discussions similaires

Partager

Partager