Skip to content
Advertisement

Get the data from previous month in sql query

I have a data of all the cost for each month. For example,

I have table of cost for each month

+--------+--------+-----+--------+--------+
| Jan    | Feb    | Mar | Apr    | May    |
+--------+--------+-----+--------+--------+
| 899.20 | 0      | 0   | 0      | 899.20 |
| 439.38 | 485.29 | 0   | 0      | 482.29 |
+--------+--------+-----+--------+--------+

Then what I want in the output for every month that is = ‘0’, it will get the data in the previous month and so on until it’s not equal = ‘0’

Is this possible using oracle sql or using xml.

I tried the oracle sql query below, for example is I want the value of April to be = 899.20

I used the case when condition,

case 
    when Apr = '0' then Mar
    when Mar = '0' then Feb
    when Feb = '0' then Jan
    else Apr
end

and in XML is the if else condition

<?xdofx:if apr = '0' then mar
        else if mar = '0' then feb
        else if feb = '0' then jan
        else apr
end if?>

But both conditions I know didn’t work. Please help

Thank you

Advertisement

Answer

You can use:

SELECT Jan,
       CASE WHEN Feb > 0 THEN Feb
            ELSE Jan END
         AS Feb,
       CASE WHEN Mar > 0 THEN Mar
            WHEN Feb > 0 THEN Feb
            ELSE Jan END
         AS Mar,
       CASE WHEN Apr > 0 THEN Apr
            WHEN Mar > 0 THEN Mar
            WHEN Feb > 0 THEN Feb
            ELSE Jan END
         AS Apr,
       CASE WHEN May > 0 THEN May
            WHEN Apr > 0 THEN Apr
            WHEN Mar > 0 THEN Mar
            WHEN Feb > 0 THEN Feb
            ELSE Jan END
         AS May
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name ( Jan, Feb, Mar, Apr, May ) AS
SELECT 899.20,   0,    0, 0, 899.20 FROM DUAL UNION ALL
SELECT 439.38, 485.29, 0, 0, 482.29 FROM DUAL;

Outputs:

   JAN |    FEB |    MAR |    APR |    MAY
-----: | -----: | -----: | -----: | -----:
 899.2 |  899.2 |  899.2 |  899.2 |  899.2
439.38 | 485.29 | 485.29 | 485.29 | 482.29

db<>fiddle here

2 People found this is helpful
Advertisement