Hello,
I'm trying to get a report using partition and joins.
The involved tables are:
ligne_ordres with
- non unique composite key titre_id, date_ord, numero_ordre,
operation_id
- foreign key titre_id to table titres
- foreign key operation_id to table operations
titres with
- field societe
operations with
- field libelle
I need to get the following report:
id societe date_ord numero_ordre libelle ppb pf ppn tpb
tf tpn
With the following code I get:
id titre_id date_ord numero_ordre operation_id ppb pf ppn
tf tpn
How to integrate societe and libelle into the report instead of
titre_id and operation_id?
[code]
WITH calculate_parts AS (
SELECT id, titre_id, date_ord, numero_ordre, operation_id,
CASE
WHEN operation_id != 2 and operation_id != 21 and
operation_id != 23 THEN
prix_brut
ELSE
-round(quantite * cumpb - 0.005, 2)
END AS prix_brut,
CASE
WHEN operation_id != 2 and operation_id != 21 and
operation_id != 23 THEN
frais
ELSE
-round(quantite * cump, 2) + round(quantite * cumpb, 2)
END AS frais,
CASE
WHEN operation_id != 2 and operation_id != 21 and
operation_id != 23 THEN
prix_net
ELSE
-round(quantite * cump, 2)
END AS prix_net
FROM ligne_ordres
WHERE date_ord >= '2011-03-01')
SELECT DISTINCT ON(titre_id, date_ord, numero_ordre, operation_id)
id, titre_id, date_ord, numero_ordre, operation_id,
SUM(prix_brut) OVER (PARTITION BY titre_id, date_ord, numero_ordre,
operation_id) AS ppb,
SUM(frais) OVER (PARTITION BY titre_id, date_ord, numero_ordre,
operation_id) AS pf,
SUM(prix_net) OVER (PARTITION BY titre_id, date_ord, numero_ordre,
operation_id) AS ppn,
SUM(prix_brut) OVER () AS tpb,
SUM(frais) OVER () AS tf,
SUM(prix_net) OVER () as tpn
FROM calculate_parts
ORDER BY titre_id, date_ord, numero_ordre, operation_id, id
[/code]
Thanks in advance for any help.
Cheers,
Michèle
