oracle query

SELECT distinct site_name,lt_location_id,
         sdp_projected_procs,
  (SELECT COUNT(donor_id)
     from donations_don do,
          drives@pdon_new dr,
  	     locations@pdon_new l
    where do.drive_id = dr.drive_id
      and l.location_id = dr.location_id	
      and drive_date between :start_date and :end_date
      and l.term_date is  null 
      and location_type = 'FIX'
      and procedure_code in (select code 
                               from master_codes
                              where code_type = 'PROC'
   			                   and udf1 in ('SDP')) 
      and l.location_id = x.lt_location_id)	sdp_registrations,
  (SELECT COUNT(unit_id)
     from donations_don do,
          drives@pdon_new dr,
 	      locations@pdon_new l
    where do.drive_id = dr.drive_id
      and l.location_id = dr.location_id	
      and drive_date between :start_date and :end_date
      and l.term_date is  null 
      and location_type = 'FIX'
      and do.unit_id is not null
      and procedure_code in (select code 
                               from master_codes
                              where code_type = 'PROC'
 			                    and udf1 in ('SDP')) 
      and transaction_id not in (select transaction_id
			        from donation_attributes_don da
			     where do.transaction_id = da.transaction_id(+) 
                                                       AND da.attrib_value IN ( '04', '05' ) 
                                                       AND da.attrib_code = 'VENI' )
      and l.location_id = x.lt_location_id) sdp_procs,
  (select SUM(CASE 
              WHEN da.attrib_value IN ( '04', '05' ) THEN 1 
              ELSE 0 
            END) 
     FROM   (drives@pdon_new bd 
            join donations_don d ON bd.drive_id = d.drive_id
            JOIN locations@pdon_new l on l.location_id = bd.location_id) 
            left outer join donation_attributes_don da 
              ON ( d.transaction_id = da.transaction_id 
             AND da.attrib_value IN ( '04', '05' ) 
             AND da.attrib_code = 'VENI' ) 
          WHERE  bd.drive_date between :start_date and :end_date
            and d.unit_id is not null
            and bd.drive_id like 'DRV5%'
            and procedure_code in (select code 
                                     from master_codes
                                    where code_type = 'PROC'
 		                              and udf1 in ('SDP'))
 	        and bd.location_id = x.lt_location_id) sdp_qns,
  (SELECT COUNT(deferral_id) 
     from donations_don do,
          drives@pdon_new dr,
 	      locations@pdon_new l
    where do.drive_id = dr.drive_id
      and l.location_id = dr.location_id	
      and drive_date between :start_date and :end_date
      and l.term_date is  null 
      and location_type = 'FIX'
      and (unit_id is null or deferral_id is not null )
      and exists(select donor_id
 		           from hbc_donor_schedule hds
 				  where hds.donor_id = do.donor_id
 					and sch_date = coll_date
 					and status = 'CNFRM'
 					and proc_code = 'PP')
      and dr.location_id = x.lt_location_id) sdp_deferrals, 
  (select SUM(CASE 
              WHEN ua.attrib_code IN ( 'CX' ) THEN 2 
 		      WHEN ua.attrib_code IN ( 'DP' ) THEN 2 
 		      WHEN ua.attrib_code IN ( 'RD' ) THEN 2 
 		      WHEN ua.attrib_code IN ( 'TP' ) THEN 3 
 		      WHEN ua.attrib_code IN ( 'CX' ) THEN 2 
 		      WHEN ua.attrib_code IN ( 'CX' ) THEN 2 
              ELSE 1 
              END)
     FROM   (drives@pdon_new bd 
             join donations_don d ON bd.drive_id = d.drive_id
 		     JOIN locations@pdon_new l on l.location_id = bd.location_id	  
 		     JOIN unit_attributes@plab.world ua on d.unit_id = ua.unit_id) 
    WHERE  bd.drive_date between :start_date and :end_date 
       and bd.drive_id like 'DRV5%'
       and d.unit_id is not null
       and procedure_code in (select code 
                                from master_codes
                               where code_type = 'PROC'
 		                         and udf1 in ('SDP'))
 	   and attrib_group = 'PHER'
                   and transaction_id not in (select transaction_id
			        from donation_attributes_don da
			     where d.transaction_id = da.transaction_id(+) 
                                                       AND da.attrib_value IN ( '04', '05' ) 
                                                       AND da.attrib_code = 'VENI' )
 	   and bd.location_id = x.lt_location_id) sdp_prod      
  FROM (  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
       )
           sdp_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 ('PP')
   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) 
        GROUP BY s.lt_location_id,site_name
        UNION ALL
          SELECT s.lt_location_id,site_name, NVL(SUM(go.override_goal), 0)
            FROM subcent_goals_per_day_override go,
                  sites s
           WHERE go.proc_code IN ('PP') 
             AND go.goal_override_date between :start_date and :end_date
             and  go.goal_override_date != '04-JUL-2015'
             AND go.override_goal IS NOT NULL
             AND go.dept_id = s.dept_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
        GROUP BY s.lt_location_id,site_name) x
 		order by 1
 	  	

Open in new window


start date is 01-nov-2015
end date is 30-nov-2015

I need help in the sdp_projected_procs part of the query that is in the end.


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
       )
           sdp_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 ('PP')
   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) 
        GROUP BY s.lt_location_id,site_name
        UNION ALL
          SELECT s.lt_location_id,site_name, NVL(SUM(go.override_goal), 0)
            FROM subcent_goals_per_day_override go,
                  sites s
           WHERE go.proc_code IN ('PP') 
             AND go.goal_override_date between :start_date and :end_date
             and  go.goal_override_date != '04-JUL-2015'
             AND go.override_goal IS NOT NULL
             AND go.dept_id = s.dept_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
        GROUP BY s.lt_location_id,site_name
 		order by 1
 	  	

Open in new window


including sample data from 2 tables.

Id goals are present in override table then take tht values else take value from goals table.

CREATE TABLE OVERRIDE_SAMPLE
(
  DEPT_ID             NUMBER                    NOT NULL,
  PROC_CODE           VARCHAR2(2 BYTE)          NOT NULL,
  GOAL_OVERRIDE_DATE  DATE                      NOT NULL,
  OVERRIDE_GOAL       NUMBER(4)
)
TABLESPACE PHBC_DAT1
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;



Insert into OVERRIDE_SAMPLE
   (DEPT_ID, PROC_CODE, GOAL_OVERRIDE_DATE, OVERRIDE_GOAL)
 Values
   (10, 'PP', TO_DATE('11/20/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 23);
Insert into OVERRIDE_SAMPLE
   (DEPT_ID, PROC_CODE, GOAL_OVERRIDE_DATE, OVERRIDE_GOAL)
 Values
   (10, 'PP', TO_DATE('11/21/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 35);
Insert into OVERRIDE_SAMPLE
   (DEPT_ID, PROC_CODE, GOAL_OVERRIDE_DATE, OVERRIDE_GOAL)
 Values
   (10, 'PP', TO_DATE('11/23/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30);
Insert into OVERRIDE_SAMPLE
   (DEPT_ID, PROC_CODE, GOAL_OVERRIDE_DATE, OVERRIDE_GOAL)
 Values
   (10, 'PP', TO_DATE('11/24/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 12);
Insert into OVERRIDE_SAMPLE
   (DEPT_ID, PROC_CODE, GOAL_OVERRIDE_DATE, OVERRIDE_GOAL)
 Values
   (10, 'PP', TO_DATE('11/25/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 12);
Insert into OVERRIDE_SAMPLE
   (DEPT_ID, PROC_CODE, GOAL_OVERRIDE_DATE, OVERRIDE_GOAL)
 Values
   (10, 'PP', TO_DATE('11/27/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 29);
Insert into OVERRIDE_SAMPLE
   (DEPT_ID, PROC_CODE, GOAL_OVERRIDE_DATE, OVERRIDE_GOAL)
 Values
   (10, 'PP', TO_DATE('11/28/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 35);
Insert into OVERRIDE_SAMPLE
   (DEPT_ID, PROC_CODE, GOAL_OVERRIDE_DATE, OVERRIDE_GOAL)
 Values
   (10, 'PP', TO_DATE('11/30/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39);
COMMIT;

Open in new window


CREATE TABLE SUBCENT_GOALS_PER_DAY_SAMPLE
(
  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
)
TABLESPACE PHBC_DAT1
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;



Insert into SUBCENT_GOALS_PER_DAY_SAMPLE
   (DEPT_ID, PROC_CODE, DAY_SUNDAY, DAY_MONDAY, DAY_TUESDAY, 
    DAY_WEDNESDAY, DAY_THURSDAY, DAY_FRIDAY, DAY_SATURDAY)
 Values
   (10, 'PP', 0, 20, 20, 
    18, 18, 19, 26);
COMMIT;

Open in new window

LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
You want to compare these columns
  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
of table SUBCENT_GOALS_PER_DAY_SAMPLE

to  GOAL_OVERRIDE_DATE  DATE  of table OVERRIDE_SAMPLE

Please explain how the number values (in bold) can be compared to a date?

Insert into SUBCENT_GOALS_PER_DAY_SAMPLE
   (DEPT_ID, PROC_CODE, DAY_SUNDAY, DAY_MONDAY, DAY_TUESDAY,
    DAY_WEDNESDAY, DAY_THURSDAY, DAY_FRIDAY, DAY_SATURDAY)
 Values
   (10, 'PP', 0, 20, 20, 18, 18, 19, 26);
anumosesAuthor Commented:
Is there a way in the query to check if the date ( is a monday)  is one of the days like day_monday then take the value for that day? Since I had the same issue, on conversion I put the question to get help from experts.
anumosesAuthor Commented:
In OVERRIDE_SAMPLE table
      
      we have 11/20/2015 date and the day is friday
      
      So in subcent_goals_per_day_sample table day_friday value = 19
      
      This value has to be replaced by 23 after getting the date to convert to day.
      
      Can you help me with this?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

PortletPaulEE Topic AdvisorCommented:
How is the number 23 relevant to the date 2015-11-20 ?
PortletPaulEE Topic AdvisorCommented:
It's OK I can see where the 23 comes from now (a different column, OVERRIDE_GOAL)

My instincts about this question is that the design of SUBCENT_GOALS_PER_DAY_SAMPLE is not good, but as asked, this is how I would approach it:

Firstly I would "transform" OVERRIDE_SAMPLE so that result has the same denormalized design as SUBCENT_GOALS_PER_DAY_SAMPLE
select
      DEPT_ID, PROC_CODE
    , MAX(case when TRUNC (GOAL_OVERRIDE_DATE)  - TRUNC (GOAL_OVERRIDE_DATE, 'IW') = 0 then OVERRIDE_GOAL end) as DAY_MONDAY 
    , MAX(case when TRUNC (GOAL_OVERRIDE_DATE)  - TRUNC (GOAL_OVERRIDE_DATE, 'IW') = 1 then OVERRIDE_GOAL end) as DAY_TUESDAY 
    , MAX(case when TRUNC (GOAL_OVERRIDE_DATE)  - TRUNC (GOAL_OVERRIDE_DATE, 'IW') = 2 then OVERRIDE_GOAL end) as DAY_WEDNESDAY 
    , MAX(case when TRUNC (GOAL_OVERRIDE_DATE)  - TRUNC (GOAL_OVERRIDE_DATE, 'IW') = 3 then OVERRIDE_GOAL end) as DAY_THURSDAY 
    , MAX(case when TRUNC (GOAL_OVERRIDE_DATE)  - TRUNC (GOAL_OVERRIDE_DATE, 'IW') = 4 then OVERRIDE_GOAL end) as DAY_FRIDAY 
    , MAX(case when TRUNC (GOAL_OVERRIDE_DATE)  - TRUNC (GOAL_OVERRIDE_DATE, 'IW') = 5 then OVERRIDE_GOAL end) as DAY_SATURDAY 
    , MAX(case when TRUNC (GOAL_OVERRIDE_DATE)  - TRUNC (GOAL_OVERRIDE_DATE, 'IW') = 6 then OVERRIDE_GOAL end) as DAY_SUNDAY 
from OVERRIDE_SAMPLE 
group by
   DEPT_ID, PROC_CODE
;  

Open in new window

On your sample data that result looks like this:
| DEPT_ID | PROC_CODE | DAY_MONDAY | DAY_TUESDAY | DAY_WEDNESDAY | DAY_THURSDAY | DAY_FRIDAY | DAY_SATURDAY | DAY_SUNDAY |
|---------|-----------|------------|-------------|---------------|--------------|------------|--------------|------------|
|      10 |        PP |         39 |          12 |            12 |       (null) |         29 |           35 |     (null) |

Open in new window

So now you can update (or perhaps insert into?) SUBCENT_GOALS_PER_DAY_SAMPLE from that result.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
anumoses

I live in Australia and my timezone is Australian Eastern Daylight Savings Time (AEDST)

look for Sydney on this page http://everytimezone.com/ (it's the right timezone, but not my city)

It's also Summer holiday time here.
anumosesAuthor Commented:
thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.