Need to pull two types of sales value: Last Month of each year/Month and Last Year Same Month data in Oracle Sql

user_14899749 0
Viewed 31

My Source table looks like below

Sales Date  Sales Value
4/1/2019    20
5/1/2019    50
6/1/2019    60
7/1/2019    10
8/1/2019    20
9/1/2019    10
4/1/2020    70
5/1/2020    100
6/1/2020    10
7/1/2020    10
8/1/2020    20
9/1/2020    20

Output should be:

Year    Month   Sales_Value_Last_Month_of_each_year/Month   Last_Year_Same_Month

2019    4   20      
2019    5   50          20  
2019    6   60          50  
2019    7   10          60  
2019    8   20          10  
2019    9   10          20  
2020    4   70          0                                   20
2020    5   100         70                                  50
2020    6   10          100                                 60
2020    7   10          10                                  10
2020    8   20          10                                  20
2020    9   20          20                                  10

Explanation:

for 2020 and 05 Month, Sales_Value_Last_Month_of_each_year/Month should be 70 as this is the previous month value(2020 and 04 is 70) and if the previous value is not exist, it should be 0. For for 2020 and 05 Month, Last_Year_Same_Month should be 50 as last year 2019 and 05 is 50 and if the values is not exist for corresponding month of last year, it should be 0.

I cannot go for regular last month data like add_months(sales_date, 'month')-1 in where condition, since my output is to show everything like each_month_sales_value, Sales_Value_Last_Month_of_each_year/Month and Last_Year_Same_Month

I am not able find the way to write this in oracle sql. Could someone please suggest a way to achieve this result

oracle sql
1 Answers

You can use analytic functions with a range window:

SELECT sales_date,
       sales_value,
       COALESCE(
         MAX(sales_value) OVER (
           ORDER BY sales_date
           RANGE BETWEEN INTERVAL '1' MONTH PRECEDING
                     AND INTERVAL '1' MONTH PRECEDING
         ),
         0
       ) AS last_month_sales,
       COALESCE(
         MAX(sales_value) OVER (
           ORDER BY sales_date
           RANGE BETWEEN INTERVAL '12' MONTH PRECEDING
                     AND INTERVAL '12' MONTH PRECEDING
         ),
         0
       ) AS last_year_sales
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (Sales_Date, Sales_Value) AS
SELECT DATE '2019-04-01',  20 FROM DUAL UNION ALL
SELECT DATE '2019-05-01',  50 FROM DUAL UNION ALL
SELECT DATE '2019-06-01',  60 FROM DUAL UNION ALL
SELECT DATE '2019-07-01',  10 FROM DUAL UNION ALL
SELECT DATE '2019-08-01',  20 FROM DUAL UNION ALL
SELECT DATE '2019-09-01',  10 FROM DUAL UNION ALL
SELECT DATE '2020-04-01',  70 FROM DUAL UNION ALL
SELECT DATE '2020-05-01', 100 FROM DUAL UNION ALL
SELECT DATE '2020-06-01',  10 FROM DUAL UNION ALL
SELECT DATE '2020-07-01',  10 FROM DUAL UNION ALL
SELECT DATE '2020-08-01',  20 FROM DUAL UNION ALL
SELECT DATE '2020-09-01',  20 FROM DUAL;

Outputs:

SALES_DATE SALES_VALUE LAST_MONTH_SALES LAST_YEAR_SALES
2019-04-01 00:00:00 20 0 0
2019-05-01 00:00:00 50 20 0
2019-06-01 00:00:00 60 50 0
2019-07-01 00:00:00 10 60 0
2019-08-01 00:00:00 20 10 0
2019-09-01 00:00:00 10 20 0
2020-04-01 00:00:00 70 0 20
2020-05-01 00:00:00 100 70 50
2020-06-01 00:00:00 10 100 60
2020-07-01 00:00:00 10 10 10
2020-08-01 00:00:00 20 10 20
2020-09-01 00:00:00 20 20 10

db<>fiddle here

user_15092640
Related