Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle date question

Posted on 2015-01-23
14
Medium Priority
?
277 Views
Last Modified: 2015-01-27
SELECT-- s.lt_location_id,site_name,
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(:start_date, 'DY'),
                             'SUN', g.day_sunday,
                             'MON', g.day_monday,
                             'TUE', g.day_tuesday,
                             'WED', g.day_wednesday,
                             'THU', g.day_thursday,
                             'FRI', g.day_friday,
                             'SAT', g.day_saturday
                         )
                     ),
                     0
                 )
                     rbc_projected_procs
            FROM subcent_goals_per_day g,
                  sites s
           WHERE g.proc_code IN ('WB', 'DR')
             AND g.dept_id = s.dept_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM subcent_goals_per_day_override go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = :start_date
                         AND go.override_goal IS NOT NULL)

Right now I use one date parameter called :start_date.
I want to use another parameter called :end_date.

Its easy to add between :start_date and :end_date

but in this part I am confused and need help

 DECODE(
                             TO_CHAR(:start_date, 'DY'),
                             'SUN', g.day_sunday,
                             'MON', g.day_monday,........
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
  • 9
  • 5
14 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40566384
DECODE is a built in IF-ELSE function


 DECODE(
                 TO_CHAR(:start_date, 'DY'),
           'SUN', g.day_sunday,     ---    if TO_CHAR(:start_date, 'DY') = 'SUN' then return g.day_sunday
           'MON', g.day_monday,    ---    if TO_CHAR(:start_date, 'DY') = 'MON' then return g.day_monday
           'TUE', g.day_tuesday,    ---    if TO_CHAR(:start_date, 'DY') = 'TUE' then return g.day_tuesday
           'WED', g.day_wednesday,    ---    if TO_CHAR(:start_date, 'DY') = 'WED' then return g.day_wednesday
           'THU', g.day_thursday,    ---    if TO_CHAR(:start_date, 'DY') = 'THU' then return g.day_thursday
            'FRI', g.day_friday,    ---    if TO_CHAR(:start_date, 'DY') = 'FRI' then return g.day_friday
            'SAT', g.day_saturday    ---    if TO_CHAR(:start_date, 'DY') = 'SAT' then return g.day_saturday
)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40566387
the basic idea is,  use the first parameter  as the search value,  search through each pair that follows,  if the

search value matches the first value in the pair, then return the second value, if not keep searching

if you run out of pairs, without finding a match, then DECODE will return NULL

if you search and the last value is a single value and not a pair, that acts as the default return value. when no preceding pairs match

these last two don't apply to you since every day of the week will fall on one of the 7 values you listed, assuming an english language calendar
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40566397
>>> I want to use another parameter called :end_date.

what do you want to do with it?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 6

Author Comment

by:anumoses
ID: 40566425
User is asking for a summary for date range 2nd Jan 2015 to 22 Jan 2015. That is why I did not know how to add between for date ranges in the decode
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40566428
please post some sample data and expected results
0
 
LVL 6

Author Comment

by:anumoses
ID: 40566577
This is how I created sample data

create table col as
select DEPT_ID, PROC_CODE, DAY_SUNDAY, DAY_MONDAY, DAY_TUESDAY,
       DAY_WEDNESDAY, DAY_THURSDAY, DAY_FRIDAY, DAY_SATURDAY
  from subcent_goals_per_day
 where PROC_CODE  IN ('WB','PP','DR')
   AND DEPT_ID in (10,90)      

CREATE TABLE COL
(
  DEPT_ID        NUMBER                         NOT NULL,
  PROC_CODE      VARCHAR2(2 BYTE)               NOT NULL,
  DAY_SUNDAY     NUMBER(4)                      NOT NULL,
  DAY_MONDAY     NUMBER(4)                      NOT NULL,
  DAY_TUESDAY    NUMBER(4)                      NOT NULL,
  DAY_WEDNESDAY  NUMBER(4)                      NOT NULL,
  DAY_THURSDAY   NUMBER(4)                      NOT NULL,
  DAY_FRIDAY     NUMBER(4)                      NOT NULL,
  DAY_SATURDAY   NUMBER(4)                      NOT NULL
)



Insert into COL
   (DEPT_ID, PROC_CODE, DAY_SUNDAY, DAY_MONDAY, DAY_TUESDAY, 
    DAY_WEDNESDAY, DAY_THURSDAY, DAY_FRIDAY, DAY_SATURDAY)
 Values
   (10, 'DR', 0, 2, 2, 
    2, 2, 2, 2);
Insert into COL
   (DEPT_ID, PROC_CODE, DAY_SUNDAY, DAY_MONDAY, DAY_TUESDAY, 
    DAY_WEDNESDAY, DAY_THURSDAY, DAY_FRIDAY, DAY_SATURDAY)
 Values
   (10, 'PP', 0, 24, 24, 
    21, 21, 20, 31);
Insert into COL
   (DEPT_ID, PROC_CODE, DAY_SUNDAY, DAY_MONDAY, DAY_TUESDAY, 
    DAY_WEDNESDAY, DAY_THURSDAY, DAY_FRIDAY, DAY_SATURDAY)
 Values
   (10, 'WB', 0, 36, 36, 
    36, 36, 36, 46);
Insert into COL
   (DEPT_ID, PROC_CODE, DAY_SUNDAY, DAY_MONDAY, DAY_TUESDAY, 
    DAY_WEDNESDAY, DAY_THURSDAY, DAY_FRIDAY, DAY_SATURDAY)
 Values
   (90, 'DR', 0, 1, 1, 
    1, 1, 1, 1);
Insert into COL
   (DEPT_ID, PROC_CODE, DAY_SUNDAY, DAY_MONDAY, DAY_TUESDAY, 
    DAY_WEDNESDAY, DAY_THURSDAY, DAY_FRIDAY, DAY_SATURDAY)
 Values
   (90, 'PP', 0, 5, 5, 
    5, 5, 5, 5);
Insert into COL
   (DEPT_ID, PROC_CODE, DAY_SUNDAY, DAY_MONDAY, DAY_TUESDAY, 
    DAY_WEDNESDAY, DAY_THURSDAY, DAY_FRIDAY, DAY_SATURDAY)
 Values
   (90, 'WB', 0, 27, 27, 
    27, 27, 27, 30);
COMMIT;

Open in new window


If I am taking a date range from Jan 2nd 2015 to Jan 22nd 2015
Jan 2nd was a Friday  and Jan 22nd was a Thursday for the decode. Do I want to be able to use that date range.

Here I can use a date range

 AND go.goal_override_date between :start_date and end_date, but decode is a problem for me
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40566617
that's one of the 3 tables in your query.  what about the other 2?

what are you expecting the results to be?
0
 
LVL 6

Author Comment

by:anumoses
ID: 40566675
CREATE TABLE COL1
(
  LT_LOCATION_ID  VARCHAR2(10 BYTE)             NOT NULL,
  DEPT_ID         NUMBER,
  SITE_NAME       VARCHAR2(90 BYTE)             NOT NULL
)



Insert into COL1
   (LT_LOCATION_ID, DEPT_ID, SITE_NAME)
 Values
   ('LOCN100008', 10, 'Aurora Donor Center');
Insert into COL1
   (LT_LOCATION_ID, DEPT_ID, SITE_NAME)
 Values
   ('LOCN100373', 90, 'Tinley Park Donor Center');
COMMIT;

Open in new window


create table col1 as
select lt_location_id,dept_id,site_name
  from sites
  where dept_id in (10,90)   
  
create table col2 as
 select DEPT_ID, PROC_CODE, GOAL_OVERRIDE_DATE, OVERRIDE_GOAL
   from subcent_goals_per_day_override
  where dept_id in (10,90) 
    and goal_override_date between '02-jan-2015' and '23-jan-2015'  

Open in new window


CREATE TABLE COL2
(
  DEPT_ID             NUMBER                    NOT NULL,
  PROC_CODE           VARCHAR2(2 BYTE)          NOT NULL,
  GOAL_OVERRIDE_DATE  DATE                      NOT NULL,
  OVERRIDE_GOAL       NUMBER(4)
)



Insert into COL2
   (DEPT_ID, PROC_CODE, GOAL_OVERRIDE_DATE, OVERRIDE_GOAL)
 Values
   (10, 'PP', TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 28);
Insert into COL2
   (DEPT_ID, PROC_CODE, GOAL_OVERRIDE_DATE, OVERRIDE_GOAL)
 Values
   (10, 'PP', TO_DATE('01/03/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 34);
Insert into COL2
   (DEPT_ID, PROC_CODE, GOAL_OVERRIDE_DATE, OVERRIDE_GOAL)
 Values
   (10, 'PP', TO_DATE('01/05/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 27);
Insert into COL2
   (DEPT_ID, PROC_CODE, GOAL_OVERRIDE_DATE, OVERRIDE_GOAL)
 Values
   (10, 'PP', TO_DATE('01/06/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 27);
COMMIT;

Open in new window


I cant give expected data as I am not able to add date range for decode.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40566756
>>I cant give expected data as I am not able to add date range for decode.

I'm not asking you to write the code, but do the math yourself.
The current code adds some numbers

For the data you have posted, what numbers should be added?  Not sql, just addition.

Based on your data structures, I don't think you can give the answer you want.
0
 
LVL 6

Author Comment

by:anumoses
ID: 40566799
2nd Jan is Thursday and 22nd Jan is Thursday. So we have to sum
DAY_SUNDAY, DAY_MONDAY, DAY_TUESDAY, DAY_WEDNESDAY, DAY_THURSDAY, DAY_FRIDAY, DAY_SATURDAY
for that date range
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40566810
2nd Jan is Thursday and 22nd Jan is Thursday. So we have to sum
DAY_SUNDAY, DAY_MONDAY, DAY_TUESDAY, DAY_WEDNESDAY, DAY_THURSDAY, DAY_FRIDAY, DAY_SATURDAY
for that date range

I think you just answered your own question.
You can't do that.

Sure you can add up the numbers, but the answer doesn't make sense.

How does DAY_SUNDAY equate to a given date?  Between Jan 2 2014 and Jan 22 2014 there were 3 Sundays,  should that value be added 3 times or just once or some other number?

What if the date range is Jan 2 to Jan 3?  You're only covering 2 days, but the row has 5 days of data.

And most importantly,  how do you relate a date filter on the subcent_goals_per_day_override table to a set of days on the subcent_goals_per_day table when your condition is a NOT exists.

So a date range of Jan 2 - Jan 22 would seem to suggest you want all days EXCEPT that date range.  Which would be an infinite time range.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40566828
Here's a guess at what you might want, if it's not correct, please tell me how you know and tell me what the answer should be


SELECT -- s.lt_location_id,site_name,
      NVL(
           SUM(
               DECODE(
                   TO_CHAR(d, 'DY'),
                   'SUN', g.day_sunday,
                   'MON', g.day_monday,
                   'TUE', g.day_tuesday,
                   'WED', g.day_wednesday,
                   'THU', g.day_thursday,
                   'FRI', g.day_friday,
                   'SAT', g.day_saturday
               )
           ),
           0
       )
           rbc_projected_procs
  FROM subcent_goals_per_day g,
       sites s,
       (    SELECT :start_date + LEVEL - 1 d
              FROM DUAL
        CONNECT BY :start_date + LEVEL - 1 <= :end_date)
 WHERE g.proc_code IN ('WB', 'DR')
   AND g.dept_id = s.dept_id
   AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
   AND NOT EXISTS
           (SELECT 'x'
              FROM subcent_goals_per_day_override go
             WHERE go.dept_id = g.dept_id
               AND go.proc_code = g.proc_code
               AND go.goal_override_date = :start_date
               AND go.override_goal IS NOT NULL)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40566853
I'm not sure if it matters or not, but probably need to change

AND go.goal_override_date = :start_date

to

AND go.goal_override_date between :start_date and :end_date

in my query above
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40572648
Thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

715 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