SQL Query Help

[gtb12314]

Hi Everyone,

I am new to oracle please help me out.

Below is my sql command, now I want to add a field ptobalance for each employee but each employee may have more than one pto (paid time off) plan & I think I have to summarize it.
---------------------------------------------------
SELECT al.process_level,al.deptname,al.department,al.emp_status,al.fte_total,al.employee,prt.hours,prt.per_end_date
FROM lrsuser.v_associatelist al,lawson.prtime prt
where prt.company=al.company
AND
prt.employee=al.employee
AND
al.group_name='G:ACTIVE'
AND
prt.pay_sum_grp IN ('FLP','PTS','PTU')
AND
al.emp_status IN ('CF','E9','EF','N9','NF','CA','EA','NA','CB','EB','NB')

AND
TO_CHAR(prt.per_end_date, 'YYYY') = '2007';

----------------------------

Please suggest me.


Thanks in advance!!!!

 

[SantaMufasa]

but each employee may have more than one pto (paid time off) plan & I think I have to summarize it.

It is not obvious, from your description, anything about "pto" or how you want "to summarize it", if there are duplicates for one employee.

It would be very helpful if you show how your output looks not, then how you want the output to appear following code modification (if there is more than one pto).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[gtb12314]

I am trying to display in my report the pto balances for each employee. pto balance is coming from the field called
"elig_balance" .

For your info the pto plan name is a primary key field. And an employee may have more than one pto plan.

So far I am not sure how my output will be,trying to figure out.

 

[gtb12314]

elig_balance field is coming from another table called "master".

 

[gtb12314]

For your info. pto balance field called "elig_balance" is coming from a different table called "emtamastr". I wrote below sql to get the PTO balance only for one employee but I want for each employee & if a employee has more than one pto plan then it should add all.

select plan.company,plan.employee,plan.plan_name,mastr.elig_balance
from v_hr_ptoplan plan inner join lawson.emtamastr mastr
on plan.company=mastr.company
and plan.employee=mastr.employee
where plan.employee=3113456
and plan.plan_name=mastr.plan_name;

And I want to add the above query to my original query,I mean I want it in one sql command.

Sorry for any inconvenience.

Thanks in advance!!!

 

[HarleyQuinn]

I think as a start you should look into aggregate functions, specifically SUM()

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[smcnulty2000]

well, you can certainly consider something in this direction:

SELECT al.process_level,
       al.deptname,
       al.department,
       al.emp_status,
       al.fte_total,
       al.employee,
       prt.hours,
       prt.per_end_date,
       /* The new part, A summary subquery */
       (select sum(mastr.elig_balance)
          from lawson.emtamastr mastr
         where al.company = mastr.company
           and al.employee = mastr.employee) elig_bal_sum

  FROM lrsuser.v_associatelist al, lawson.prtime prt
 where prt.company = al.company
   AND prt.employee = al.employee
   AND al.group_name = 'G:ACTIVE'
   AND prt.pay_sum_grp IN ('FLP', 'PTS', 'PTU')
   AND al.emp_status IN
       ('CF', 'E9', 'EF', 'N9', 'NF', 'CA', 'EA', 'NA', 'CB', 'EB', 'NB')
      
   AND TO_CHAR(prt.per_end_date, 'YYYY') = '2007';

If you choose to use a subquery in this way, it has to return only one value per line for the master line. Sum will make that happen, of course, so this should work. As would Max, or any other summary function. I won't overload this post with advice on how to use this method since you say you are just getting started.

I'm not 100% convinced that's what you need but maybe it'll get you thinking in a different direction.