1
I’m mounting a query where I need the percentage of category for each cargo. If I filter for only one load, the query returns correctly. But if I put more than one (a period, for example) the percentage is calculated on all loads. It is possible to calculate the percentage individually?
Query:
select pcb.registro
,pcb.data_entrada
,DECODE(tab.categ,'01','CANDIES','CHOCOLATE') Categoria
,SUM(tab.qtde) AS QTDE_CX
,ROUND(SUM(tab.Peso_Padrao),2) AS Peso_KG_PADRAO
,ROUND(SUM(tab.qtde) / SUM(SUM(tab.qtde)) OVER (),4)*100 AS PERCENTUAL_CARGA
-- ,ROUND(pcb.peso_liquido,2) PESO_PERC_BALANCA
FROM
(WITH w_cargas AS (SELECT DISTINCT wt.name carga,
wnd.delivery_id distribuicao,
ooha.order_number ordem
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_trips wt,
wsh_trip_stops wts1,
wsh_delivery_legs wdl
WHERE ooha.header_id = oola.header_id
AND oola.cancelled_flag = 'N' -- ordem nao pode estar cancelada
AND oola.header_id = wdd.source_header_id
AND oola.line_id = wdd.source_line_id
AND wdd.source_code = 'OE'
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wts1.trip_id = wt.trip_id
AND wdl.pick_up_stop_id = wts1.stop_id
AND wdl.delivery_id = wnd.delivery_id
AND wnd.delivery_id = wda.delivery_id)
SELECT wc.carga carga,
sum(rctl.quantity_invoiced) qtde,
sum(msi.unit_weight * (rctl.quantity_invoiced)) Peso_Padrao,
mc.segment2 categ
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
mtl_system_items msi,
mtl_item_categories mic,
mtl_category_sets mcs,
mtl_categories mc,
w_cargas wc
WHERE rctl.CUSTOMER_TRX_ID = rct.CUSTOMER_TRX_ID
and rctl.line_type = 'LINE'
and rct.status_trx <> 'VD'
and msi.inventory_item_id = rctl.inventory_item_id
and msi.organization_id = 195
and msi.inventory_item_id = mic.inventory_item_id
and msi.organization_id = mic.organization_id
and mic.category_set_id = mcs.category_set_id
and mcs.structure_id = mc.structure_id
and mic.category_id = mc.category_id
and mcs.category_set_name = 'Vendas e Marketing'
AND wc.distribuicao = to_number(rct.interface_header_attribute3)
AND RCT.trx_date BETWEEN '01-SET-2019' AND '25-SET-2019'
-- and wc.carga IN ('2228376','2230362') -- se tiver numero da carga, se nao comenta essa linha
-- and rct.trx_number = '387593'
group by mc.segment2, wc.carga) tab,
--PORTARIA
pcn_pesagem_balanca pcb
WHERE tab.carga IN (pcb.carga,pcb.carga2,pcb.carga3,pcb.carga4,pcb.carga5,pcb.carga6,pcb.carga7,pcb.carga8,pcb.carga9)
GROUP BY pcb.registro,
DECODE(tab.categ,'01','CANDIES','CHOCOLATE'),
pcb.data_entrada;
Exit with a load:
Exit with multiple loads (period):
