How to add the results from a subquery to the main query

if you want to add a summary at the bottom, check the group by rollup syntax
https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets

rollup has the advantage of not having to execute the query twice

people usually add a row with summaries with a union all and a group by

select a,b,c from table
union all
select 'summary', null, sum(c) from table

Select allOpen in new window

if you already have a group by in the initial query then it's very easy

change
group by a, b, c
to
group by rollup ((a,b,c));

if you use (a,b,c) instead of ((a,b,c)) you will get sub summaries too

This will work.. Enjoy !

SELECT * , prt.hourrr
FROM yourtable y
CROSS APPLY
(
	SELECT sum(prt.hours) hourrr
	FROM prod.prtime prt
	WHERE prt.tr_date BETWEEN TRUNC (ADD_MONTHS (SYSDATE, -13), 'MM')  
	AND TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1))+1)
	and prt.pay_sum_grp in ('REG', 'PTO','OT')
) prt

Select allOpen in new window

Just as an aside, why are you doing this:

TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1))+1)

That is a really complicated way of doing this:

trunc(sysdate, 'mm')

Couldn't think of a better way to get the last 12 months worth of data.
I will try your code.

Great ! Thank bro ! Also once you get the proper result please update the accepted & assisted solution.

Thanks !

If you want the last 12 months, then you would need this:

add_months(trunc(sysdate, 'mm'), -12)

Your original date math will return the first day of the current month.

alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
select TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1))+1) your_original,
       trunc(sysdate, 'mm') better_first_of_month,
       add_months(trunc(sysdate, 'mm'), -12) first_of_month_year_ago
  from dual;

Select allOpen in new window

This will return:

YOUR_ORIGINAL       BETTER_FIRST_OF_MON FIRST_OF_MONTH_YEAR
------------------- ------------------- -------------------
09/01/2016 00:00:00 09/01/2016 00:00:00 09/01/2015 00:00:00

Select allOpen in new window

Hey guys,

I've been trying to add the suggestions to my query, but I'm not making any progress.
Here is my full query that I'm trying to add the summary subquery to.
Any suggestions on how to get it to work?

SET NEWPAGE 0
SET SPACE 0
SET PAGES 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
SET COLSEP "|"
SET TRIMSPOOL ON
SET LINES 20000
SPOOL C:\WORK\hartf.txt



        select to_char(replace(emp.fica_nbr,'-'),'999999999')
       ||rpad(substr(emp.employee,1,15),15) 
       ||rpad(substr(emp.last_name,1,30),30,' ')
       ||rpad(substr(emp.first_name,1,30),30,' ')
       ||nvl(emp.middle_name,' ')
       ||lpad(substr(emp.addr1,1,40),40)
       ||lpad(substr(emp.addr2,1,40),40)
       ||lpad(substr(emp.city,1,30),30)
       ||lpad(substr(emp.zip,1,10),10)
       ||'001' 
       ||'  '-- Province Code 
       ||'  '-- Foreign Provinice Name
       ||to_char(pem.birthdate,'YYYYMMDD')
       ||regexp_replace(pem.hm_phone_nbr,'[^0-9]') 
       ||pem.true_mar_stat
       ||pem.sex
       ||rpad(CASE
            WHEN emp.emp_status in ('FB','PN','PB','LW','LP','LS','LT','LE','LI','WA') THEN 'A'
            WHEN emp.emp_status in ('LF','LM','LO','LU','LA','LR') THEN 'L'
            ELSE null
          END,20)-- "Employee Status Code",
       ||to_char(ben.start_date,'YYYYMMDD')
       ||to_char(emp.adj_hire_date,'YYYYMMDD')
       ||to_char(emp.date_hired,'YYYYMMDD')
       ||to_char(emp.annivers_date,'YYYYMMDD')
       ||(CASE
            WHEN emp.nbr_fte < 1 then 'P '
            ELSE 'F '
          END)
       ||to_char(prh.beg_date,'YYYYMMDD')
       ||emp.pro_rate_total
       ||'A' -- Salary Basis
       ||'FL' --Work State Code
       ||'                                        '  --Employee Work Address 1   LPAD( case expression ... end, 20 )
       ||'                                        '  --Employee Work Address 2
       ||'                              '  --Employee Work City
       ||'  '  --Employee Work State
       ||'          '  --Employee Work Postal Code
       ||'   '  --Employee Work Country
       ||'          '  --Employee Work Phone#
       ||lpad(substr(emp.email_address,1,50),50)
       ||lpad(substr(pos.description,1,25),25)
       ||' ' -- Job Code
       ||lpad(substr(pos.job_code,1,10),10)
       ||(CASE
            WHEN emp.exempt_emp = 'Y' then 'E'
            ELSE 'N'
          END)
       ||' '  --Union Member
       ||'                              '  --Union Name
       ||lpad(substr(emp.department,1,10),10)
       ||'          '  --Division ID
       ||rpad(substr(pem.locat_code,1,10),10)  --Location ID
       ||'          '  --Account ID
       ||'          '  --Region ID
       ||'          '  --Company ID
       ||lpad(substr(emp1.last_name,1,30),30,' ')
       ||lpad(substr(emp1.first_name,1,30),30,' ')
       ||lpad(substr(emp1.email_address,1,50),50,' ')
       ||'          '  --Filler
       ||'          '  --Supervisor Phone#
       ||lpad(substr(zzw.lastname,1,30),30,' ')
       ||lpad(substr(zzw.firstname,1,30),30,' ')
       ||lpad(substr(zzw.email,1,50),50,' ')
       ||'           '  --Filler
       ||'          '  --HR Partner Phone#
       ||'LOA                           '
       ||'Administrator                 '
       ||'FMLA@MCH.COM                                      '
       ||'         '  --EFT ABA NUMBER
       ||'                                   '  --EFT Account Number
       ||' '  --EFT Account Type
       ||to_char(ben.start_date,'YYYYMMDD')
       ||'        '
       ||rpad(substr(emp.ot_plan_code,1,5),5)
       ||to_char(ben.start_date,'YYYYMMDD')
       ||'        '
       ||rpad(substr(ben.plan_code,1,5),5)
       ||'   '  --Disability Flex
       ||'   '  --Disability Felx
       ||'   '  --Disability Flex Duration
       ||'      '  --Disability Flex Benefit Amount
       ||'    '  --Filler
       ||'               '  --Last 8 weeks Earning
       ||'               '  --Client Specific field: job Code
       ||'                    '  --Client Specific field
       ||'                    '  --Client Specific Field
       ||rpad(CASE
            WHEN pco.r_type = 'LO' then pco.description
            ELSE null
          END,30)  --Client Specific Field
       ||'                              '  --Client Specific Field
       ||'Y'  --Leave Management Services Indicator
       ||'Y'  --Employee Covered by FML 50/75 Rule
       ||' '  --Key Employee Indicator
       ||'    '  --Sick Leave Hours Remaining
       ||'    '  --Vacation Hours Remaining
       ||' '  --Foreced Eligibility 
       ||'      '  --Last 12 Month Paid Hours
       ||'W  '  --Work Schedule Type
       ||'        '  --Work Schedule from Date
       ||'    '  --Schedule Hours Per Week
       ||'    '  --WS Monday
       ||'    '  --WS Tuesday
       ||'    '  --WS Wednesday
       ||'    '  --WS Thursday
       ||'    '  --WS Friday
       ||'    '  --WS Saturday
       ||'    '  --WS Sunday
       ||'                                                                                                                                                                                                                                                                              '
       
   --     "Output"
 from PROD.EMPLOYEE emp,
      PROD.PAEMPLOYEE pem,
      PROD.BENEFIT ben,
      PROD.PRRATEHIST prh,
      PROD.PAPOSITION pos,
      PROD.HRSUPER hsu,
      PROD.EMPLOYEE emp1,
      PROD.ZZWEBUSER zzw,
      PROD.PCODES pco
--      PROD.PRTIME prt
      
where emp.company = pem.company
and   emp.employee = pem.employee
and   emp.company = ben.company
and   emp.employee = ben.employee
and   emp.company = prh.company 
and   emp.employee = prh.employee
and   emp.company = pos.company
and   emp.position = pos.position
and   emp.company = hsu.company
and   emp.supervisor = hsu.code
and   hsu.company = emp1.company 
and   hsu.employee = emp1.employee 
and   zzw.company = hsu.company
and   upper(zzw.userid) = hsu.user1
and   pem.locat_code = pco.code
--and   emp.company = prt.company 
--and   emp.employee = prt.employee
and   emp.emp_status not in('T','TP','SR','LG','LU')
and   emp.employee = 19330
and   ben.plan_code in ('LTD','LTD1','LTD2')  --is STD not being counted?
AND   ben.STOP_DATE=TO_DATE ('01-01-1700 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
and   prh.beg_date = (select max(prh.beg_date)
          from prod.prratehist prh
          where prh.employee = emp.employee)
and   pos.effect_date = (select max(pos.effect_date)
          from prod.paposition pos
          where pos.position = emp.position)

Select allOpen in new window

Off topic items:

I would like to encourage you to stop trying to solve all the formatting problems UNTIL you have solved ALL the LOGIC problems. It is soooooo much easier to add/debug a simple query than it is to deal with both at the same time. Leave formatting (e.g. to_char, lpad,rpad ) to the very last thing. You can even do that "layer" of formatting "on top" of a "derived table" e.g.

SELECT
      to_char(REPLACE(d.fica_nbr, '-'), '999999999')
    ||rpad(substr(emp.employee,1,15),15) 
FROM ( /* now the "simple query" goes here, no formats are applid, just gather the needed data */
      SELECT
            emp.fica_nbr, emp.employee
      FROM emp
    ) d

Select allOpen in new window

Also, way back in 1992 ANSI standards adopted an improved syntax for joining tables (~24 years ago). A simple way to implement this standard is to deny yourself the use of commas in the FROM clause. What this standard achieves is a more maintainable query and it also vastly simplifies the WHERE clause because join predicates are placed into the JOIN syntax.

SELECT
    *
FROM PROD.EMPLOYEE emp
    INNER JOIN PROD.PAEMPLOYEE pem ON emp.company = pem.company AND emp.employee = pem.employee
    INNER JOIN PROD.BENEFIT ben ON emp.company = ben.company AND emp.employee = ben.employee
    INNER JOIN PROD.PRRATEHIST prh ON emp.company = prh.company AND emp.employee = prh.employee
    INNER JOIN PROD.PAPOSITION pos ON emp.company = pos.company AND emp.position = pos.position
    INNER JOIN PROD.HRSUPER hsu ON emp.company = hsu.company AND emp.supervisor = hsu.code
    INNER JOIN PROD.EMPLOYEE emp1 ON hsu.company = emp1.company AND hsu.employee = emp1.employee
    INNER JOIN PROD.ZZWEBUSER zzw ON hsu.user1 = UPPER(zzw.userid) AND hsu.company = zzw.company
    INNER JOIN PROD.PCODES pco ON pem.locat_code = pco.code
WHERE emp.emp_status NOT IN ('T', 'TP', 'SR', 'LG', 'LU')
    AND emp.employee = 19330
    AND ben.plan_code IN ('LTD', 'LTD1', 'LTD2')  --is STD not being counted?
    AND ben.STOP_DATE = TO_DATE('01-01-1700 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
    AND prh.beg_date = (
          SELECT
                MAX(prh.beg_date)
          FROM prod.prratehist prh
          WHERE prh.employee = emp.employee
    )
    AND pos.effect_date = (
          SELECT
                MAX(pos.effect_date)
          FROM prod.paposition pos
          WHERE pos.position = emp.position
    )

Select allOpen in new window

ps: I placed your 165 line query into a code block to simplify navigation (try to read questions to a phone you will soon learn why 165 lines of code is a pain).