I have the following table I want to get the last 13 months data based on my customer orders and months:
create table dynamicorders
(
order_date date,
order_total number(8),
name varchar2(100)
);
insert into dynamicorders values( '2021-01-05', 705, 'jhone');
insert into dynamicorders values( '2021-01-15', 715, 'steve');
insert into dynamicorders values( '2021-01-17', 50, 'jhone');
insert into dynamicorders values( '2021-02-09', 802, 'mular');
insert into dynamicorders values( '2021-03-12', 812, 'steve');
insert into dynamicorders values( '2021-03-29', 829, 'steve');
insert into dynamicorders values( '2021-04-03', 903, 'jhone');
insert into dynamicorders values( '2021-04-29', 150, 'steve');
insert into dynamicorders values( '2021-05-19', 100, 'steve');
insert into dynamicorders values( '2022-01-10', 300, 'steve');
insert into dynamicorders values( '2022-01-18', 400, 'mular');
Here is the screenshot of the result set I need:
CodePudding user response：
You can use:
SELECT name,
SUM(CASE TRUNC(order_date, 'MM') WHEN DATE '2021-01-01' THEN order_total END) AS "21-Jan",
SUM(CASE TRUNC(order_date, 'MM') WHEN DATE '2021-02-01' THEN order_total END) AS "21-Feb",
SUM(CASE TRUNC(order_date, 'MM') WHEN DATE '2021-03-01' THEN order_total END) AS "21-Mar",
SUM(CASE TRUNC(order_date, 'MM') WHEN DATE '2021-04-01' THEN order_total END) AS "21-Apr",
SUM(CASE TRUNC(order_date, 'MM') WHEN DATE '2021-05-01' THEN order_total END) AS "21-May",
SUM(CASE TRUNC(order_date, 'MM') WHEN DATE '2021-06-01' THEN order_total END) AS "21-Jun",
SUM(CASE TRUNC(order_date, 'MM') WHEN DATE '2021-07-01' THEN order_total END) AS "21-Jul",
SUM(CASE TRUNC(order_date, 'MM') WHEN DATE '2021-08-01' THEN order_total END) AS "21-Aug",
SUM(CASE TRUNC(order_date, 'MM') WHEN DATE '2021-09-01' THEN order_total END) AS "21-Sep",
SUM(CASE TRUNC(order_date, 'MM') WHEN DATE '2021-10-01' THEN order_total END) AS "21-Oct",
SUM(CASE TRUNC(order_date, 'MM') WHEN DATE '2021-11-01' THEN order_total END) AS "21-Nov",
SUM(CASE TRUNC(order_date, 'MM') WHEN DATE '2021-12-01' THEN order_total END) AS "21-Dec",
SUM(CASE TRUNC(order_date, 'YY') WHEN DATE '2021-01-01' THEN order_total END) AS TOTAL,
SUM(CASE TRUNC(order_date, 'MM') WHEN DATE '2022-01-01' THEN order_total END) AS "22-Jan"
FROM dynamicorders d
GROUP BY name
Which, for your sample data, outputs:
NAME 21-Jan 21-Feb 21-Mar 21-Apr 21-May 21-Jun 21-Jul 21-Aug 21-Sep 21-Oct 21-Nov 21-Dec TOTAL 22-Jan jhone 755 null null 903 null null null null null null null null 1658 null steve 715 null 1641 150 100 null null null null null null null 2606 300 mular null 802 null null null null null null null null null null 802 400
If you want the last 13 months, rather than specific months then you CANNOT provide dynamic column names relevant to the months (unless you use dynamic SQL) and can use:
SELECT name,
SUM(CASE TRUNC(order_date, 'MM') WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12) THEN order_total END) AS "CURRENT_MONTH-12",
SUM(CASE TRUNC(order_date, 'MM') WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -11) THEN order_total END) AS "CURRENT_MONTH-11",
SUM(CASE TRUNC(order_date, 'MM') WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -10) THEN order_total END) AS "CURRENT_MONTH-10",
SUM(CASE TRUNC(order_date, 'MM') WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -9) THEN order_total END) AS "CURRENT_MONTH-9",
SUM(CASE TRUNC(order_date, 'MM') WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -8) THEN order_total END) AS "CURRENT_MONTH-8",
SUM(CASE TRUNC(order_date, 'MM') WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -7) THEN order_total END) AS "CURRENT_MONTH-7",
SUM(CASE TRUNC(order_date, 'MM') WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -6) THEN order_total END) AS "CURRENT_MONTH-6",
SUM(CASE TRUNC(order_date, 'MM') WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -5) THEN order_total END) AS "CURRENT_MONTH-5",
SUM(CASE TRUNC(order_date, 'MM') WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -4) THEN order_total END) AS "CURRENT_MONTH-4",
SUM(CASE TRUNC(order_date, 'MM') WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -3) THEN order_total END) AS "CURRENT_MONTH-3",
SUM(CASE TRUNC(order_date, 'MM') WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2) THEN order_total END) AS "CURRENT_MONTH-2",
SUM(CASE TRUNC(order_date, 'MM') WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1) THEN order_total END) AS "CURRENT_MONTH-1",
SUM(CASE WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12) <= order_date AND order_date < TRUNC(SYSDATE, 'MM') THEN order_total END) AS TOTAL,
SUM(CASE TRUNC(order_date, 'MM') WHEN TRUNC(SYSDATE, 'MM') THEN order_total END) AS "CURRENT_MONTH"
FROM dynamicorders d
GROUP BY name
Which outputs:
NAME CURRENT_MONTH-12 CURRENT_MONTH-11 CURRENT_MONTH-10 CURRENT_MONTH-9 CURRENT_MONTH-8 CURRENT_MONTH-7 CURRENT_MONTH-6 CURRENT_MONTH-5 CURRENT_MONTH-4 CURRENT_MONTH-3 CURRENT_MONTH-2 CURRENT_MONTH-1 TOTAL CURRENT_MONTH jhone 755 null null 903 null null null null null null null null 1658 null steve 715 null 1641 150 100 null null null null null null null 2606 300 mular null 802 null null null null null null null null null null 802 400
db<>fiddle here
CodePudding user response：
This is a typical use case for a PIVOT function.
with mon as (
select
trunc(ORDER_DATE,'MM') ORDER_MONTH, ORDER_TOTAL, NAME
from dynamicorders)
select * from mon
PIVOT (sum(ORDER_TOTAL) ORDER_TOTAL for (ORDER_MONTH) in
(date'2021-01-01' as "21-Jan",
date'2021-02-01' as "21-Feb",
date'2021-03-01' as "21-Mar",
date'2021-04-01' as "21-Apr",
date'2021-05-01' as "21-May",
date'2021-06-01' as "21-Jun",
date'2021-07-01' as "21-Jul",
date'2021-08-01' as "21-Aug",
date'2021-09-01' as "21-Sep",
date'2021-10-01' as "21-Oct",
date'2021-11-01' as "21-Nov",
date'2021-12-01' as "21-Dec"
)
);
For dynamic last N months you will need a dynamic SQL, as PIVOT requires static expression.
CodePudding user response：
SELECT * FROM dynamicorders WHERE order_date >= DATEADD(MONTH, -13, CAST(GETDATE() AS DATE))
Use the DATEADD function, It takes three parameters DATEADD(interval, number, date).
The first Parameter is interval i.e (YEAR/MONTH/DAY).
The second parameter is number i.e (The number of times you want to increment the date based on interval).
The third parameter is date i.e Date that you want to modify
Check this answer for more information Grab data from the last 13 months.
Also check out this documentation of DATEADD on Microsoft official page for detailed information about this function.