oracle query help

select *
  from table1;

select *
  from table2;

select to_char(sysdate, 'D')
  from dual;

with days as
 (select to_date('01 2014', 'MM YYYY') - 1 + level as DateRange,
         t1.*
    from table1 t1
   where (to_date('01 2014', 'MM YYYY') - 1 + level) <= last_day(to_date('01 2014', 'MM YYYY'))
  connect by level <= 31)
select a.daterange,
       case
         when b.override_goal is not null then
          b.override_goal
         else
          case to_number(to_char(a.daterange, 'D'))
            when 1 then
             a.day_monday
            when 2 then
             a.day_tuesday
            when 3 then
             a.day_wednesday
            when 4 then
             a.day_thursday
            when 5 then
             a.day_friday
            when 6 then
             a.day_saturday
            when 7 then
             a.day_sunday
          end
       end override_goal
  from table2 b,
       days a
 where a.daterange >= to_date('02.01.2014', 'dd.mm.yyyy')
   and a.daterange < to_date('12.01.2014', 'dd.mm.yyyy')
   and b.goal_override_date(+) = a.daterange
 order by a.daterange;

Select all Open in new window

with dates (dt) as 
(SELECT '2014-01-11' AS dt FROM DUAL
 UNION ALL 
 SELECT to_char(to_date(dt, 'YYYY-MM-DD') - interval '1' day, 'YYYY-MM-DD') FROM dates 
  WHERE to_date(dt, 'YYYY-MM-DD') > to_date('2014-01-02', 'YYYY-MM-DD') )
SELECT to_date(d.dt, 'YYYY-MM-DD'), 
NVL(T2.OVERRIDE_GOAL,   CASE to_number(to_char(to_date(d.dt, 'YYYY-MM-DD'), 'D')) 
                          WHEN 2 THEN T1.DAY_MONDAY
                          WHEN 3 THEN T1.DAY_TUESDAY
                          WHEN 4 THEN T1.DAY_WEDNESDAY
                          WHEN 5 THEN T1.DAY_THURSDAY
                          WHEN 6 THEN T1.DAY_FRIDAY
                          WHEN 7 THEN T1.DAY_SATURDAY
                          WHEN 1 THEN T1.DAY_SUNDAY
                          ELSE 0 END)
from dates d LEFT JOIN Table2 T2 ON to_date(d.dt, 'YYYY-MM-DD') = T2.GOAL_OVERRIDE_DATE
AND T2.dept_id = 10 and T2.proc_code = 'CP'
LEFT JOIN Table1 T1 ON T1.dept_id = 10 and T1.proc_code = 'CP'
ORDER BY 1;

Select all Open in new window

Alexander,

Your query gave me this result

DATERANGE|OVERRIDE_GOAL
1/2/2014|3  correct
1/3/2014|3  correct
1/4/2014|0  wrong has to be 3 saturday value
1/5/2014|3  wrong has to be 0 sunday value
1/6/2014|1  correct
1/7/2014|3  correct
1/8/2014|5  correct
1/9/2014|5  correct
1/10/2014|3  correct
1/11/2014|3  correct