Set date to last day of previous month in Oracle if it's not the last row

Question

Whole setup on SQL Fiddle: http://sqlfiddle.com/#!4/1fd0e/5

I have some data containing persons id, level and the levels date range like shown below:

   PID        LVL START_DATE END_DATE

     1          1 01.01.14   19.03.14 
     1          2 20.03.14   15.08.14 
     1          3 16.08.14   09.10.14 
     1          4 10.10.14   31.12.14 
     2          1 01.01.14   31.12.14 
     3          1 01.01.14   16.01.14

I need to set the start date to the first day of month and the end date to the last day of month. the last day rule applies only if it ist not the last row of data for that person.

what i've don so far:

select
pid, lvl, 
trunc(start_date, 'month') as start_date,
case when lead(pid, 1) over (PARTITION BY pid order by end_date) is not null 
     then last_day(add_months(end_date, -1)) 
     else last_day(end_date) 
     end as end_date
from date_tbl t;

gives me the desired output:

   PID        LVL START_DATE END_DATE

     1          1 01.01.14   28.02.14 
     1          2 01.03.14   31.07.14 
     1          3 01.08.14   30.09.14 
     1          4 01.10.14   31.12.14 
     2          1 01.01.14   31.12.14 
     3          1 01.01.14   31.01.14

BUT: It just works well with my test-data. On my production data on a table containing 25k+ rows of data (witch is not too much data i'd say) it performs really slow.

Can anyone give me a hint how I could improve the query's performance? What indices to set on wich columns for example...? The only indexed column so far is the PID column.

How slow is "really slow"? And what is the full query? Since there are 25K rows I assume they are not all just displayed on a screen. Is there an `UPDATE` statement, or is there a predicate to limit the results for a specific person? — Jon Heller, Apr 29 '14 at 18:08

score 0 · Answer 1 · 2014-04-29T18:44:29.463

0

Actually, as I understand, your script produces wrong result if person has only one record (case with pid = 3)

Please, could you try this one?

select
  pid, 
  lvl, 
  trunc(start_date, 'month') as start_date,
  last_day(add_months(end_date, case when lvl = max(lvl) over (partition by pid) then 0 else -1 end)) end_date
from date_tbl t;

I guess that you need to build index for columns (pid, lvl desc)

edited Apr 29 '14 at 18:44

answered Apr 29 '14 at 18:36

thx for answer. the pid 3 record ist correct: it is valid for one month only. the records have to be monthly valid. – Jakob Apr 30 '14 at 06:23

Jakob · Accepted Answer · 2014-07-02T06:04:38.693

Ok guys, sorry for waisting your time. To make it short: it was my fault. In my procedure the query above makes a LEFT JOIN to another table in some subquery:

with dates as (
  select
  pid, lvl, 
  trunc(start_date, 'month') as start_date,
  case when lead(pid, 1) over (PARTITION BY pid order by end_date) is not null 
       then last_day(add_months(end_date, -1)) 
       else last_day(end_date) 
       end as end_date
  from date_tbl t
),
  some_other_table as (
  select pid, (...some more columns)
  from other_table
)
select * from (
  select 
    b.pid,  -- <== this has to be a.pid. b is much bigger than a!
    a.start_date, 
    a.end_date
  from dates a left join some_other_table b on a.pid = b.pid
)

The whole query is much bigger.

@jonearles thx for your comment. "And what is the full query?" helped me to get back on track: split the query into pieces and check again what REALLY slows it down.

Set date to last day of previous month in Oracle if it's not the last row

2 Answers2