Spaces:
Running
Running
File size: 3,278 Bytes
1e111cb | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | -- FUNCTION: trans.get_trade_sales_analytics(character varying, character varying, date, date)
-- DROP FUNCTION IF EXISTS trans.get_trade_sales_analytics(character varying, character varying, date, date);
CREATE OR REPLACE FUNCTION trans.get_trade_sales_analytics(
p_supplier_id character varying DEFAULT NULL::character varying,
p_client_id character varying DEFAULT NULL::character varying,
p_date_from date DEFAULT NULL::date,
p_date_to date DEFAULT NULL::date)
RETURNS TABLE(metric_name character varying, metric_value numeric, metric_unit character varying)
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
BEGIN
-- Total shipments
RETURN QUERY
SELECT
'total_shipments'::VARCHAR(50) as metric_name,
COUNT(*)::NUMERIC as metric_value,
'count'::VARCHAR(20) as metric_unit
FROM trans.scm_trade_shipment ts
WHERE (p_supplier_id IS NULL OR ts.supplier_id = p_supplier_id)
AND (p_client_id IS NULL OR ts.client_id = p_client_id)
AND (p_date_from IS NULL OR ts.shipment_date >= p_date_from)
AND (p_date_to IS NULL OR ts.shipment_date <= p_date_to);
-- Total quantity shipped
RETURN QUERY
SELECT
'total_qty_shipped'::VARCHAR(50) as metric_name,
COALESCE(SUM(tsi.shipped_qty), 0)::NUMERIC as metric_value,
'units'::VARCHAR(20) as metric_unit
FROM trans.scm_trade_shipment ts
JOIN trans.scm_trade_shipment_item tsi ON ts.shipment_id = tsi.shipment_id
WHERE (p_supplier_id IS NULL OR ts.supplier_id = p_supplier_id)
AND (p_client_id IS NULL OR ts.client_id = p_client_id)
AND (p_date_from IS NULL OR ts.shipment_date >= p_date_from)
AND (p_date_to IS NULL OR ts.shipment_date <= p_date_to);
-- Unique SKUs shipped
RETURN QUERY
SELECT
'unique_skus'::VARCHAR(50) as metric_name,
COUNT(DISTINCT tsi.sku)::NUMERIC as metric_value,
'count'::VARCHAR(20) as metric_unit
FROM trans.scm_trade_shipment ts
JOIN trans.scm_trade_shipment_item tsi ON ts.shipment_id = tsi.shipment_id
WHERE (p_supplier_id IS NULL OR ts.supplier_id = p_supplier_id)
AND (p_client_id IS NULL OR ts.client_id = p_client_id)
AND (p_date_from IS NULL OR ts.shipment_date >= p_date_from)
AND (p_date_to IS NULL OR ts.shipment_date <= p_date_to);
-- Average shipment size
RETURN QUERY
SELECT
'avg_shipment_size'::VARCHAR(50) as metric_name,
COALESCE(AVG(shipment_totals.total_qty), 0)::NUMERIC as metric_value,
'units'::VARCHAR(20) as metric_unit
FROM (
SELECT ts.shipment_id, SUM(tsi.shipped_qty) as total_qty
FROM trans.scm_trade_shipment ts
JOIN trans.scm_trade_shipment_item tsi ON ts.shipment_id = tsi.shipment_id
WHERE (p_supplier_id IS NULL OR ts.supplier_id = p_supplier_id)
AND (p_client_id IS NULL OR ts.client_id = p_client_id)
AND (p_date_from IS NULL OR ts.shipment_date >= p_date_from)
AND (p_date_to IS NULL OR ts.shipment_date <= p_date_to)
GROUP BY ts.shipment_id
) shipment_totals;
END;
$BODY$;
ALTER FUNCTION trans.get_trade_sales_analytics(character varying, character varying, date, date)
OWNER TO trans_owner;
|