Search code examples
sqloracle-databaseoracle11goracle10g

How do get last 13 months data from database

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:

Expected Result

Solution

  • 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