468,242 Members | 1,625 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

returning a single value based on 2 or more records

I have spent several days attempting to accomplish a certain task but am truly no closer than I was near the beginning.

i need to return a single value based on an employees payment option; whether they use Direct Deposit, physical check or both. Each payment type resides in a seperate record, but here's the kicker; if an employee only get's a solid check they may not have a record in that table.

D = DIRECT DEPOSIT

C = CHECK


EID RCD PAYTYPE
=== === =======
123 1 D
234 1 D
234 2 C
999 1 D
890 1 C
541 1 D
541 2 D

I Need It To Return



EID PAYTYPE
=== =======
123 D
234 B
999 D
890 C
541 D

Any help on the matter would be greatly appreciated.
Nov 21 '08 #1
2 1559
debasisdas
8,127 Expert 4TB
can you kindly post the query that you are working on.
Nov 21 '08 #2
I've only got the initial SELECT statement based which pulls from 1 table and a VIEW to return the most recent up-to-date information, MAX(EFFDT). i had toyed with a few different versions of trying to get what i need but have all since been failures which resulted in those lines beign deleted.

SELECT
DD.EMPLID,
MX.EMPL_RCD_NBR,
MX.EFFDT,
DD.PRIORITY,
CASE
WHEN DD.ACCOUNT_TYPE IN ('C','S') THEN 'D'
ELSE 'C'
END AS ACCT_TYPE
FROM PS_DIR_DEP_DISTRIB DD
JOIN MAX_DD_EFF_DT MX ON DD.EMPLID = MX.EMPLID
AND DD.EMPL_RCD_NBR = MX.EMPL_RCD_NBR
AND DD.EFFDT = MX.EFFDT

again, i don't know how much help this would be...
Nov 21 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by news | last post: by
7 posts views Thread by Bill Reed via AccessMonster.com | last post: by
41 posts views Thread by Materialised | last post: by
14 posts views Thread by Fabian Steiner | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.