I have 5 different queries that work fine but basically do the same thing. The difference is that they group by different periods.
My question is can these 5 queries be combined into 1 query perhaps a procedure, Where I pass in a D (Day), W (Week) M (Month), Q (quarter) or Y (year).
Below are the queries and some test data. Thanks in advance to all who respond.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE TABLE customers
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Palmice' FROM DUAL UNION ALL
SELECT 4, 'Jerry', 'Torchiano' FROM DUAL;
CREATE TABLE items
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;
CREATE TABLE purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) AS
SELECT 1, 101, 3, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-12 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2, 101, 3, TIMESTAMP '2022-10-17 19:34:58' FROM DUAL UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-06 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual CONNECT BY LEVEL <= 6 UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-26 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual CONNECT BY LEVEL <= 6 UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-12-21 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-27 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual
CONNECT BY LEVEL <= 15 UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-12-11 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-12 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual
CONNECT BY LEVEL <= 5;
/* purchases per day for each customer */
SELECT TO_CHAR (p.purchase_date, 'YYYY-MM-DD') AS year_mon_day
, p.customer_id
, c.first_name
, c.last_name
, SUM (p.quantity * i.price) AS total_amt
FROM purchases p
JOIN customers c ON p.customer_id = c.customer_id
JOIN items i ON p.product_id = i.product_id
GROUP BY GROUPING SETS ( (TO_CHAR (p.purchase_date, 'YYYY-MM-DD'), p.customer_id, c.first_name, c.last_name)
, (TO_CHAR (p.purchase_date, 'YYYY-MM-DD'))
, ()
)
ORDER BY TO_CHAR (p.purchase_date, 'YYYY-MM-DD'), p.customer_id;
/* purchases per week for each customer */
SELECT TO_CHAR (p.purchase_date, 'IYYY"W"IW') AS year_week
, p.customer_id
, c.first_name
, c.last_name
, SUM (p.quantity * i.price) AS total_amt
FROM purchases p
JOIN customers c ON p.customer_id = c.customer_id
JOIN items i ON p.product_id = i.product_id
GROUP BY GROUPING SETS ( (TO_CHAR (p.purchase_date, 'IYYY"W"IW'), p.customer_id, c.first_name, c.last_name)
, (TO_CHAR (p.purchase_date, 'IYYY"W"IW'))
, ()
)
ORDER BY TO_CHAR (p.purchase_date, 'IYYY"W"IW'), p.customer_id;
/* purchases per month for each customer */
SELECT TO_CHAR (p.purchase_date, 'YYYY"M"MM') AS year_month
, p.customer_id
, c.first_name
, c.last_name
, SUM (p.quantity * i.price) AS total_amt
FROM purchases p
JOIN customers c ON p.customer_id = c.customer_id
JOIN items i ON p.product_id = i.product_id
GROUP BY GROUPING SETS ( (TO_CHAR (p.purchase_date, 'YYYY"M"MM'), p.customer_id, c.first_name, c.last_name)
, (TO_CHAR (p.purchase_date, 'YYYY"M"MM'))
, ()
)
ORDER BY TO_CHAR (p.purchase_date, 'YYYY"M"MM'), p.customer_id;
/* purchases per quarter for each customer */
SELECT TO_CHAR (p.purchase_date, 'YYYY"Q"Q') AS year_quarter
, p.customer_id
, c.first_name
, c.last_name
, SUM (p.quantity * i.price) AS total_amt
FROM purchases p
JOIN customers c ON p.customer_id = c.customer_id
JOIN items i ON p.product_id = i.product_id
GROUP BY GROUPING SETS ( (TO_CHAR (p.purchase_date, 'YYYY"Q"Q'), p.customer_id, c.first_name, c.last_name)
, (TO_CHAR (p.purchase_date, 'YYYY"Q"Q'))
, ()
)
ORDER BY TO_CHAR (p.purchase_date, 'YYYY"Q"Q'), p.customer_id;
/* purchases per year for each customer */
SELECT TO_CHAR (p.purchase_date, 'YYYY"Y"') AS year
, p.customer_id
, c.first_name
, c.last_name
, SUM (p.quantity * i.price) AS total_amt
FROM purchases p
JOIN customers c ON p.customer_id = c.customer_id
JOIN items i ON p.product_id = i.product_id
GROUP BY GROUPING SETS ( (TO_CHAR (p.purchase_date, 'YYYY"Y"'), p.customer_id, c.first_name, c.last_name)
, (TO_CHAR (p.purchase_date, 'YYYY"Y"'))
, ()
)
ORDER BY TO_CHAR (p.purchase_date, 'YYYY"Y"'), p.customer_id;
If you want it as a procedure then:
CREATE PROCEDURE get_customer_data(
i_period IN VARCHAR2,
o_cursor OUT SYS_REFCURSOR
)
AS
v_format VARCHAR2(10);
BEGIN
v_format := CASE UPPER(i_period)
WHEN 'D' THEN 'YYYY-MM-DD'
WHEN 'W' THEN 'IYYY"W"IW'
WHEN 'M' THEN 'YYYY"M"MM'
WHEN 'Q' THEN 'YYYY"Q"Q'
WHEN 'Y' THEN 'YYYY"Y"'
ELSE 'YYYY-MM-DD'
END;
OPEN o_cursor FOR
SELECT TO_CHAR (p.purchase_date, v_format) AS period
, p.customer_id
, c.first_name
, c.last_name
, SUM (p.quantity * i.price) AS total_amt
FROM purchases p
JOIN customers c ON p.customer_id = c.customer_id
JOIN items i ON p.product_id = i.product_id
GROUP BY
GROUPING SETS(
( TO_CHAR (p.purchase_date, v_format), p.customer_id, c.first_name, c.last_name )
, TO_CHAR (p.purchase_date, v_format)
, ()
)
ORDER BY TO_CHAR (p.purchase_date, v_format), p.customer_id;
END;
/