troubleshooting Question

oracle query

Avatar of anumoses
anumosesFlag for United States of America asked on
Oracle Database
7 Comments1 Solution137 ViewsLast Modified:
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
 	  	

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
 	  	

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;

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;
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros