Recherche d'une valeur par année

Version imprimable

Bonjour,
J'ai une table dasn laquelle on enregistre le nombre de collaborateur à une date donnée.
Voici le script de création et quelques insert

Code:

1
2
3
4
5
6
7
8
9
10
CREATE TABLE [dbo].[EFFECTIF](
	[ID_EFFECTIF] [int] IDENTITY(1,1) NOT NULL,
	[ID_ENTREPRISE] [int] NOT NULL,
	[NbCollaborateur] [int] NULL,
	[Date_Modification] [datetime] NULL,
 CONSTRAINT [PK_EFFECTIF] PRIMARY KEY CLUSTERED 
(
	[ID_EFFECTIF] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Code:

1
2
3
4
5
6
7
INSERT INTO EFFECTIF ([ID_EFFECTIF], [ID_ENTREPRISE], [NbCollaborateur], [Date_Modification]) VALUES (1, 5, 100, 'Jan 10 2000 12:00AM')
INSERT INTO EFFECTIF ([ID_EFFECTIF], [ID_ENTREPRISE], [NbCollaborateur], [Date_Modification]) VALUES (2, 5, 110, 'Mar 10 2014 12:00AM')
INSERT INTO EFFECTIF ([ID_EFFECTIF], [ID_ENTREPRISE], [NbCollaborateur], [Date_Modification]) VALUES (3, 5, 220, 'Nov 11 2014 12:00AM')
INSERT INTO EFFECTIF ([ID_EFFECTIF], [ID_ENTREPRISE], [NbCollaborateur], [Date_Modification]) VALUES (4, 5, 150, 'Jan  1 2016 12:00AM')
INSERT INTO EFFECTIF ([ID_EFFECTIF], [ID_ENTREPRISE], [NbCollaborateur], [Date_Modification]) VALUES (5, 5, 110, 'May  1 2016 12:00AM')
INSERT INTO EFFECTIF ([ID_EFFECTIF], [ID_ENTREPRISE], [NbCollaborateur], [Date_Modification]) VALUES (6, 5, 90, 'Dec  1 2016 12:00AM')
INSERT INTO EFFECTIF ([ID_EFFECTIF], [ID_ENTREPRISE], [NbCollaborateur], [Date_Modification]) VALUES (7, 5, 200, 'Dec 31 2016 12:00AM')

ce qui donne le jeu de données suivant

Code:

1
2
3
4
5
6
7
8
ID_EFFECTIF	ID_ENTREPRISE	NbCollaborateur	Date_Modification
1	5	100	2000-01-10 00:00:00.000
2	5	110	2014-03-10 00:00:00.000
3	5	220	2014-11-11 00:00:00.000
4	5	150	2016-01-01 00:00:00.000
5	5	110	2016-05-01 00:00:00.000
6	5	90	2016-12-01 00:00:00.000
7	5	200	2016-12-31 00:00:00.000

Je dois obtenir pour une entreprise donnée (ici je n'en ai créé qu'une donc ce sera la 5) pour les 4 dernières années la dernière valeur pour nbcollaborateur.
En sus, si une année il n'y a pas de nbcollateur qui a été encodé, j'ai besoin d'obtenir la dernière valeur non null.
Ce qui dans mon jeu de donnée donnerait

Code:

1
2
3
4
5
refyear	NbCollaborateur
2014	220
2015	220
2016	200
2017	200

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
With REFYEAR (refyear) as
(
    SELECT year(getdate()) as REF_Year 
	UNION ALL
	SELECT year(getdate())-1 as REF_Year 
	UNION ALL
	SELECT year(getdate())-2 as REF_Year 
	UNION ALL
	SELECT year(getdate()) -3 as REF_Year
)
SELECT refyear, 
       [NbCollaborateur]
FROM (SELECT ID_ENTREPRISE as ID_ENTREPRISE,
             REFYEAR.refyear, 
             [NbCollaborateur],
	         ROW_NUMBER() OVER(PARTITION BY REFYEAR.refyear ORDER BY E.[Date_Modification] DESC) AS rn
      FROM refyear LEFT OUTER JOIN 
           [dbo].[EFFECTIF] E ON year(E.Date_Modification) = refyear.refyear
	  --WHERE ID_ENTREPRISE = 5 
	  ) AS T
where T.rn = 1

me donne

Code:

1
2
3
4
5
refyear	NbCollaborateur
2014	220
2015	NULL
2016	200
2017	NULL

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
With REFYEAR (refyear) as
(
    SELECT year(getdate()) as REF_Year 
	UNION ALL
	SELECT year(getdate())-1 as REF_Year 
	UNION ALL
	SELECT year(getdate())-2 as REF_Year 
	UNION ALL
	SELECT year(getdate()) -3 as REF_Year
)
SELECT refyear, 
       [NbCollaborateur]
FROM (SELECT ID_ENTREPRISE as ID_ENTREPRISE,
             REFYEAR.refyear, 
             [NbCollaborateur],
	         ROW_NUMBER() OVER(PARTITION BY REFYEAR.refyear ORDER BY E.[Date_Modification] DESC) AS rn
      FROM refyear LEFT OUTER JOIN 
           [dbo].[EFFECTIF] E ON year(E.Date_Modification) = refyear.refyear
	  WHERE ID_ENTREPRISE = 5 
	  ) AS T
where T.rn = 1

me donne

Code:

1
2
3
refyear	NbCollaborateur
2014	220
2016	200

Merci d'avance pour votre aide

Cordialement,

Bonjour Bernardos,

Je me suis rajouté quelques lignes pour d'autres entreprises à partir du jeu de données que tu as donné. Voici une requête :

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
29
;WITH
	DATE_RANGE AS
	(
		SELECT	DATEADD(year, DATEDIFF(year, 0, DATEADD(year, -V.v, GETDATE())), 0) AS dt_start
			, DATEADD(year, DATEDIFF(year, 0, DATEADD(year, -V.v, GETDATE())) + 1, 0) AS dt_end
		FROM	(
				VALUES (0), (1), (2), (3)
			) AS V(v)
	)
	, LAST_MODIFICATION AS
	(
		SELECT		E.ID_ENTREPRISE
				, DR.dt_start
				, DR.dt_end
				, MAX(E.Date_Modification) AS last_modification_date
		FROM		DATE_RANGE AS DR
		LEFT JOIN	dbo.EFFECTIF AS E
					ON E.Date_Modification BETWEEN DR.dt_start AND DR.dt_end
		GROUP BY	E.ID_ENTREPRISE, DR.dt_start, DR.dt_end
	)
SELECT		LM.ID_ENTREPRISE
		, LM.dt_start
		, LM.dt_end
		, E.Date_Modification
		, E.NbCollaborateur
FROM		LAST_MODIFICATION AS LM
INNER JOIN	dbo.EFFECTIF AS E
			ON LM.ID_ENTREPRISE = E.ID_ENTREPRISE
			AND LM.last_modification_date = E.Date_Modification

Te donne-t-elle le résultat escompté ?

@++ ;)

Bonjour a tous,

@Bernardos n'y a t'il pas une erreur dans l'exemple ? Pour moi l'effectif de 2015 devrait etre de 220 et non 200 car il n'y a pas eu de mouvement en 2015 et le dernier effectif en 2014 (2014-11-11) etait de 220. Est-ce correct ?

Cette requete me semble repondre au besoin
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 WITH REFYEAR as (SELECT year(DATEADD(year,-V.v,GETDATE())) as REF_Year FROM ( VALUES (0), (1), (2), (3) ) AS V(v)), TMP as ( SELECT REFYEAR.REF_Year, EFFECTIF.ID_EFFECTIF, ROW_NUMBER() OVER (PARTITION BY REFYEAR.REF_Year ORDER BY EFFECTIF.Date_Modification DESC) as OrderLine FROM EFFECTIF JOIN REFYEAR on year(EFFECTIF.Date_Modification) <= REFYEAR.REF_Year ) SELECT TMP.REF_Year, EFFECTIF.NbCollaborateur FROM TMP JOIN EFFECTIF on EFFECTIF.ID_EFFECTIF = TMP.ID_EFFECTIF WHERE OrderLine = 1 ORDER BY TMP.REF_Year
Code:

1 2 3 4 5 6 REF_Year NbCollaborateur 2014 220 2015 220 2016 200 2017 200
Sinon merci Elsuket pour le debut de la requete, je ne connaissais pas la technique du AS V(v) ... si tu peux m'indiquer le nom de cette technique ou mieux un tuto ce sera super cool !

A+

18/01/2017, 14h09
elsuket

Bonjour sqllm,

En fait c'est la clause VALUES que l'on peut utiliser en dehors du contexte d'un INSERT : dans ce cas on dit que c'est un constructeur de valeurs de table (table value constructor en Anglais).
Celle-ci ne permet pas de donner un nom aux colonnes, donc j'ai donné à l'expression de table.

La limite du nombre de lignes que l'on peut spécifier "en dur" est 1000.
Comme c'est une expression de table, ce constructeur est utilisable partout où une table peut être spécifiée : dans l'INSERT bien sûr, les DELETE, les SELECT, les UPDATE, et aussi dans la clause USING de l'instruction MERGE.

J'aime bien ce constructeur car il permet d'éviter les boucles et la récursivité.

@++ ;)
18/01/2017, 14h17
sqllm

Merci !