By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

returning a single value based on 2 or more records

P: 16
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
Share this Question
Share on Google+
2 Replies


debasisdas
Expert 5K+
P: 8,127
can you kindly post the query that you are working on.
Nov 21 '08 #2

P: 16
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.