CODEWITHSUNDEEP
sqloracle-databaseoracle11gdate-arithmetic
simplify_life
simplify_life

Reputation: 405

oracle month to day

I have data as below in which data is splitted as below where month 1 represent JAN ,Month 2 represent FEB and so on.

MONTH   VALUE
1        93
2        56
3       186
4        60

Now I need to calculate sum of value based on number of days.

Ex- If date is between Jan 1st to March 31st then SUM is 335(93+56+186)

If date is between Jan 17th to March 31st then sum is 287(45+56+186) .

Here 45 for month of january is dervied from average per day for month of JAN (93/31==3) .Multiplied by number of days in January for the required period.

Ignore leap year and FEB month can be taken 28 days always.

Upvotes: 2

Views: 198

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

As one of the approaches, you can turn a month into a list of days(dates) that constitute it (ease filtering operation), and perform calculation as follows:

/* sample of data that you've provided */
with t1(mnth,val) as(
  select 1, 93  from dual union all
  select 2, 56  from dual union all
  select 3, 186 from dual union all
  select 4, 60  from dual
), 
/*
    Generates current year dates 
    From January 1st 2014 to December 31st 2014  
 */
dates(dt) as(
  select trunc(sysdate, 'YEAR') - 1 + level
    from dual
  connect by extract(year from (trunc(sysdate, 'YEAR') - 1 + level)) <= 
             extract(year from sysdate)
)
/* 
   The query that performs calculations based on range of dates 
 */
select sum(val / extract(day from last_day(dt))) as result
  from dates d
  join t1
    on (extract(month from d.dt) = t1.mnth)
 where dt between date '2014-01-17' and        -- January 17th 2014 to    
                  date '2014-03-31'            -- March 31st 2014

Result:

    RESULT
----------
       287

Upvotes: 3

Related Questions