We help IT Professionals succeed at work.
Get Started

oracle query

anumoses
anumoses asked
on
134 Views
Last Modified: 2015-12-31
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

Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE