[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

oracle query- extension to previous question

Posted on 2015-01-14
22
Medium Priority
?
149 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

656 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