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;
SQL Fiddle
ASKER
ASKER
ASKER
ASKER
ASKER
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
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
TRUSTED BY
Open in new window