Solved

oracle query- extension to previous question

Posted on 2015-01-14
22
145 Views
Last Modified: 2015-01-21
http://www.experts-exchange.com/Database/Oracle/Q_28594986.html

The above is a closed one. I need a slight change and addition

SELECT lt_location_id,
       projected_procs,
       (SELECT COUNT(donor_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 2) AND tab1.location_id = lt_location_id)
           registrations,
       (SELECT COUNT(unit_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 2)
           AND tab1.location_id = lt_location_id
           AND term_date IS NULL
           -- and unit_id is not null
       )
           success_procedures
  FROM (  SELECT s.lt_location_id,
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 2, '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
                 )
                     projected_procs
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('WB', 'PP', 'DR')
             AND g.dept_id = s.dept_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = TRUNC(SYSDATE - 2)
                         AND go.override_goal IS NOT NULL)
        GROUP BY s.lt_location_id
        UNION ALL
          SELECT s.lt_location_id, NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 2)
             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)
 WHERE lt_location_id = 'LOCN100008'; 

Open in new window


The projected_procs, now becomes rbc_projected_procs

WHERE go.proc_code IN ('WB', 'PP', 'DR') now
becomes WHERE go.proc_code IN ('WB', 'DR')

I need an additional column sdp_projected_procs

WHERE go.proc_code IN ('PP')

 
FROM (  SELECT s.lt_location_id,
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 2, '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
                 )
                     projected_procs
            FROM tab2 g, tab3 s
           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 tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = TRUNC(SYSDATE - 2)
                         AND go.override_goal IS NOT NULL)
        GROUP BY s.lt_location_id
        UNION ALL
          SELECT s.lt_location_id, NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('PP')
             AND go.goal_override_date = TRUNC(SYSDATE - 2)
             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)

Open in new window


Now the big select becomes

SELECT lt_location_id,
             rbc_projected_procs,
             sdp_projected_procs,
(select.......
0
Comment
Question by:anumoses
  • 15
  • 7
22 Comments
 
LVL 6

Author Comment

by:anumoses
ID: 40549435
The date in the sample data is different that was given based on the previous question
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40549447
I don't know what you're asking.

You mention a new column but I have no idea from what you posted where it might have come from
0
 
LVL 6

Author Comment

by:anumoses
ID: 40549459
The data is in the sample data tab2,tab3,tab4

Earlier is was

WHERE go.proc_code IN ('WB', 'PP', 'DR') now
becomes WHERE go.proc_code IN ('WB', 'DR')

and the new select will be for PP
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:anumoses
ID: 40549465
SELECT lt_location_id,
       projected_procs,
       (SELECT COUNT(donor_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 4) AND tab1.location_id = lt_location_id)
           registrations,
       (SELECT COUNT(unit_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 4)
           AND tab1.location_id = lt_location_id
           AND term_date IS NULL
           and unit_id is not null
       )
           success_procedures
  FROM (  SELECT s.lt_location_id,
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 4, '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
                 )
                     projected_procs
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('WB', 'PP', 'DR')
             AND g.dept_id = s.dept_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = TRUNC(SYSDATE - 4)
                         AND go.override_goal IS NOT NULL)
        GROUP BY s.lt_location_id
        UNION ALL
          SELECT s.lt_location_id, NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 4)
             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)
 WHERE lt_location_id = 'LOCN100008'; 

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 40549481
expected data

sdp_projected_procs  will be new column with expected data is  30
0
 
LVL 6

Author Comment

by:anumoses
ID: 40549816
I tried this code but getting
ORA-01427: single-row subquery returns more than one row


SELECT lt_location_id,
       rbc_projected_procs,
       (SELECT COUNT(donor_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 4) AND tab1.location_id = lt_location_id)
           registrations,
       (SELECT COUNT(unit_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 4)
           AND tab1.location_id = lt_location_id
           AND term_date IS NULL
           and unit_id is not null
       )
           success_procedures,
   (SELECT 
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 4, '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 tab2 g, tab3 s
           WHERE g.proc_code IN ('PP')
             AND g.dept_id = s.dept_id
			 and s.lt_location_id = 'LOCN100008'
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = TRUNC(SYSDATE - 4)
                         AND go.override_goal IS NOT NULL)
        UNION ALL
          SELECT  NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('PP')  -- IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 4)
             AND go.override_goal IS NOT NULL
             AND go.dept_id = s.dept_id
			 and s.lt_location_id = 'LOCN100008'
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
			   ) 
  FROM (  SELECT s.lt_location_id,
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 4, '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
                 )
                     rbc_projected_procs
            FROM tab2 g, tab3 s
           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 tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = TRUNC(SYSDATE - 4)
                         AND go.override_goal IS NOT NULL)
        GROUP BY s.lt_location_id
        UNION ALL
          SELECT s.lt_location_id, NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('PP') 
             AND go.goal_override_date = TRUNC(SYSDATE - 4)
             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)	
 WHERE lt_location_id = 'LOCN100008'

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 40549820
corrected

SELECT lt_location_id,
       rbc_projected_procs,
       (SELECT COUNT(donor_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 4) AND tab1.location_id = lt_location_id)
           registrations,
       (SELECT COUNT(unit_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 4)
           AND tab1.location_id = lt_location_id
           AND term_date IS NULL
           and unit_id is not null
       )
           success_procedures,
   (SELECT 
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 4, '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
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('PP')
             AND g.dept_id = s.dept_id
			 and s.lt_location_id = 'LOCN100008'
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = TRUNC(SYSDATE - 4)
                         AND go.override_goal IS NOT NULL)
       -- GROUP BY s.lt_location_id
        UNION ALL
          SELECT  NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('PP') -- IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 4)
             AND go.override_goal IS NOT NULL
             AND go.dept_id = s.dept_id
			 and s.lt_location_id = 'LOCN100008'
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
			   )	--and lt_location_id = 'LOCN100008'--sdp   
  FROM (  SELECT s.lt_location_id,
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 4, '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
                 )
                     rbc_projected_procs
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('WB','DR')
             AND g.dept_id = s.dept_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = TRUNC(SYSDATE - 4)
                         AND go.override_goal IS NOT NULL)
        GROUP BY s.lt_location_id
        UNION ALL
          SELECT s.lt_location_id, NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('WB','DR')  -- IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 4)
             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)	
 WHERE lt_location_id = 'LOCN100008'

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 40549880
If I take the union all I get the results but with union all error

SELECT lt_location_id,
       rbc_projected_procs,
       (SELECT COUNT(donor_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 4) AND tab1.location_id = lt_location_id)
           registrations,
       (SELECT COUNT(unit_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 4)
           AND tab1.location_id = lt_location_id
           AND term_date IS NULL
           and unit_id is not null
       )
           success_procedures,
   (SELECT 
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 4, '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
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('PP')
             AND g.dept_id = s.dept_id
			 and s.lt_location_id = 'LOCN100008'
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
						 --and go.proc_code IN ('PP')
                         AND go.goal_override_date = TRUNC(SYSDATE - 4)
                         AND go.override_goal IS NOT NULL)
       -- GROUP BY s.lt_location_id
      /* UNION ALL
          SELECT  NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('PP') -- IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 4)
             AND go.override_goal IS NOT NULL
             AND go.dept_id = s.dept_id
			 and s.lt_location_id = 'LOCN100008'
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')*/
			   ) sdp_projected_procs	
  FROM (  SELECT s.lt_location_id,
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 4, '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
                 )
                     rbc_projected_procs
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('WB','DR')
             AND g.dept_id = s.dept_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = TRUNC(SYSDATE - 4)
                         AND go.override_goal IS NOT NULL)
        GROUP BY s.lt_location_id
        UNION ALL
          SELECT s.lt_location_id, NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('WB','DR')  -- IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 4)
             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)	
 WHERE lt_location_id = 'LOCN100008'

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40551463
running your last query using the data you provided in the previous question all of the numbers come back 0.

what should the results be?
0
 
LVL 6

Author Comment

by:anumoses
ID: 40551482
expected data
instead of sysdate -4 we need to take sysdate - 5 based on the dates in the sample table I gave
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40551506
I get 52 registrations not 60.  

Please use the same data you posted, or, provide new data that matches whatever you are testing with
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40551514
I'm going to assume the 52 is correct based on the previous question.
Instead of doing a UNION, add the 2 sub queries


SELECT lt_location_id,
       rbc_projected_procs,
       (SELECT COUNT(donor_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 5) AND tab1.location_id = lt_location_id)
           registrations,
       (SELECT COUNT(unit_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 5)
           AND tab1.location_id = lt_location_id
           AND term_date IS NULL
           AND unit_id IS NOT NULL)
           success_procedures,
         (SELECT NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 5, '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
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('PP')
             AND g.dept_id = s.dept_id
             AND s.lt_location_id = 'LOCN100008'
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         --and go.proc_code IN ('PP')
                         AND go.goal_override_date = TRUNC(SYSDATE - 5)
                         AND go.override_goal IS NOT NULL))
       + (SELECT NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('PP') -- IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 4)
             AND go.override_goal IS NOT NULL
             AND go.dept_id = s.dept_id
             AND s.lt_location_id = 'LOCN100008'
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120'))
           sdp_projected_procs
  FROM (  SELECT s.lt_location_id,
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 5, '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
                 )
                     rbc_projected_procs
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('WB', 'DR')
             AND g.dept_id = s.dept_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = TRUNC(SYSDATE - 5)
                         AND go.override_goal IS NOT NULL)
        GROUP BY s.lt_location_id
        UNION ALL
          SELECT s.lt_location_id, NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('WB', 'DR') -- IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 5)
             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)
 WHERE lt_location_id = 'LOCN100008'
0
 
LVL 6

Author Comment

by:anumoses
ID: 40551536
SELECT lt_location_id,
       projected_procs,
       (SELECT COUNT(donor_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 5) AND tab1.location_id = lt_location_id)
           registrations,
       (SELECT COUNT(unit_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 5)
           AND tab1.location_id = lt_location_id
           AND term_date IS NULL
           -- and unit_id is not null
       )
           success_procedures
  FROM (  SELECT s.lt_location_id,
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 5, '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
                 )
                     projected_procs
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('WB',  'DR')
             AND g.dept_id = s.dept_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = TRUNC(SYSDATE - 5)
                         AND go.override_goal IS NOT NULL)
        GROUP BY s.lt_location_id
        UNION ALL
          SELECT s.lt_location_id, NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('WB', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 5)
             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)
 WHERE lt_location_id = 'LOCN100008';

Open in new window


expected data
You are correct. Here is the exact query

Now new requirement sdp I have to get 30
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40551553
>>> You are correct. Here is the exact query

I'm not sure what you're trying to tell me there,  your query does NOT produce the same results you posted when using the data you posted.  It returns   48, 52, 52.  not 48, 60, 52

again, please quit posting results that don't apply to the data you have supplied.  It does not help move the question forward, it only introduces doubt.

Either fix your data,  or post new data so we're all using the same thing.

as for the sdp of 30,  Did you try my query above?  http:#a40551514
0
 
LVL 6

Author Comment

by:anumoses
ID: 40551572
There is a confusion. Now the last big select statement that is ( projected_procs)
changes to rbc_projected_procs with one change

WHERE go.proc_code IN ('WB', 'DR') instead of ( new requirement)

WHERE go.proc_code IN ('WB','PP', 'DR') - ( old Code)

so I asked help to add another column called sdp_projected_procs

WHERE go.proc_code IN ('PP')

In the code I gave you I ran for WB and DR. So we get 48 ( total was 78 earlier)

Now 30 will be sdp we I get the query from you. So 48 plus 30 is 78 original query
0
 
LVL 6

Author Comment

by:anumoses
ID: 40551587
Did you try my query above?  http:#a40551514


This works for me. Thanks
0
 
LVL 6

Author Comment

by:anumoses
ID: 40551788
I had one question though. The query you gave me for sdp now runs fine for for one location ie LOCN100008. But if I want to run for all centers, I tried to comment that but I am getting incorrect data. getting 60 for all centers.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40551795
post what you tried
0
 
LVL 6

Author Comment

by:anumoses
ID: 40551799
getting 78 for all centers

SELECT lt_location_id,
       rbc_projected_procs,
       (SELECT COUNT(donor_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 5) AND tab1.location_id = lt_location_id)
           registrations,
       (SELECT COUNT(unit_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 5)
           AND tab1.location_id = lt_location_id
           AND term_date IS NULL
           AND unit_id IS NOT NULL)
           success_procedures,
         (SELECT NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 5, '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
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('PP')
             AND g.dept_id = s.dept_id
            -- AND s.lt_location_id = 'LOCN100008'
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         --and go.proc_code IN ('PP')
                         AND go.goal_override_date = TRUNC(SYSDATE - 5)
                         AND go.override_goal IS NOT NULL))
       + (SELECT NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('PP') -- IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 5)
             AND go.override_goal IS NOT NULL
             AND go.dept_id = s.dept_id
           --  AND s.lt_location_id = 'LOCN100008'
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120'))
           sdp_projected_procs
  FROM (  SELECT s.lt_location_id,
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 1, '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
                 )
                     rbc_projected_procs
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('WB', 'DR')
             AND g.dept_id = s.dept_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = TRUNC(SYSDATE - 5)
                         AND go.override_goal IS NOT NULL)
        GROUP BY s.lt_location_id
        UNION ALL
          SELECT s.lt_location_id, NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('WB', 'DR') -- IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 5)
             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)
 --WHERE lt_location_id = 'LOCN100008' 

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 40551803
SELECT lt_location_id,
       rbc_projected_procs,
       (SELECT COUNT(donor_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 5) AND tab1.location_id = lt_location_id)
           registrations,
       (SELECT COUNT(unit_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 5)
           AND tab1.location_id = lt_location_id
           AND term_date IS NULL
           AND unit_id IS NOT NULL)
           success_procedures,
         (SELECT NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 5, '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
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('PP')
             AND g.dept_id = s.dept_id
            -- AND s.lt_location_id = 'LOCN100008'
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         --and go.proc_code IN ('PP')
                         AND go.goal_override_date = TRUNC(SYSDATE - 5)
                         AND go.override_goal IS NOT NULL))
       + (SELECT NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('PP') -- IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 5)
             AND go.override_goal IS NOT NULL
             AND go.dept_id = s.dept_id
           --  AND s.lt_location_id = 'LOCN100008'
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120'))
           sdp_projected_procs
  FROM (  SELECT s.lt_location_id,
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 5, '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
                 )
                     rbc_projected_procs
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('WB', 'DR')
             AND g.dept_id = s.dept_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = TRUNC(SYSDATE - 5)
                         AND go.override_goal IS NOT NULL)
        GROUP BY s.lt_location_id
        UNION ALL
          SELECT s.lt_location_id, NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('WB', 'DR') -- IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 5)
             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)
 --WHERE lt_location_id = 'LOCN100008' 

Open in new window

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40551950
SELECT lt_location_id,
       rbc_projected_procs,
       (SELECT COUNT(donor_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 5) AND tab1.location_id = x.lt_location_id)
           registrations,
       (SELECT COUNT(unit_id)
          FROM tab1
         WHERE drive_date = TRUNC(SYSDATE - 5)
           AND tab1.location_id = x.lt_location_id
           AND term_date IS NULL
           AND unit_id IS NOT NULL)
           success_procedures,
         (SELECT NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 5, '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
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('PP')
             AND g.dept_id = s.dept_id
             AND s.lt_location_id = x.lt_location_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         --and go.proc_code IN ('PP')
                         AND go.goal_override_date = TRUNC(SYSDATE - 5)
                         AND go.override_goal IS NOT NULL))
       + (SELECT NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('PP') -- IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 5)
             AND go.override_goal IS NOT NULL
             AND go.dept_id = s.dept_id
             AND s.lt_location_id = x.lt_location_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120'))
           sdp_projected_procs
  FROM (  SELECT s.lt_location_id,
                 NVL(
                     SUM(
                         DECODE(
                             TO_CHAR(SYSDATE - 5, '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
                 )
                     rbc_projected_procs
            FROM tab2 g, tab3 s
           WHERE g.proc_code IN ('WB', 'DR')
             AND g.dept_id = s.dept_id
             AND s.lt_location_id NOT IN ('LOCN201749', 'LOCN201489', 'LOCN201120')
             AND NOT EXISTS
                     (SELECT 'x'
                        FROM tab4 go
                       WHERE go.dept_id = g.dept_id
                         AND go.proc_code = g.proc_code
                         AND go.goal_override_date = TRUNC(SYSDATE - 5)
                         AND go.override_goal IS NOT NULL)
        GROUP BY s.lt_location_id
        UNION ALL
          SELECT s.lt_location_id, NVL(SUM(go.override_goal), 0)
            FROM tab4 go, tab3 s
           WHERE go.proc_code IN ('WB', 'DR') -- IN ('WB', 'PP', 'DR')
             AND go.goal_override_date = TRUNC(SYSDATE - 5)
             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) x
--WHERE lt_location_id = 'LOCN100008'
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40562635
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question