?
Solved

oracle query help

Posted on 2014-01-13
10
Medium Priority
?
498 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39777904
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 25

Expert Comment

by:chaau
ID: 39778003
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
ID: 39779027
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 6

Author Comment

by:anumoses
ID: 39779031
Chaau,

I am getting this error when I run your query

ORA-32033 - Unsupported column aliasing
0
 
LVL 6

Author Comment

by:anumoses
ID: 39779071
Another question I had asked is that I want the sum for the date range.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39779193
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
ID: 39779229
@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 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 39779300
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
ID: 39779537
thanks a lot
0
 
LVL 13

Expert Comment

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

752 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