Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

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
0
anumoses
Asked:
anumoses
  • 5
  • 2
  • 2
  • +1
2 Solutions
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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

0
 
chaauCommented:
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
0
 
anumosesAuthor Commented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
anumosesAuthor Commented:
Chaau,

I am getting this error when I run your query

ORA-32033 - Unsupported column aliasing
0
 
anumosesAuthor Commented:
Another question I had asked is that I want the sum for the date range.
0
 
sdstuberCommented:
SELECT goal_override_date, override_goal, SUM(override_goal) OVER () total
    FROM (SELECT d goal_override_date,
                 NVL(
                     override_goal,
                     DECODE(
                         TO_CHAR(d, 'Dy'),
                         'Sun', day_sunday,
                         'Mon', day_monday,
                         'Tue', day_tuesday,
                         'Wed', day_wednesday,
                         'Thu', day_thursday,
                         'Fri', day_friday,
                         'Sat', day_saturday
                     )
                 )
                     override_goal
            FROM table2 t2
                 RIGHT JOIN (    SELECT DATE '2014-01-02' + LEVEL - 1 d
                                   FROM DUAL
                             CONNECT BY DATE '2014-01-02' + LEVEL - 1 <= DATE '2014-01-11')
                     ON t2.goal_override_date = d AND t2.dept_id = 10 AND t2.proc_code = 'CP'
                 CROSS JOIN table1 t1
           WHERE t1.dept_id = 10 AND t1.proc_code = 'CP')
ORDER BY goal_override_date
0
 
anumosesAuthor Commented:
@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?
0
 
sdstuberCommented:
instead of

 DATE '2014-01-02'  use   :first_date

and

 DATE '2014-01-11'   use :last date


  or whatever your variables are.
0
 
anumosesAuthor Commented:
thanks a lot
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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!
Screenshot from my FIRST post
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now