CODEWITHSUNDEEP
databaseoracle-database
bollam_rohith
bollam_rohith

Reputation: 384

Get data month wise(previous six months from current month) in oracle sql

Table:

Id         Month         Value
T101       02/2020        3
T101       01/2020        5
T101       05/2020        4
T103       03/2020        7
T103       12/2020        10
T103       11/2019        16

Required:

Id      DEC'19     JAN'20     FEB'20    MAR'20    APR'20    MAY'20
T101     0            5          3          0        0        4
T103     10           0          0          7        0        0

I need to get 6 months data (DEC'19-MAY'20)......Thanks in advance!!

Upvotes: 0

Views: 294

Answers (2)

Popeye
Popeye

Reputation: 35900

You can use the conditional aggregation as follows:

SELECT id,
       COALESCE(CASE WHEN MONTH = ADD_MONTHS(TRUNC(SYSDATE,'MON'), - 5) 
                THEN value END), 0) as MONTH6,
       COALESCE(CASE WHEN MONTH = ADD_MONTHS(TRUNC(SYSDATE,'MON'), - 4) 
                THEN value END), 0) as MONTH5,
       ....
       COALESCE(CASE WHEN MONTH = TRUNC(SYSDATE,'MON') 
                THEN value END), 0) as CURRENT_MONTH,
FROM your_table
GROUP BY id

Note that I am considering that the MONTH is the date column

Upvotes: 2

MT0
MT0

Reputation: 167774

You can use a PIVOT (with COALESCE to get the zero values):

SELECT ID,
       COALESCE( "MAY'20", 0 ) AS "MAY'20",
       COALESCE( "APR'20", 0 ) AS "APR'20",
       COALESCE( "MAR'20", 0 ) AS "MAR'20",
       COALESCE( "FEB'20", 0 ) AS "FEB'20",
       COALESCE( "JAN'20", 0 ) AS "JAN'20",
       COALESCE( "DEC'19", 0 ) AS "DEC'19"
FROM   data
PIVOT (
  SUM( value )
  FOR Month IN (
    DATE '2020-05-01' AS "MAY'20",
    DATE '2020-04-01' AS "APR'20",
    DATE '2020-03-01' AS "MAR'20",
    DATE '2020-02-01' AS "FEB'20",
    DATE '2020-01-01' AS "JAN'20",
    DATE '2019-12-01' AS "DEC'19"
  )
)

However, you will need to hardcode the date values.

So, for your sample data (assuming you are storing your Month date values as dates; if not then convert the literals to strings):

CREATE TABLE data ( Id, Month, Value ) AS
SELECT 'T101', DATE '2020-02-01',  3 FROM DUAL UNION ALL
SELECT 'T101', DATE '2020-01-01',  5 FROM DUAL UNION ALL
SELECT 'T101', DATE '2020-05-01',  4 FROM DUAL UNION ALL
SELECT 'T103', DATE '2020-03-01',  7 FROM DUAL UNION ALL
SELECT 'T103', DATE '2020-12-01', 10 FROM DUAL UNION ALL
SELECT 'T103', DATE '2019-11-01', 16 FROM DUAL;

This outputs:

ID   | MAY'20 | APR'20 | MAR'20 | FEB'20 | JAN'20 | DEC'19
:--- | -----: | -----: | -----: | -----: | -----: | -----:
T103 |      0 |      0 |      7 |      0 |      0 |      0
T101 |      4 |      0 |      0 |      3 |      5 |      0

db<>fiddle here

Upvotes: 1

Related Questions