Champ multiple par ligne avec GROUP BY Year()

**Firlfire** · 30/04/2018, 18h08

Bonjour à tous et toutes !

J'ai une requête à mettre en place et je n'ai aucune idée de comment m'y prendre (exemple via Excel en bas du post):

J'ai une table "Facturation" composé de plusieurs champs :

idFacturation : int
date: datetime
montant : float
cesure1 : float
cesure2 : float
cesure3 : float
cesure4 : float

Ce qui me pose problème sont les césure : en gros ce sont UN pourcentage : le total des 4 est inférieur ou égal à 100, et elle représente la répartition du montant sur plusieurs années
Je souhaite avec une requête grouper le montant par année (dépendant de la date) en tenant compte de ces pourcentage.

Voilà un exemple :
Nom : cesure.PNG
Affichages : 461
Taille : 19,9 Ko

Nom : cesure.PNG
Affichages : 461
Taille : 19,9 Ko

La difficulté c'est que les années sur lesquelles le montant est répartit dépendent de l'année de la date :

Pour les factures 1 et 2, l'année est 2018 donc cesure1 représente le pourcentage du montant à mettre dans 2017, cesure2 représente 2019, cesure3 représente 2020, cesure4 représente 2021
Pour les factures 3, 4 et 5, l'année de la facture 2017, donc cesure1 représente le pourcentage du montant à mettre dans 2017, cesure2 représente 2018, cesure3 représente 2019, cesure4 représente 2020

Dans l'exemple :
- 2017 correspond à : (0% de 650) + 20% de 15000 + 100% de 4850
- 2018 correspond à : 50% de 1500 + 50% de 2000 + 25% de 650 + 25% de 15000 + (0% de 4850)
- 2019 correspond à : 25% de 1500 + 50% de 2000 + 75% de 650 + 20% de 15000 + (0% de 4850)
- 2020 correspond à : 25% de 1500 + (0% de 2000) + (0% de 650) + 35% de 1500 + (0% de 4850)
- 2021 correspond à : (0% de 1500) + (0% de 2000)
Nbr correspond au nombre de lignes de Facturation utiles au calcul (supérieur à 0)

Je sais que je vais avoir besoin d'un GROUP BY Year(date), mais je n'ai aucune idée de comment m'y prendre pour faire cette requête

Quelqu'un peut-il m'aider svp ?

**Waldar** · 30/04/2018, 18h36

Il faut d'abord utiliser UNPIVOT pour remettre vos données dans un sens exploitable, puis décoder les cesures en nombre d'années supplémentaires pour trouver la bonne année de facturation (ou d'amortissement) :

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
create table Facturation
( IdFacturation   integer
, DtFacturation   datetime
, montant         decimal(10,2)
, cesure1         smallint
, cesure2         smallint
, cesure3         smallint
, cesure4         smallint
)
go
 
 
insert into Facturation values
(1, '2018-04-10 09:41:19',  1500,  50, 25, 25,  0),
(2, '2018-04-10 09:41:19',  2000,  50, 50,  0,  0),
(3, '2017-04-10 09:41:19',   650,   0, 25, 75,  0),
(4, '2017-04-10 09:41:19', 15000,  20, 25, 20, 35),
(5, '2017-04-10 09:41:19',  4850, 100,  0,  0,  0)
go
 
  select year(DtFacturation)
       + case cesure
           when 'cesure1' then 0
           when 'cesure2' then 1
           when 'cesure3' then 2
           when 'cesure4' then 3
         end as annee
       , sum(montant * pct) / 100.00 as montant
       , count(case when pct > 0 then 1 end) as nbr
    from Facturation
 unpivot (pct for cesure in (cesure1, cesure2, cesure3, cesure4)) as t
group by year(DtFacturation)
       + case cesure
           when 'cesure1' then 0
           when 'cesure2' then 1
           when 'cesure3' then 2
           when 'cesure4' then 3
         end
order by annee asc
go
 
annee  montant  nbr
-----  -------  ---
 2017     7850    2
 2018     5662    4
 2019     4862    4
 2020     5625    2
 2021        0    0

**soazig** · 01/05/2018, 09h38

Bonjour,
On n'utilise jamais float pour représenter de l'argent car il y a des possibilités d'arrondi, et de perte de centimes ou d'euros, il vaut mieux utiliser des numéric, decimal,ou eventuellement money.
Cela ne t'aidera pas dans ton problème, mais peut-être plus tard.
Cordialement
Soazig

**Firlfire** · 02/05/2018, 14h06

Merci à toi soazig c'est noté

J'ai toujours 2/3 questions :
Dans le Sum() au niveau du premier select, pourquoi je ne peux pas accéder à TOTAL_NO_VAT via data mais seulement par pvt ?

Ai-je bien compris le Pivot/Unpivot :

le 1er select (en gras) désigne ce que l'on souhaite
le from (en gras) permet d'obtenir les données que le pivot/unpivot doit traité
le pivot/unpivot fait "pivoter" les données de ligne à colonne ou colonne en ligne

En fait ma question est plus simplement : pourquoi les champs accessibles via le tout premier select sont ceux de pvt et non pas ceux de pvt et data ?

Code SQL :

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
30
31
select year(D_CREATE)
       + case pvt.cesure
           when 'F_CESURE1' then 0
           when 'F_CESURE2' then 1
           when 'F_CESURE3' then 2
           when 'F_CESURE4' then 3
         end as annee
       , Sum(pvt.TOTAL_NO_VAT * pvt.pct) / 100.00 as montant
       , Count(case when pvt.pct > 0 then 1 end) as nbr
	from 
	(
		select inv.K_DOCUMENT
			  ,inv.D_CREATE
			  ,inv.TOTAL_NO_VAT
			  ,inv.F_CESURE1
			  ,inv.F_CESURE2
			  ,inv.F_CESURE3
			  ,inv.F_CESURE4
				from DOCU$INVOICING as inv
				inner join DOCUMENTS as Docu ON Docu.K_DOCUMENT=inv.K_DOCUMENT
				where Docu.REFERENCE='IPC'
	) data
	unpivot	( pct for cesure in (data.F_CESURE1, data.F_CESURE2, data.F_CESURE3, data.F_CESURE4)) as pvt
	group by year(D_CREATE)
		   + case cesure
			   when 'F_CESURE1' then 0
			   when 'F_CESURE2' then 1
			   when 'F_CESURE3' then 2
			   when 'F_CESURE4' then 3
			 end
	order by annee asc

**Waldar** · 02/05/2018, 15h23

Quand vous utilisez UNPIVOT, vous faites disparaître la / les vues qui sont en dessous... enfin ici au-dessus.
Un peu comme quand vous écrivez :

Code :

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

select * from (select * from table1 as t1) as t2

Dans le select extérieur, vous n'avez plus accès à t1, seulement à t2.

Si on reprend le cas d'UNPIVOT :

Code :

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

select * from table1 as t1 unpivot () as t2

Est équivalent à :

Code :

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

select * from ((select * from table1 as t1) unpivot ()) as t2

Par contre, vous n'êtes pas obligé d'écrire data comme vous l'avez fait, vous pouvez légèrement simplifier la syntaxe de cette façon (uniquement la syntaxe, derrière le moteur fera la même chose) :

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
  select year(D_CREATE)
       + case cesure
           when 'F_CESURE1' then 0
           when 'F_CESURE2' then 1
           when 'F_CESURE3' then 2
           when 'F_CESURE4' then 3
         end as annee
       , Sum(TOTAL_NO_VAT * pct) / 100.00 as montant
       , Count(case when pct > 0 then 1 end) as nbr
    from DOCU$INVOICING as inv
    join DOCUMENTS      as Doc  on Doc.K_DOCUMENT = inv.K_DOCUMENT
                               and Doc.REFERENCE  = 'IPC'
 unpivot (pct for cesure in (F_CESURE1, F_CESURE2, F_CESURE3, F_CESURE4)) as pvt
group by year(D_CREATE)
       + case cesure
           when 'F_CESURE1' then 0
           when 'F_CESURE2' then 1
           when 'F_CESURE3' then 2
           when 'F_CESURE4' then 3
         end
order by annee asc

**Firlfire** · 02/05/2018, 15h54

Je ne parviens pas à faire fonctionner votre requête avec la syntaxe simplifiée :

Msg*8156, Niveau*16, État*1, Ligne*13
La colonne 'K_DOCUMENT' a été spécifiée plusieurs fois pour 'pvt'.

Si je repars de ma précédente requête, j'ai remarqué que j'ai oublié d'inclure une donnée sortante : les dépenses
Le problème c'est qu'elle vient encore d'une autre table (OPPORTUNITIES) et ne dépendent pas directement de la même année :
Nom : cesure.PNG
Affichages : 339
Taille : 30,8 Ko

Nom : cesure.PNG
Affichages : 339
Taille : 30,8 Ko

Dans l'exemple les DOCU$INVOICING et OPPORTUNITIES sont relie par ligne (1 avec 1, 2 avec 2, 3 avec 3, ...). La colonne "depense" du résultat correspond à
- 2017 = somme des F_PLANNEDSPENDING et F_PLANNEDWAGES de la table OPPORTUNITIES où l'année de OPPORTUNITIES.D_CONCLUDE = 2017 (500+500+750+600+1230+900)
- 2018 = somme des F_PLANNEDSPENDING et F_PLANNEDWAGES de la table OPPORTUNITIES où l'année de OPPORTUNITIES.D_CONCLUDE = 2018 (1250+500+2300+2500)
Avec ma requête ces champs sont compté suivant les champs cesure, sauf qu'il ne faut pas.

La requête ci-dessous les sort correctement, mais je n'arrive à la placer au bon endroit :

Code SQL :

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

1
2
3
select Sum(Oppo.F_PLANNEDSPENDING+Oppo.F_PLANNEDWAGES) as depenses
	  		from Opportunities Oppo
	  		group by Year(Oppo.D_CONCLUDE)

Requête actuelle :

Code SQL :

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
30
31
32
33
34
35
select Year(pvt.D_CREATE)
       + case pvt.cesure
           when 'F_CESURE1' then 0
           when 'F_CESURE2' then 1
           when 'F_CESURE3' then 2
           when 'F_CESURE4' then 3
         end as annee
       ,Sum(pvt.TOTAL_NO_VAT * pvt.pct) / 100.00 as montant
	   ,Sum(pvt.F_PLANNEDSPENDING+pvt.F_PLANNEDWAGES) as depense
       ,Count(case when pvt.pct > 0 then 1 end) as nbr
	from 
	(
		select inv.D_CREATE
			  ,inv.TOTAL_NO_VAT
			  ,inv.F_CESURE1
			  ,inv.F_CESURE2
			  ,inv.F_CESURE3
			  ,inv.F_CESURE4
			  ,Oppo.F_PLANNEDSPENDING
			  ,Oppo.F_PLANNEDWAGES
				from Provademse.ADMINPROVADEMSE.DOCU$INVOICING as inv
				inner join DOCUMENTS as Docu ON Docu.K_DOCUMENT=inv.K_DOCUMENT
				inner join DOCU_OPPO as DocuOppo on DocuOppo.K_DOCUMENT=inv.K_DOCUMENT
				inner join OPPORTUNITIES Oppo on DocuOppo.K_OPPORTUNITY=Oppo.K_OPPORTUNITY
				where Docu.REFERENCE='IPC' and Docu.K_DOCUMENT>=240
	) data
	unpivot	(pct for cesure in (data.F_CESURE1, data.F_CESURE2, data.F_CESURE3, data.F_CESURE4)) as pvt 
	group by year(pvt.D_CREATE)
		   + case cesure
			   when 'F_CESURE1' then 0
			   when 'F_CESURE2' then 1
			   when 'F_CESURE3' then 2
			   when 'F_CESURE4' then 3
			 end
	order by annee asc

**Waldar** · 02/05/2018, 16h17

Là vous devez scinder votre code en plusieurs blocs et les rapprocher à la fin :

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
with cte_base_data as
(
select inv.K_DOCUMENT
     , inv.D_CREATE
     , inv.TOTAL_NO_VAT
     , inv.F_CESURE1
     , inv.F_CESURE2
     , inv.F_CESURE3
     , inv.F_CESURE4
  from Provademse.ADMINPROVADEMSE.DOCU$INVOICING as inv
  join DOCUMENTS                                 as doc on doc.K_DOCUMENT = inv.K_DOCUMENT
 where doc.REFERENCE   = 'IPC'
   and doc.K_DOCUMENT >= 240
)
  ,  cte_depenses (annee, depenses) as
(
  select year(opp.D_CONCLUDE)
       , sum(coalesce(opp.F_PLANNEDSPENDING, 0) + coalesce(opp.F_PLANNEDWAGES, 0))
    from Opportunities as opp
   where exists (select null
                   from DOCU_OPPO       as dop
                   join cte_base_data   as bdt on bdt.K_DOCUMENT = dop.K_DOCUMENT
                  where dop.K_OPPORTUNITY = opp.K_OPPORTUNITY)
group by year(opp.D_CONCLUDE)
)
  ,  cte_tva_montant (annee, montant, nbr) as
(
  select year(D_CREATE)
       + case cesure
           when 'F_CESURE1' then 0
           when 'F_CESURE2' then 1
           when 'F_CESURE3' then 2
           when 'F_CESURE4' then 3
         end
       , sum(TOTAL_NO_VAT * pct) / 100.00
       , count(case when pct > 0 then 1 end)
    from cte_base_data
 unpivot (pct for cesure in (F_CESURE1, F_CESURE2, F_CESURE3, F_CESURE4)) as pvt
group by year(D_CREATE)
       + case cesure
           when 'F_CESURE1' then 0
           when 'F_CESURE2' then 1
           when 'F_CESURE3' then 2
           when 'F_CESURE4' then 3
         end
)
    select mnt.annee
         , mnt.montant
         , mnt.nbr
         , coalesce(dep.depenses, 0) as depenses
      from cte_tva_montant as mnt
 left join cte_depenses    as dep on dep.annee = mnt.annee
  order by mnt.annee asc

**Firlfire** · 03/05/2018, 12h04

Merci pour votre réponse, j'aurais mis un sacré moment à la faire..

Du coup pour être sûr que je l'ai bien comprise :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
with cte_base_data --[= nom d'une table temporaire qui stock les données utiles aux requêtes suivantes]
	as --[query de récupération des données à mettre dans la table précédente]
, cte_depenses (annee, depenses) --[= 2ème table temporaire en spécifiant le nom des colonnes de cette table]
	as --[query de récupération des données à mettre dans la 2ème table temporaire]
, cte_tva_montant (annee, montant, nbr) --[= 3ème table temporaire en spécifiant le nom des colonnes de cette table]
	as --[query de récupération des données à mettre dans la 3ème table temporaire]
 
select ... --[Requête pour récupérer les données depuis les tables temporaires précédemment créées]
 
 
La partie with crée des "tables temporaires" (ici 3) qui sont utilisé

Deux derniers trucs, après je passerai le sujet en résolu :

Même après quelques recherches sur le net, je ne comprend pas l'utilité du "where exists" ligne 20 : Il sert à vérifier que la requête "select null ..." retourne bien au moins une ligne, mais j'ai du mal à comprendre à quoi cela sert de vérifier ça
à quoi sert la ligne 18 ?

Code SQL :

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

sum(coalesce(opp.F_PLANNEDSPENDING, 0) + coalesce(opp.F_PLANNEDWAGES, 0))

Pour que le résultat contienne des 0 à la place des null le coalesce de la ligne 50 suffit

**SQLpro** · 03/05/2018, 15h15

Envoyé par Firlfire

...La partie with crée des "tables temporaires" (ici 3) qui sont utilisé

Non les CTE ne créent pas de table temporaires. On peut cepandant les considérées comme des vues jetables, "one shot" ou "kleenex", mais c'est une manière de penser et non pas la réalité de l'exécution. Ce qui se passe derrière peut effectivement conduire à utiliser une table temporaire ou bien d'autres choses comme une réintégration du code de la CET dans la requête finale avec simplification mathématique...

Deux derniers trucs, après je passerai le sujet en résolu :
[*] Même après quelques recherches sur le net, je ne comprend pas l'utilité du "where exists" ligne 20 : Il sert à vérifier que la requête "select null ..." retourne bien au moins une ligne, mais j'ai du mal à comprendre à quoi cela sert de vérifier ça

Le exists vérifie qu'il existe bien au moins une opportunité. EXISTS est le quantificateur d'existence et vaut vrai si la requête peut retourner de l'information et faux qi aucune information n'est retournable.

[*] à quoi sert la ligne 18 ?

De quelle requête ?

Code SQL :

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

sum(coalesce(opp.F_PLANNEDSPENDING, 0) + coalesce(opp.F_PLANNEDWAGES, 0))

Pour que le résultat contienne des 0 à la place des null le coalesce de la ligne 50 suffit

De quelle requête ?

A +

**Waldar** · 03/05/2018, 15h41

Les coalesces de la ligne 18 n'ont pas le même objectif que celui de la ligne 50.

Ceux de la ligne 18 permettent de garantir votre addition, en effet :

Code :

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

1
2
NULL + valeur => NULL
coalesce(NULL, 0) + coalesce(valeur, 0) => valeur

Si vos colonnes ont l'attribut NOT NULL, vous pouvez vous en passer.
Sinon laissez-les.

Celui de la ligne 50 gère les années sans dépense, les deux parties de la requête étant liées par une jointure externe.

**Wachter** · 03/05/2018, 21h47

SUM(COALESCE(opp.F_PLANNEDSPENDING, 0) + COALESCE(opp.F_PLANNEDWAGES, 0))Les fonctions d'agrégation ignorent les valeurs NULL, donc le premier COALESCE est de trop. SUM(opp.F_PLANNEDSPENDING) suffit.

**Waldar** · 04/05/2018, 10h55

Les fonctions d'agrégations oui, les additions non.
Avez-vous vu l'addition à l'intérieur de la somme ?

**Wachter** · 05/05/2018, 12h23

En effet, vous avez raison.

**Firlfire** · 11/06/2018, 10h38

Bonjour!

Désolé de rouvrir le sujet, mais suite à un changement de structure de la base de données j'ai dû modifier ma requête et je voudrais être sûr qu'elle est 'opti' : y a-t-il mieux, etc. ?

Rappel : regrouper différentes données par années
Exemple :
Nom : Cesure v2.PNG
Affichages : 249
Taille : 26,8 Ko

Nom : Cesure v2.PNG
Affichages : 249
Taille : 26,8 Ko

Le changement est que les dépenses prennent en compte un pourcentage du total du projet (cf cte_depenses). Ce Total est le cumul de cte_base_data.TOTAL_NO_VAT aya,t le meme K_PROJECT.
Pour faire ça j'ai dû utiliser une sous-requête correlée dans cte_depenses au niveau du from.
Est-ce qu'il n'y a vraiment pas moyen d'y faire dans le select ?

Code SQL :

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- CA DECENNAL CONTRACTUALISE PAR COMMANDE (SANS DEPENSES)
with cte_base_data as (
  select DP.K_PROJECT
    ,Year(inv.D_INVOICE) as annee
        ,inv.TOTAL_NO_VAT
        ,inv.F_CESURE1
        ,inv.F_CESURE2
        ,inv.F_CESURE3
        ,inv.F_CESURE4
          from DOCU$INVOICING as inv
          join DOCUMENTS as Docu on inv.K_DOCUMENT=Docu.K_DOCUMENT and Docu.REFERENCE like 'IPC' and Docu.K_USER=2
          join DOCU_PROJ DP on DP.K_DOCUMENT=inv.K_DOCUMENT
          where Year(inv.D_INVOICE)>=Year(CURRENT_TIMESTAMP)-10
),
cte_depenses (annee, depenses) as (
  select year(Proj.D_START)
       , sum(coalesce(mgmt.F_MANAGEMENTFEES, 0)/100*data.TOTAL_NO_VAT + coalesce(mgmt.F_MONTANTST, 0) + coalesce(mgmt.F_PLANNEDSPENDING, 0) + coalesce(mgmt.F_PLANNEDWAGES, 0))
    from PROJECTS as Proj
    join PROJ$MANAGEMENT as mgmt on Proj.K_PROJECT = mgmt.K_PROJECT
  join (select K_PROJECT, Sum(TOTAL_NO_VAT) as TOTAL_NO_VAT from cte_base_data group by K_PROJECT) as data on data.K_PROJECT=Proj.K_PROJECT
    group by year(Proj.D_START)
),
cte_tva_montant (annee, montant, nbr) as (
  select annee
     + case cesure
       when 'F_CESURE1' then 0
       when 'F_CESURE2' then 1
       when 'F_CESURE3' then 2
       when 'F_CESURE4' then 3
     end
      ,sum(TOTAL_NO_VAT * pct) / 100.00
      ,count(case when pct > 0 then 1 end)
    from cte_base_data
    unpivot (pct for cesure in (F_CESURE1, F_CESURE2, F_CESURE3, F_CESURE4)) as pvt
    group by annee
       + case cesure
         when 'F_CESURE1' then 0
         when 'F_CESURE2' then 1
         when 'F_CESURE3' then 2
         when 'F_CESURE4' then 3
       end
)
--select * from cte_depenses
select mnt.annee
      ,mnt.montant
      ,mnt.nbr
      ,coalesce(dep.depenses, 0) as depenses
      from cte_tva_montant as mnt
      left join cte_depenses as dep on dep.annee = mnt.annee
      order by mnt.annee asc

Merci d'avanace !

Champ multiple par ligne avec GROUP BY Year()

Développement SQL Server

Discussions similaires

Partager

Partager