How to pull all records if distinct count > 1

[rreiling007]

I have a table with the following values.

EMPLLOYEE_ID
JOB
PAY_DATE
LOCATION

Most employees have 10 to 15 rows. I only want to pull ALL rows for employees ONLY if there is a distinct count from DATE_PAID greater than 1.

There are cases where an employee might have two pay checks generarted for the same PAY_DATE.

Can't seem to figure this out.

Any help is appreciated.

Thanks.

 

[CJ_London]

use a subquery or separate query to select distinct, then use the duplicates wizard to find the duplicates on the resultant query.

If you require further help, please advise whre the Date_PAID field is - or are you giving PAY_DATE a different name?

 

[rreiling007]

Hi CJ,

Below is the query. Focus is on column PAY_PAYROLL_ACTIONS.DATE_PAID.

Could you possibly provide a clearer example?

Thank you!

SELECT PAY_PAYROLL_ACTIONS.FULL_NAME, PAY_PAYROLL_ACTIONS.ASSIGNMENT_NUMBER, PAY_PAYROLL_ACTIONS.PAYROLL_NAME, PAY_PAYROLL_ACTIONS.PERIOD_NAME, PAY_PAYROLL_ACTIONS.DATE_PAID, PAY_PAYROLL_ACTIONS.RUN_TYPE_ID, PAY_PAYROLL_ACTIONS.ACTION_TYPE, PAY_PAYROLL_ACTIONS.TYPE, PAY_PAYROLL_ACTIONS.ACTION_SEQUENCE, PAY_PAYROLL_ACTIONS.GROSS_EARNINGS, PAY_PAYROLL_ACTIONS.INCENTIVEGROSSUP, PAY_PAYROLL_ACTIONS.ADMIN_BONUS, PAY_PAYROLL_ACTIONS.COBRAGROSSUP, PAY_PAYROLL_ACTIONS.GIFTCHECKGROSSUP, PAY_PAYROLL_ACTIONS.DEF_COMP_NO_FICA_MONTHLY, PAY_PAYROLL_ACTIONS.DEFERRED_COMP_NO_FICA_MONTHLY, PAY_PAYROLL_ACTIONS.CRITICAL_IIINESS_CHILD, PAY_PAYROLL_ACTIONS.HEALTHCARE_FLEX, PAY_PAYROLL_ACTIONS.DEPENDANT_FLEX, PAY_PAYROLL_ACTIONS.PRETAX_401K_CONTR, PAY_PAYROLL_ACTIONS.PRETAX_401K_CATCHUP, PAY_PAYROLL_ACTIONS.PRETAX_401K_CONTR_CATCHUP2, PAY_PAYROLL_ACTIONS.PRETAX_401K_CONTR_CATCHUP, PAY_PAYROLL_ACTIONS.ROTH_401K_CATCHUP, PAY_PAYROLL_ACTIONS.ROTH_401K_CONTR, PAY_PAYROLL_ACTIONS.HEALTH_SAVINGS_ACCT
FROM PAY_PAYROLL_ACTIONS INNER JOIN q_DATE_PAID_GRE_1 ON PAY_PAYROLL_ACTIONS.ASSIGNMENT_NUMBER = q_DATE_PAID_GRE_1.ASSIGNMENT_NUMBER
GROUP BY PAY_PAYROLL_ACTIONS.FULL_NAME, PAY_PAYROLL_ACTIONS.ASSIGNMENT_NUMBER, PAY_PAYROLL_ACTIONS.PAYROLL_NAME, PAY_PAYROLL_ACTIONS.PERIOD_NAME, PAY_PAYROLL_ACTIONS.DATE_PAID, PAY_PAYROLL_ACTIONS.RUN_TYPE_ID, PAY_PAYROLL_ACTIONS.ACTION_TYPE, PAY_PAYROLL_ACTIONS.TYPE, PAY_PAYROLL_ACTIONS.ACTION_SEQUENCE, PAY_PAYROLL_ACTIONS.GROSS_EARNINGS, PAY_PAYROLL_ACTIONS.INCENTIVEGROSSUP, PAY_PAYROLL_ACTIONS.ADMIN_BONUS, PAY_PAYROLL_ACTIONS.COBRAGROSSUP, PAY_PAYROLL_ACTIONS.GIFTCHECKGROSSUP, PAY_PAYROLL_ACTIONS.DEF_COMP_NO_FICA_MONTHLY, PAY_PAYROLL_ACTIONS.DEFERRED_COMP_NO_FICA_MONTHLY, PAY_PAYROLL_ACTIONS.CRITICAL_IIINESS_CHILD, PAY_PAYROLL_ACTIONS.HEALTHCARE_FLEX, PAY_PAYROLL_ACTIONS.DEPENDANT_FLEX, PAY_PAYROLL_ACTIONS.PRETAX_401K_CONTR, PAY_PAYROLL_ACTIONS.PRETAX_401K_CATCHUP, PAY_PAYROLL_ACTIONS.PRETAX_401K_CONTR_CATCHUP2, PAY_PAYROLL_ACTIONS.PRETAX_401K_CONTR_CATCHUP, PAY_PAYROLL_ACTIONS.ROTH_401K_CATCHUP, PAY_PAYROLL_ACTIONS.ROTH_401K_CONTR, PAY_PAYROLL_ACTIONS.HEALTH_SAVINGS_ACCT
HAVING (((PAY_PAYROLL_ACTIONS.ASSIGNMENT_NUMBER)="100014625"));

 

[CJ_London]

not really, you said you had a table with a few fields, but this is a query with many fields

The best I can suggest is if your query is called 'myquery' you would create qa query, lets call it myQuery1

SELECT DISTINCT EMPLLOYEE_ID, JOB, PAY_DATE, LOCATION, DATE_DATE FROM myQuery

And then you can use the query wizard to create your duplicates query based on myQuery1.

Incidnetaly, I'm not sure why your big query is using group by - you are not summing or counting so you could just use select distinct instead