SQL Oracle - Using RowNum in Query

Question

I have two tables, examples as follows.

table_1
days        special_day
10/09/2013     Y
10/10/2013     N
10/11/2013     Y
10/12/2013     N
10/13/2013     N
10/14/2013     Y

table_2
id          special_day_ind      numdays         order
123            Y                    3              2
456            N                    5              1

My query would have to select the difference between sysday and the correct date from table_1 based on the parameters in table_2. If special_day_ind is 'Y', then I need 3 (numdays) special_days back from sysdate. If 'N', the numdays is the answer. Results would be ORDER(ed) BY order asc(ending).

In the above tables example, the query would return back.

sysdate = 10/14/2013

 id     days 
456     5
123     5    (10/14/2013 - 10/9/2013)

It seems like ROWNUM would do the trick, however with the differing 'ways' of counting, I'm not sure how to proceed.

Noel Noel · Accepted Answer · 2013-10-15T05:27:18

Here's a way to do it.

You need to assign a row number to special days in table_1.

select days,
       row_number() over (order by days desc) r
  from table_1
 where special_day = 'Y';

Using this as CTE, you can find the earlier special days and subtract it from the sysdate.

with x as(
  select days,
         row_number() over (order by days desc) r
    from table_1
   where special_day = 'Y'
  )
select id, 
       case when special_day_ind = 'N'
            then numdays
            when special_day_ind = 'Y'
            then trunc(sysdate) - (select days
                                     from x
                                    where r = numdays)
       end days
  from table_2
 order by order_;

Demo.

SQL Oracle - Using RowNum in Query

1 Answers