IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Recherche d'une valeur par année


Sujet :

Développement SQL Server

  1. #1
    Membre éclairé Avatar de Bernardos
    Homme Profil pro
    Consultant Senior dba sql server & Microsoft Business Intelligence
    Inscrit en
    Avril 2008
    Messages
    332
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Belgique

    Informations professionnelles :
    Activité : Consultant Senior dba sql server & Microsoft Business Intelligence
    Secteur : Conseil

    Informations forums :
    Inscription : Avril 2008
    Messages : 332
    Points : 723
    Points
    723
    Par défaut Recherche d'une valeur par année
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    refyear	NbCollaborateur
    2014	220
    2015	220
    2016	200
    2017	200
    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
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    refyear	NbCollaborateur
    2014	220
    2015	NULL
    2016	200
    2017	NULL
    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
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    refyear	NbCollaborateur
    2014	220
    2016	200
    Merci d'avance pour votre aide

    Cordialement,
    Loïc BERNARD
    Consultant Senior dba sql server & Microsoft Business Intelligence



    Il n'y a jamais de problèmes, il n'y a que des solutions!

  2. #2
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    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 : 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
    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é ?

    @++

  3. #3
    Membre régulier
    Homme Profil pro
    Consultant ERP
    Inscrit en
    Mars 2016
    Messages
    58
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Consultant ERP

    Informations forums :
    Inscription : Mars 2016
    Messages : 58
    Points : 105
    Points
    105
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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+

  4. #4
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    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é.

    @++

  5. #5
    Membre régulier
    Homme Profil pro
    Consultant ERP
    Inscrit en
    Mars 2016
    Messages
    58
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Consultant ERP

    Informations forums :
    Inscription : Mars 2016
    Messages : 58
    Points : 105
    Points
    105
    Par défaut
    Merci !

Discussions similaires

  1. [XL-2007] Recherche d'une position par rapport a une valeur
    Par 0x44-0x43-0x53-0x43 dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 03/02/2010, 12h17
  2. Rechercher une valeur par palier.
    Par zooffy dans le forum Développement
    Réponses: 6
    Dernier message: 09/10/2008, 12h56
  3. afficher une valeur par défault dans la combobox
    Par shadow31 dans le forum MFC
    Réponses: 6
    Dernier message: 09/01/2006, 18h25
  4. [dblookup] afficher une valeur par programmation
    Par let_me_in dans le forum Bases de données
    Réponses: 1
    Dernier message: 11/05/2005, 15h42
  5. [scriplet] affecter une valeur par un bean:write
    Par viena dans le forum Servlets/JSP
    Réponses: 5
    Dernier message: 25/11/2004, 17h29

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo