Toad World® Forums

Analytic Function - SUM


#1

Greetings to all

I have following query, which is working perfectly fine.

But I am trying to calculate the weekly sales( week1 and week2 from below query) using analytical functions.

SELECT inventory_item_id,
ship_from_org_id,
SUM (ordered_quantity),
MAX (header_id),
MAX (line_id),
SUM (CASE WHEN no_of_days >= 0 AND no_of_days < 7 THEN ordered_quantity ELSE 0 END) week1,
SUM (CASE WHEN no_of_days >= 7 AND no_of_days < 14 THEN ordered_quantity ELSE 0 END) week2
FROM (SELECT
a.inventory_item_id, a.ship_from_org_id, ordered_quantity,
TRUNC (SYSDATE, ‘day’) - TRUNC (b.ordered_date) no_of_days,
a.header_id, a.line_id
FROM oe_order_lines_all a, oe_order_headers_all b, mtl_system_items_b c
WHERE 1 = 1
AND a.inventory_item_id = c.inventory_item_id
AND a.ship_from_org_id = c.organization_id
AND b.booked_flag = ‘Y’
AND b.booked_date IS NOT NULL
AND a.header_id = b.header_id
and (c.organization_id = :xn_org_id or :xn_org_id is null)
and (c.inventory_item_id = :xn_inventory_item_id or :xn_inventory_item_id is null))
GROUP BY inventory_item_id, ship_from_org_id;


Is there any equivalent function available, how can I do it.

Thanks in advance