?
Solved

oracle query- extension to previous question

Posted on 2015-01-14
22
Medium Priority
?
150 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month16 days, 9 hours left to enroll

864 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