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

View Solution Only

PortletPaulEE Topic Advisor

Trusted Expert

Most Valuable Expert 2014

Awarded 2013

Commented: 2015-12-28

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);

anumoses

Author

Commented: 2015-12-29

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.

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?

How is the number 23 relevant to the date 2015-11-20 ?

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
;

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) |

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

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.

Commented: 2015-12-31

thanks

oracle query

Premium Content

Premium Content

Explore our Suite of Services