x en fonction <= a la date

Version imprimable

Bonjour,

Merci beaucoup pour votre aide.
J'ai deux table :
La table table1 avec 3 colonnes user, date, budget.

-- Table1 --
U | date | Budget
1 | 01/01/17 | 100
2 | 01/01/17 | 110
3 | 01/01/17 | 101
2 | 15/02/17 | 111
2 | 03/03/17 | 109
3 | 07/06/17 | 102

La table2 avec trois colonnes user, date depense

-- Table2 --
U | date | depense
1 | 04/01/17 | 100
2 | 06/01/17 | 150
1 | 24/02/17 | 130
2 | 20/03/17 | 110
1 | 14/07/17 | 170
3 | 16/08/17 | 180

je souhaiterai avoir la table 2 avec une colonne du budget aloue (< ou = a la date de depense) en fonction des users.

resultat souhaite:

-- resultat --
U | date | depense | budget
1 | 01/17 | 100 | 100
2 | 01/17 | 150 | 110
1 | 02/17 | 130 | 100
2 | 03/17 | 110 | 109
1 | 07/17 | 170 | 100
3 | 08/17 | 180 | 102

je n'y arrive pas, ma formule ne fonctionne pas:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text) AS date, table2.depense, table2.user, table1.budget FROM table1 INNER JOIN table2 ON (public.table1.user = table2.user) GROUP BY to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text), table2.user, table1.budget

Bon je ne vais pas discuter sur les tables et leurs relations ...
Mais
1) tout champ figurant hors "group by" doit figurer dans une fonction de regroupement ...
2) Il manque à priori la jointure sur partie de date (MM/YYYY) entre table1 et table2
3) Si la requête ci-dessous fonctionne, je tenterai de remplacer le to_char ... par des combinaisons
de EXTRACT(MONTH FROM table2.date) et EXTRACT(year FROM date), plus propre à mon sens ...
car caster un timestamp en chaine puis en date ça me parait d'un lourd 8O

Ainsi tester ceci
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text) AS date, table2.user, min(table2.depense), min(table1.budget) FROM table1 INNER JOIN table2 ON public.table1.user = table2.user and to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text) = to_char((public.table1.date) ::timestamp with time zone, 'MM/YYYY' ::text) GROUP BY to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text), table2.user
puis
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT EXTRACT(MONTH FROM table2.date) , EXTRACT(YEAR,FROM table2.date), table2.user, min(table2.depense), min(table1.budget) ... GROUP BY EXTRACT(MONTH FROM table2.date) , EXTRACT(YEAR,FROM table2.date), table2.user

Bonjour,

N'y a-t- il pas une erreur dans le résultat attendu, je ne comprends pas la ligne
2 | 03/17 | 110 | 111

qui à mon sens devrait être
2 | 03/17 | 110 | 109

Puisque dans la 1ère table, vous avez un budget de 109 au 03-03-2017 soit la date la plus proche antérieure à la dépense du 20-03-2017

Si c'est bien le cas, voici une solution possible :

Création de mon jeu d'essai conforme au votre, avec 2 CTE (la 1ère pour les dépenses, la deuxième pour les budgets)

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
 
   with cte1 (xuser, xdate, xdepe) as                          
       (select 1, '2017-01-04', 100     
        union all
        select 1, '2017-02-24', 130     
        union all                                               
        select 1, '2017-07-14', 170     
        union all                                              
        select 2, '2017-01-06', 150     
        union all                                              
        select 2, '2017-03-20', 110     
        union all                                              
        select 3, '2017-08-16', 180     
       )                                                       
      , cte2 (yuser, ydate, ybudg) as                         
       (select 1 , '2017-01-01', 100     
        union all                                             
        select 2 , '2017-01-01', 110     
        union all                                             
        select 2 , '2017-02-15', 111     
        union all                                             
        select 2 , '2017-03-03', 109     
        union all                                             
        select 3 , '2017-01-01', 101     
        union all                                             
        select 3 , '2017-06-07', 102     
       )

Puis exécution de la requête (à adapter pour la conversion de date en char à mettre à la syntaxe PG)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
   select xuser                         
        , substr(char(xdate), 01, 07)   
        , sum(xdepe)                    
        , ybudg                         
   from CTE1 T1                         
   inner join CTE2 T2                   
      on T2.yuser =T1.xuser             
     and T2.ydate<=T1.xdate             
   where T2.ydate=                      
        (select max(ydate)              
         from CTE2 S2                   
         where S2.yuser =T2.yuser       
           and S2.ydate<=T1.xdate)      
   group by xuser                       
          , substr(char(xdate), 01, 07) 
          , ybudg                       
   order by 2, 1

Résultat :

Code:

1
2
3
4
5
6
  1 2017-01         100         100
  2 2017-01         150         110
  1 2017-02         130         100
  2 2017-03         110         109
  1 2017-07         170         100
  3 2017-08         180         102

Citation:
Envoyé par vttman
Bon je ne vais pas discuter sur les tables et leurs relations ...
Mais
1) tout champ figurant hors "group by" doit figurer dans une fonction de regroupement ...
2) Il manque à priori la jointure sur partie de date (MM/YYYY) entre table1 et table2
3) Si la requête ci-dessous fonctionne, je tenterai de remplacer le to_char ... par des combinaisons
de EXTRACT(MONTH FROM table2.date) et EXTRACT(year FROM date), plus propre à mon sens ...
car caster un timestamp en chaine puis en date ça me parait d'un lourd 8O

Ainsi tester ceci
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text) AS date, table2.user, min(table2.depense), min(table1.budget) FROM table1 INNER JOIN table2 ON public.table1.user = table2.user and to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text) = to_char((public.table1.date) ::timestamp with time zone, 'MM/YYYY' ::text) GROUP BY to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text), table2.user
puis
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT EXTRACT(MONTH FROM table2.date) , EXTRACT(YEAR,FROM table2.date), table2.user, min(table2.depense), min(table1.budget) ... GROUP BY EXTRACT(MONTH FROM table2.date) , EXTRACT(YEAR,FROM table2.date), table2.user
un tres tres grand MERCI.... ca fonctionne
je suis a la moselle aussi de Thionville ;-)

Citation:

Envoyé par escartefigue

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
 
   with cte1 (xuser, xdate, xdepe) as                          
       (select 1, '2017-01-04', 100     
        union all
        select 1, '2017-02-24', 130     
        union all                                               
        select 1, '2017-07-14', 170     
        union all                                              
        select 2, '2017-01-06', 150     
        union all                                              
        select 2, '2017-03-20', 110     
        union all                                              
        select 3, '2017-08-16', 180     
       )                                                       
      , cte2 (yuser, ydate, ybudg) as                         
       (select 1 , '2017-01-01', 100     
        union all                                             
        select 2 , '2017-01-01', 110     
        union all                                             
        select 2 , '2017-02-15', 111     
        union all                                             
        select 2 , '2017-03-03', 109     
        union all                                             
        select 3 , '2017-01-01', 101     
        union all                                             
        select 3 , '2017-06-07', 102     
       )

Puis exécution de la requête (à adapter pour la conversion de date en char à mettre à la syntaxe PG)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
   select xuser                         
        , substr(char(xdate), 01, 07)   
        , sum(xdepe)                    
        , ybudg                         
   from CTE1 T1                         
   inner join CTE2 T2                   
      on T2.yuser =T1.xuser             
     and T2.ydate<=T1.xdate             
   where T2.ydate=                      
        (select max(ydate)              
         from CTE2 S2                   
         where S2.yuser =T2.yuser       
           and S2.ydate<=T1.xdate)      
   group by xuser                       
          , substr(char(xdate), 01, 07) 
          , ybudg                       
   order by 2, 1

Résultat :

Code:

1
2
3
4
5
6
  1 2017-01         100         100
  2 2017-01         150         110
  1 2017-02         130         100
  2 2017-03         110         109
  1 2017-07         170         100
  3 2017-08         180         102

exact c'est 109 je vais le rectifier
punaise ... Merci grandement ;-)

12/01/2018, 17h45
vttman

Citation:

Envoyé par reimic5

un tres tres grand MERCI.... ca fonctionne
je suis a la moselle aussi de Thionville ;-)

;) Thionville j'y passe tous les matins vers 6H ... quand je bosse bien sûr.
12/01/2018, 19h00
escartefigue

Je me déplaçais il y a fort longtemps sur Florange pour le boulot, c'était à l'époque des dinosaures, c'est à dire les bases hiérarchiques et réseau, que les bases relationnelles n'avaient pas encore supplantées http://yelims3.free.fr/Jesuis/Vieux01.gif :mrgreen:
12/01/2018, 19h08
vttman

Florange aussi, je suis passé par là et maintenant je me souviens avoir travaillé à cette période sur du DL1, il y a un an il y a un siècle il y a une éternité :lol:
12/01/2018, 19h26
escartefigue

Du DL1 il y a un an, et ben c'est un des rares sites qui font encore de la résistance :weird: :mrgreen:
13/01/2018, 09h16
vttman

Euh oui, il y a 27-28 ans :roll:, j'avais la musique de l'été indien en tête ... mais le 1 an on peut enlever.

1 pièce(s) jointe(s)

:( non ca ne fonctionne pas comme je veux.

la table1 (budget aloue) peut ne pas evoluer mais la table2 evolue chaque mois (depense) donc chaque mois la table3 affiche les depenses (passees) et le budget aloue.

-- Table1 --
U | date | Budget
1 | 01/01/17 | 100
2 | 01/01/17 | 110
3 | 01/01/17 | 101
2 | 15/02/17 | 111
2 | 03/03/17 | 109
3 | 07/06/17 | 102

La table2 avec trois colonnes user, date depense

-- Table2 --
U | date | depense
1 | 04/01/17 | 100
2 | 06/01/17 | 150
1 | 24/02/17 | 130
2 | 20/03/17 | 110
1 | 14/07/17 | 170
3 | 16/08/17 | 180
1 | 18/09/17 | 200

je souhaiterai avoir la table 2 avec une colonne du budget aloue (< ou = a la date de depense) en fonction des users.

resultat souhaite:

-- resultat --
U | date | depense | budget
1 | 01/17 | 100 | 100
2 | 01/17 | 150 | 110
1 | 02/17 | 130 | 100
2 | 03/17 | 110 | 109
1 | 07/17 | 170 | 100
3 | 08/17 | 180 | 102
1 | 09/17 | 200 | 100

voici la formule modifiee mais le budget ne s'affiche pas pour tout les mois (juste pour le mois reference dans table1)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text) AS date, table2.user, min(table2.depense), min(table1.budget) FROM table1 RIGHT JOIN table2 ON public.table1.user = table2.user and to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text) = to_char((public.table1.date) ::timestamp with time zone, 'MM/YYYY' ::text) GROUP BY to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text), table2.user
code production:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 SELECT to_char(( relevecarburant.DATE ) :: TIMESTAMP WITH TIME ZONE, 'MM/YYYY' :: TEXT ) AS "date", "public".relevecarburant.usager, Min("public".carmanagement.budgetessence) AS budget_essence, Sum("public".relevecarburant.montant) AS somme_carburant, ( MIN ( carmanagement.budgetessence ) - SUM ( relevecarburant.montant )) AS delta FROM ("public".carmanagement RIGHT JOIN "public".relevecarburant ON ((("public".carmanagement.usager = "public".relevecarburant.usager) AND (to_char(( relevecarburant.DATE ) :: TIMESTAMP WITH TIME ZONE, 'MM/YYYY' :: TEXT )= to_char(( carmanagement.DATE ) :: TIMESTAMP WITH TIME ZONE, 'MM/YYYY' :: TEXT ))))) GROUP BY ( to_char(( relevecarburant.DATE ) :: TIMESTAMP WITH TIME ZONE, 'MM/YYYY' :: TEXT )), "public".relevecarburant.usager HAVING "public".relevecarburant.usager = 171 //filtre juste pour le test
Pièce jointe 343247

Citation:

Envoyé par escartefigue

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
 
   with cte1 (xuser, xdate, xdepe) as                          
       (select 1, '2017-01-04', 100     
        union all
        select 1, '2017-02-24', 130     
        union all                                               
        select 1, '2017-07-14', 170     
        union all                                              
        select 2, '2017-01-06', 150     
        union all                                              
        select 2, '2017-03-20', 110     
        union all                                              
        select 3, '2017-08-16', 180     
       )                                                       
      , cte2 (yuser, ydate, ybudg) as                         
       (select 1 , '2017-01-01', 100     
        union all                                             
        select 2 , '2017-01-01', 110     
        union all                                             
        select 2 , '2017-02-15', 111     
        union all                                             
        select 2 , '2017-03-03', 109     
        union all                                             
        select 3 , '2017-01-01', 101     
        union all                                             
        select 3 , '2017-06-07', 102     
       )

Puis exécution de la requête (à adapter pour la conversion de date en char à mettre à la syntaxe PG)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
   select xuser                         
        , substr(char(xdate), 01, 07)   
        , sum(xdepe)                    
        , ybudg                         
   from CTE1 T1                         
   inner join CTE2 T2                   
      on T2.yuser =T1.xuser             
     and T2.ydate<=T1.xdate             
   where T2.ydate=                      
        (select max(ydate)              
         from CTE2 S2                   
         where S2.yuser =T2.yuser       
           and S2.ydate<=T1.xdate)      
   group by xuser                       
          , substr(char(xdate), 01, 07) 
          , ybudg                       
   order by 2, 1

Résultat :

Code:

1
2
3
4
5
6
  1 2017-01         100         100
  2 2017-01         150         110
  1 2017-02         130         100
  2 2017-03         110         109
  1 2017-07         170         100
  3 2017-08         180         102

-----
Bonjour escartefigue,
ca ne fonctionne pas pour moi :-(
---
voici mon code:

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
SELECT
	relevecarburant.usager,
	substr( CHAR ( relevecarburant.DATE ), 01, 07 ),
	SUM ( relevecarburant.montant ),
	carmanagement.budgetessence 
FROM
	relevecarburant T1
	INNER JOIN carmanagement T2 ON T2.usager = T1.usager 
	AND T2.DATE <= T1.DATE 
WHERE
	T2.DATE = (
SELECT MAX
	( carmanagement.DATE ) 
FROM
	carmanagement S2 
WHERE
	S2.usager = T2.usager 
	AND S2.DATE <= T1.DATE 
	) 
GROUP BY
	relevecarburant.usager,
	substr( CHAR ( relevecarburant.DATE ), 01, 07 ),
	carmanagement.budgetessence 
ORDER BY
	2, 1

---

merci beaucoup ca fonctionne

Code:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT to_char((t1.date)::timestamp with time zone, 'MM/YYYY'::text) AS date,
    t1.usager,
    t2.budgetessence AS budget_essence,
    sum(t1.montant) AS somme_carburant,
    (t2.budgetessence - sum(t1.montant)) AS delta
   FROM (relevecarburant t1
     JOIN carmanagement t2 ON (((t2.usager = t1.usager) AND (t2.date <= t1.date))))
  WHERE (t2.date = ( SELECT max(s2.date) AS max
           FROM carmanagement s2
          WHERE ((s2.usager = t2.usager) AND (s2.date <= t1.date))))
  GROUP BY t1.usager, (to_char((t1.date)::timestamp with time zone, 'MM/YYYY'::text)), t2.budgetessence
  ORDER BY (to_char((t1.date)::timestamp with time zone, 'MM/YYYY'::text)), t1.usager

17/01/2018, 12h23
escartefigue

Oui j'avais bien précisé qu'il fallait adapter à la sauce PG, les fonctions de date sont assez différentes d'un SGBD à l'autre et je n'ai pas de PG sous la main ;)