asked on 1/13/2014

oracle query help

I have enclosed sample data and also table structure. I need help in getting the query.

select dept_id,proc_code,override_goal,goal_override_date
from table2
where goal_override_date between '02-jan-2014' and '11-jan-2014'
and dept_id = 10
and proc_code = 'CP'

select DEPT_ID, PROC_CODE, DAY_SUNDAY, DAY_MONDAY,
DAY_TUESDAY, DAY_WEDNESDAY, DAY_THURSDAY,
DAY_FRIDAY, DAY_SATURDAY
from table1
where dept_id =10
and proc_code = 'CP'; --

Table1 is kind of maintenance table.
In Table2 values can be overridden.

Requirement

Check to see if there is data in table 2 for the date range . If table2 has no value then take value from table1 for that day the date falls into. Any more clarification please ask me.
Sundays are all zeros.
I want this data. and the sum for the date range.
2-jan-2014 - 3
3-jan-2014 - 3
4-jan-2014 - 3
5-jan-2014 - 0
6-jan-2014 - 1
7-jan-2014 - 3
8-jan-2014 - 5
9-jan-2014 - 5
10-jan-2014 - 3
11-jan-2014 - 3
table-script.txt
screen.JPG

Oracle Database

Last Comment

Alex [***Alex140181***]

1/14/2014

Alex [***Alex140181***]

1/13/2014

You're looking for something like this (I think it still can be enhanced):

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;

Open in new window

chaau

1/13/2014

Similar result with ANSI Recursive CTE syntax:

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;

Open in new window

SQL Fiddle

anumoses

1/14/2014

ASKER

anumoses

1/14/2014

ASKER

Chaau,

I am getting this error when I run your query

ORA-32033 - Unsupported column aliasing

anumoses

1/14/2014

ASKER

Another question I had asked is that I want the sum for the date range.

ASKER CERTIFIED SOLUTION

Sean Stuber

1/14/2014

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

anumoses

1/14/2014

ASKER

@sdstuber

Your query is correct and very helpful. If I have to add parameters to the date fields instead of hard coding, how to I add that in your query?

SOLUTION

Sean Stuber

1/14/2014

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

anumoses

1/14/2014

ASKER

thanks a lot

Alex [***Alex140181***]

1/14/2014

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

I cannot confirm that, see attached screenshot!