Paycheck Query

Paycheck Query - HELP

[ Follow Ups ] [ Post Followup ] [ Message Board ]

Posted by [IP Address: 216.185.65.41] 'Helen Coutinho' on August 23, 2001 at 14:54:41 EST:

I need help

I'm trying to create a query that adds all earnings and verifies it against the Total Gross.
tables that need to be joined are
PS_PAY_CHECK
PS_PAY_EARNINGS
PS_OTH_EARNS

I would like to stay away from creating a view...

I had to use an expression with an outer join (MSSQL7) to get all rows of data (else data is dropped) Addl# etc...
When I do a sum on the earnings it sometimes (based on how many rows are retreived, it adds earnings more then once. If I use distinct it will drop earnings that have the same value. )I only want one row returned per employee. ANY SUGGESTIONS????

Here's my current Query in SQL
***********************************************
SELECT B.EMPLID, B.EMPL_RCD#, A.NAME,
SUM(DISTINCT(CASE WHEN C.OTH_EARNS IS NULL THEN '0' ELSE C.OTH_EARNS END))
+ (SUM(DISTINCT(CASE WHEN B.REG_HRLY_EARNS IS NULL THEN '0' ELSE B.REG_HRLY_EARNS END))
+ SUM(DISTINCT(CASE WHEN B.OT_HRLY_EARNS IS NULL THEN '0' ELSE B.OT_HRLY_EARNS END)),
A.TOTAL_GROSS, D.CHECK_DT, A.OFF_CYCLE, A.CHECK#
FROM PS_PAY_CHECK A, PS_PAY_EARNINGS B, PS_PAY_OTH_EARNS C, PS_PAY_CALENDAR D
WHERE A.COMPANY = B.COMPANY
AND A.PAYGROUP = B.PAYGROUP
AND A.PAY_END_DT = B.PAY_END_DT
AND A.OFF_CYCLE = B.OFF_CYCLE
AND A.PAGE# = B.PAGE#
AND A.LINE# = B.LINE#
AND A.SEPCHK = B.SEPCHK
AND A.COMPANY *= C.COMPANY
AND A.PAYGROUP *= C.PAYGROUP
AND A.PAY_END_DT *= C.PAY_END_DT
AND A.OFF_CYCLE *= C.OFF_CYCLE
AND A.PAGE# *= C.PAGE#
AND A.LINE# *= C.LINE#
AND A.SEPCHK *= C.SEPCHK
AND B.ADDL# *= C.ADDL#
AND A.COMPANY = D.COMPANY
AND A.PAYGROUP = D.PAYGROUP
AND A.PAY_END_DT = D.PAY_END_DT
AND D.CHECK_DT = '2001-08-24'
--AND A.EMPLID = '000614'
--AND CHECK# = '380781'
GROUP BY B.EMPLID, B.EMPL_RCD#, A.NAME, A.TOTAL_GROSS, D.CHECK_DT, A.OFF_CYCLE, A.CHECK#
HAVING SUM(CASE WHEN C.OTH_EARNS IS NULL THEN '0' ELSE C.OTH_EARNS END)+ SUM (CASE WHEN B.REG_HRLY_EARNS IS NULL THEN '0' ELSE B.REG_HRLY_EARNS END)
+ SUM(CASE WHEN B.OT_HRLY_EARNS IS NULL THEN '0' ELSE B.OT_HRLY_EARNS END) <> 0

Follow Ups:

Post a Followup

Name:
E-Mail:
Subject:
Message: : I need help : I'm trying to create a query that adds all earnings and verifies it against the Total Gross. : tables that need to be joined are : PS_PAY_CHECK : PS_PAY_EARNINGS : PS_OTH_EARNS : I would like to stay away from creating a view... : I had to use an expression with an outer join (MSSQL7) to get all rows of data (else data is dropped) Addl# etc... : When I do a sum on the earnings it sometimes (based on how many rows are retreived, it adds earnings more then once. If I use distinct it will drop earnings that have the same value. )I only want one row returned per employee. ANY SUGGESTIONS???? : Here's my current Query in SQL : *********************************************** : SELECT B.EMPLID, B.EMPL_RCD#, A.NAME, : SUM(DISTINCT(CASE WHEN C.OTH_EARNS IS NULL THEN '0' ELSE C.OTH_EARNS END)) : + (SUM(DISTINCT(CASE WHEN B.REG_HRLY_EARNS IS NULL THEN '0' ELSE B.REG_HRLY_EARNS END)) : + SUM(DISTINCT(CASE WHEN B.OT_HRLY_EARNS IS NULL THEN '0' ELSE B.OT_HRLY_EARNS END)), : A.TOTAL_GROSS, D.CHECK_DT, A.OFF_CYCLE, A.CHECK# : FROM PS_PAY_CHECK A, PS_PAY_EARNINGS B, PS_PAY_OTH_EARNS C, PS_PAY_CALENDAR D : WHERE A.COMPANY = B.COMPANY : AND A.PAYGROUP = B.PAYGROUP : AND A.PAY_END_DT = B.PAY_END_DT : AND A.OFF_CYCLE = B.OFF_CYCLE : AND A.PAGE# = B.PAGE# : AND A.LINE# = B.LINE# : AND A.SEPCHK = B.SEPCHK : AND A.COMPANY = C.COMPANY : AND A.PAYGROUP = C.PAYGROUP : AND A.PAY_END_DT = C.PAY_END_DT : AND A.OFF_CYCLE = C.OFF_CYCLE : AND A.PAGE# = C.PAGE# : AND A.LINE# = C.LINE# : AND A.SEPCHK = C.SEPCHK : AND B.ADDL# = C.ADDL# : AND A.COMPANY = D.COMPANY : AND A.PAYGROUP = D.PAYGROUP : AND A.PAY_END_DT = D.PAY_END_DT : AND D.CHECK_DT = '2001-08-24' : --AND A.EMPLID = '000614' : --AND CHECK# = '380781' : GROUP BY B.EMPLID, B.EMPL_RCD#, A.NAME, A.TOTAL_GROSS, D.CHECK_DT, A.OFF_CYCLE, A.CHECK# : HAVING SUM(CASE WHEN C.OTH_EARNS IS NULL THEN '0' ELSE C.OTH_EARNS END)+ SUM (CASE WHEN B.REG_HRLY_EARNS IS NULL THEN '0' ELSE B.REG_HRLY_EARNS END) : + SUM(CASE WHEN B.OT_HRLY_EARNS IS NULL THEN '0' ELSE B.OT_HRLY_EARNS END) <> 0

[ Follow Ups ] [ Post Followup ] [ Message Board ]