Solved

oracle query help

Posted on 2014-01-13
10
470 Views
Last Modified: 2014-01-14
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
Comment
Question by:anumoses
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
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
 
LVL 24

Expert Comment

by:chaau
Comment Utility
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
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
 
LVL 6

Author Comment

by:anumoses
Comment Utility
Chaau,

I am getting this error when I run your query

ORA-32033 - Unsupported column aliasing
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
Another question I had asked is that I want the sum for the date range.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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
 
LVL 6

Author Comment

by:anumoses
Comment Utility
@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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
Comment Utility
instead of

 DATE '2014-01-02'  use   :first_date

and

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


  or whatever your variables are.
0
 
LVL 6

Author Closing Comment

by:anumoses
Comment Utility
thanks a lot
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now