Re: Request over partition and join

Answering to myself for the record:

[code]
SELECT DISTINCT ON (societe,date_ord,numero_ordre,libelle)
ligne_ordres.id,societe,date_ord,numero_ordre,libelle,
SUM(CASE
WHEN operation_id!=2 AND operation_id!=21 AND operation_id!=23 THEN
quantite
ELSE
-quantite
END) OVER w AS pqte,
SUM(CASE
WHEN operation_id!=2 AND operation_id!=21 AND operation_id!=23 THEN
prix_brut
ELSE
-round(quantite*cumpb,2)
END) OVER w AS pprix_brut,
SUM(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) OVER w AS pfrais,
SUM(CASE
WHEN operation_id!=2 AND operation_id!=21 AND operation_id!=23 THEN
prix_net
ELSE
-round(quantite*cump,2)
END) OVER w AS pprix_net
FROM ligne_ordres INNER JOIN titres ON ligne_ordres.titre_id=titres.id
INNER JOIN operations ON ligne_ordres.operation_id=operations.id
WHERE (date_ord<='2011-03-01' AND societe='WHATEVER'
AND ligne_ordres.id BETWEEN 12 AND 3690)
WINDOW w AS (PARTITION BY societe,date_ord,numero_ordre,libelle)
ORDER BY societe,date_ord,numero_ordre,libelle,ligne_ordres.id
[/code]

Not sure if it is the best way to do it, but at least it works.

Le 7 juil. 11 à 00:59, Michèle Garoche a écrit :

> 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
>
>
>

Cheers,
Michèle