query
unknown
sql
3 years ago
3.0 kB
3
Indexable
WITH tranches_count AS (SELECT arc.nip, max(art.number) AS tranches_count FROM axpo_rozliczenia_contract arc JOIN axpo_rozliczenia_pointofdelivery a ON arc.id = a.contract_id JOIN axpo_rozliczenia_tranche art ON a.id = art.ppe_id WHERE 1=1 AND date_part('year', settlement_date) = 2022 AND date_part('month', settlement_date) = 4 GROUP BY arc.nip), min_years AS ( SELECT arc2.contract_number, a.number, min(date_part('year', arm.date)) AS min_year FROM axpo_rozliczenia_contract arc2 JOIN axpo_rozliczenia_pointofdelivery a ON arc2.id = a.contract_id JOIN axpo_rozliczenia_monthlyvolume arm ON a.id = arm.point_of_delivery_id GROUP BY arc2.contract_number, a.number ) SELECT DISTINCT c.bzs_date AS "Data Weryfikacji Pozytywnej BZS", c.settlement_date AS "Data Rozliczenia", c.partner_number AS "Numer Partnera", c.client_name AS "Nazwa Klienta", c.nip AS "NIP", c.contract_number AS "Numer Umowy", c.employee_first_name || ' ' || c.employee_last_name AS Pracownik, c.employee_number AS "Id Doradcy Klienta", arp.name AS "Nazwa PPE", arp.number AS "Numer PPE/PPG", c.product_name AS "Nazwa produktu", c.contract_id AS "contract_id", arp.tariff AS "Taryfa", CASE WHEN c.partner_number='P/155' then '-' else arm.zone END AS "Strefa", c.product_edition, coalesce(l.tranches_count, 0) AS tranches_count, cast(my.min_year AS integer) AS min_year FROM axpo_rozliczenia_contract c JOIN axpo_rozliczenia_pointofdelivery arp ON c.id = arp.contract_id JOIN axpo_rozliczenia_monthlyvolume arm ON arp.id = arm.point_of_delivery_id LEFT JOIN tranches_count l ON c.nip = l.nip JOIN min_years my ON c.contract_number = my.contract_number AND arp.number = my.number WHERE medium_id = 2 AND partner_number LIKE '%%' AND date_part('year', settlement_date) = 2022 AND date_part('month', settlement_date) = 4 ORDER BY c.contract_number, arp.number, "Strefa";
Editor is loading...