Home » SQL & PL/SQL » SQL & PL/SQL » Distinct Count Help
Distinct Count Help [message #293504] Sun, 13 January 2008 13:45 Go to next message
Messages: 13
Registered: January 2008
Junior Member
Hi Everyone,

Below is my command, I want to know how to have the distinct count of per_end_date for each pay_sum_grp(i.e; for FLP,PTS &PTU) for each employee
please see the last two lines of my command to have an idea,please help me out.

 eelist AS
  SELECT assoc.company, assoc.process_level,assoc.deptname,
assoc.employee,assoc.first_name as empfirst_name,
assoc.last_name as emplast_name,assoc.middle_init as empmiddle_int,
  FROM lrsuser.v_associatelist assoc
 AND emp_status IN ('CF','E9','EF','N9','NF','CA','EA','NA','CB','EB','NB')
 eelistwithpto AS(
   SELECT eelist.*, 
NVL2(ptoelig.elig_balance,ptoelig.elig_balance,0) As EligibleBal
  from eelist 
left outer join 
(select assoc.company,assoc.employee,
 SUM(emtamastr.elig_balance) elig_balance
        FROM eelist assoc inner join
        lawson.emtamastr emtamastr
        ON assoc.company = emtamastr.company
        AND assoc.employee = emtamastr.employee
        inner join lawson.planmaster planmaster
        ON emtamastr.company = planmaster.company
        AND emtamastr.plan_name = planmaster.plan_name
        WHERE  planmaster.end_eff_date = TO_DATE ('1700/01/01', 'YYYY/MM/DD')
        AND planmaster.plan_class in ('PTO', 'VAC') 
group by assoc.company, assoc.employee
			 ) ptoelig
 on eelist.company = ptoelig.company
 and eelist.employee = ptoelig.employee
select eelistwithpto.*, prt.hours,prt.per_end_date, prt.pay_sum_grp
FROM eelistwithpto INNER JOIN lawson.prtime prt
ON eelistwithpto.company=prt.company
AND eelistwithpto.employee=prt.employee 
WHERE prt.pay_sum_grp IN ('FLP','PTS','PTU')
AND TO_CHAR(prt.per_end_date,'YYYY')='2007'


[Updated on: Sun, 13 January 2008 14:43] by Moderator

Report message to a moderator

Re: Distinct Count Help [message #293527 is a reply to message #293504] Sun, 13 January 2008 23:10 Go to previous messageGo to next message
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


SELECT pay_sum_grp , <employee>,
  COUNT(distinct per_end_date )
  FROM  .... 
  WHERE ....
  GROUP BY  pay_sum_grp , <employee>;

Thumbs Up

[Updated on: Sun, 13 January 2008 23:12]

Report message to a moderator

Re: Distinct Count Help [message #293662 is a reply to message #293504] Mon, 14 January 2008 08:17 Go to previous message
Messages: 4759
Registered: February 2005
Location: East Coast USA
Senior Member
kam123 wrote on Sun, 13 January 2008 14:45

WHERE planmaster.end_eff_date = TO_DATE ('1700/01/01', 'YYYY/MM/DD')

Previous Topic: Send attachment with email using pl/sql
Next Topic: how can I get the right partition name
Goto Forum:

Current Time: Wed Jul 26 18:04:33 CDT 2017

Total time taken to generate the page: 0.11782 seconds