Solved

oracle query- extension to previous question

Posted on 2015-01-14
22
142 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 73

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

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 73

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

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 73

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 73

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 73

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.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now