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 wrapper, which calls 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.
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(
PURCHASE_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
CUSTOMER_ID NUMBER,
PRODUCT_ID NUMBER,
QUANTITY NUMBER,
PURCHASE_DATE TIMESTAMP
);
INSERT INTO purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE)
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;
/* Group by day*/
SELECT p.customer_id,
c.first_name,
c.last_name,
TO_CHAR (p.purchase_date, 'YYYY"Y"MM"M"DD"D"') AS period,
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 (
(p.customer_id, c.first_name, c.last_name, (TO_CHAR (p.purchase_date,
'YYYY"Y"MM"M"DD"D"')))
, p.customer_id
, ()
)
ORDER BY p.customer_id,
TO_CHAR (p.purchase_date, 'YYYY"Y"MM"M"DD"D"');
/* Group by week*/
SELECT p.customer_id,
c.first_name,
c.last_name,
TO_CHAR (p.purchase_date, 'IYYY"W"IW') AS period,
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 (
(p.customer_id, c.first_name, c.last_name, (TO_CHAR (p.purchase_date, 'IYYY"W"IW')))
, p.customer_id
, ()
)
ORDER BY p.customer_id,
TO_CHAR (p.purchase_date, 'IYYY"W"IW');
/* Group by month */
SELECT p.customer_id,
c.first_name,
c.last_name,
TO_CHAR (p.purchase_date, 'YYYY"Y"MM"M"') AS period,
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 (
(p.customer_id, c.first_name, c.last_name, (TO_CHAR (p.purchase_date,'YYYY"Y"MM"M"')))
, p.customer_id
, ()
)
ORDER BY p.customer_id,
TO_CHAR (p.purchase_date, 'YYYY"Y"MM"M"');
/* Group by quarter */
SELECT p.customer_id,
c.first_name,
c.last_name,
TO_CHAR (p.purchase_date,'YYYY"Y"Q"Q"') AS period,
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 (
(p.customer_id, c.first_name, c.last_name, (TO_CHAR (p.purchase_date,'YYYY"Y"Q"Q"')))
, p.customer_id
, ()
)
ORDER BY p.customer_id,
TO_CHAR (p.purchase_date, 'YYYY"Y"Q"Q"');
/* Group by year */
SELECT p.customer_id,
c.first_name,
c.last_name,
TO_CHAR (p.purchase_date, 'YYYY"Y"') AS period,
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 (
(p.customer_id, c.first_name, c.last_name, (TO_CHAR (p.purchase_date,'YYYY"Y"')))
, p.customer_id
, ()
)
ORDER BY p.customer_id,
TO_CHAR (p.purchase_date, 'YYYY"Y"');