Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

oracle query help

Posted on 2014-01-13
10
Medium Priority
?
505 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 14

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 14

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

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!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

598 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