Need inputs to write a SQL query

Question

I need help with oracle SQL. The data in the table is as given below.' For given data I need to create a bands based on COB dates range and Values.

COB         VALUE   
-----------------   
4/5/2012    20      
4/6/2012    20      
4/7/2012    22      
4/8/2012    21      
4/9/2012    20      
4/10/2012   21      
4/11/2012   21      
4/12/2012   22      
4/13/2012   20      

The output I am expecting is as given below:            

START_DT    END_DT          VALUE
----------------------------------  
4/5/2012    4/7/2012    20  
4/7/2012    4/8/2012    22  
4/8/2012    4/9/2012    21  
4/9/2012    4/10/2012   20  
4/10/2012   4/12/2012   21  
4/12/2012   4/13/2012   22  
4/13/2012   null        20

Can someone help please?

Rob van Wijk · Accepted Answer · 2012-04-13 13:06:56Z

lag and lead are your friends in this case

select cob start_dt
,      lead(cob) over (order by cob) end_dt
,      value
from
(
    select cob
    ,      value
    ,      lag(value) over (order by cob) prev_value
    from   tmp
)
where prev_value is null
or    value <> prev_value

edit: fixed query :)

Edit by Rob van Wijk: here is some proof that the query works:

SQL> create table mytable(cob,value)
  2  as
  3  select date '2012-04-05', 20 from dual union all
  4  select date '2012-04-06', 20 from dual union all
  5  select date '2012-04-07', 22 from dual union all
  6  select date '2012-04-08', 21 from dual union all
  7  select date '2012-04-09', 20 from dual union all
  8  select date '2012-04-10', 21 from dual union all
  9  select date '2012-04-11', 21 from dual union all
 10  select date '2012-04-12', 22 from dual union all
 11  select date '2012-04-13', 20 from dual
 12  /

Table created.

SQL> select cob start_dt
  2  ,      lead(cob) over (order by cob) end_dt
  3  ,      value
  4  from
  5  (   select cob
  6      ,      value
  7      ,      lag(value) over (order by cob) prev_value
  8      from   mytable
  9  )
 10  where prev_value is null
 11  or    value <> prev_value
 12  /

START_DT            END_DT                   VALUE
------------------- ------------------- ----------
05-04-2012 00:00:00 07-04-2012 00:00:00         20
07-04-2012 00:00:00 08-04-2012 00:00:00         22
08-04-2012 00:00:00 09-04-2012 00:00:00         21
09-04-2012 00:00:00 10-04-2012 00:00:00         20
10-04-2012 00:00:00 12-04-2012 00:00:00         21
12-04-2012 00:00:00 13-04-2012 00:00:00         22
13-04-2012 00:00:00                             20

7 rows selected.

aF. · Accepted Answer · 2012-04-13 12:14:56Z

0

In pseudo-code it can be done with something like this:

startDate = min(COB) from table
actualDate = min(COB) from table
lastDate = max(COB) from table

lastValue = value from actualDate


while actualDate <= lastDate
  actualValue = value from actualDate

  if actualValue <> lastValue
    insert in temptable startdate, actualdate, actualValue
    startdate = actualdate

  actualdate = actualdate + 1 day



insert in temptable startdate, null, actualValue

select * from temptable

answered Apr 13, 2012 at 12:14

aF.

67k45 gold badges141 silver badges201 bronze badges

Collectives™ on Stack Overflow

Need inputs to write a SQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related