74 lines
2.5 KiB
SQL
74 lines
2.5 KiB
SQL
--DROP VIEW IF EXISTS vw_bi_fct_open_position;
|
|
|
|
CREATE OR REPLACE VIEW vw_bi_fct_open_position
|
|
|
|
AS
|
|
|
|
WITH purchase_position AS (
|
|
SELECT
|
|
Contracts."intTradeId",
|
|
Contracts."Trade Category",
|
|
Contracts."Trade Type",
|
|
Contracts."Delivery Date",
|
|
TO_CHAR(Contracts."Delivery Date", 'YYYY-MM') AS "delivery_month",
|
|
TO_CHAR(Contracts."Delivery Date", 'Mon YYYY') AS "delivery_month_label",
|
|
Contracts."Quantity",
|
|
Contracts."Quantity" - COALESCE( Delivery."delivered_quantity" , 0 ) AS "Open Position Quantity"
|
|
FROM vw_bi_fct_trade_line AS Contracts
|
|
LEFT JOIN (
|
|
-- Deduct delivered quantities
|
|
SELECT
|
|
FFE."inTradeId",
|
|
SUM(FFE."Quantity") AS "delivered_quantity"
|
|
FROM vw_bi_fct_trade_fulfillment_event AS FFE
|
|
WHERE FFE."Trade Category" = 'Physical'
|
|
AND FFE."Trade Type" = 'Purchase'
|
|
GROUP BY
|
|
FFE."inTradeId"
|
|
) AS Delivery ON Contracts."intTradeId" = Delivery."inTradeId"
|
|
WHERE 1=1
|
|
AND Contracts."Trade Category" = 'Physical'
|
|
AND Contracts."Trade Type" = 'Purchase'
|
|
)
|
|
,sale_position AS (
|
|
SELECT
|
|
Contracts."intTradeId",
|
|
Contracts."Trade Category",
|
|
Contracts."Trade Type",
|
|
Contracts."Delivery Date",
|
|
TO_CHAR(Contracts."Delivery Date", 'YYYY-MM') AS "delivery_month",
|
|
TO_CHAR(Contracts."Delivery Date", 'Mon YYYY') AS "delivery_month_label",
|
|
Contracts."Quantity",
|
|
(Contracts."Quantity" - COALESCE( Delivery."delivered_quantity" , 0 ) ) * (-1) AS "Open Position Quantity"
|
|
FROM vw_bi_fct_trade_line AS Contracts
|
|
LEFT JOIN (
|
|
-- Deduct delivered quantities
|
|
SELECT
|
|
FFE."inTradeId",
|
|
SUM(FFE."Quantity") AS "delivered_quantity"
|
|
FROM vw_bi_fct_trade_fulfillment_event AS FFE
|
|
WHERE FFE."Trade Category" = 'Physical'
|
|
AND FFE."Trade Type" = 'Sale'
|
|
GROUP BY
|
|
FFE."inTradeId"
|
|
) AS Delivery ON Contracts."intTradeId" = Delivery."inTradeId"
|
|
WHERE 1=1
|
|
AND Contracts."Trade Category" = 'Physical'
|
|
AND Contracts."Trade Type" = 'Sale'
|
|
)
|
|
|
|
SELECT *
|
|
FROM purchase_position
|
|
|
|
UNION ALL
|
|
|
|
SELECT *
|
|
FROM sale_position;
|
|
|
|
|
|
ALTER TABLE public.vw_bi_fct_open_position
|
|
OWNER TO postgres;
|
|
|
|
|
|
|