asked on
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
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;
Oracle Database
Last Comment
anumoses