I need help desperately writing a query. I'm kind of new to SQL and was given the task of writing a query way beyond my skill set.
I run this query
SELECT * FROM RESULT_SET_DETAIL
where STRING_VALUE = 'FI'
It returns a population of 1361. This is what I'm expecting to receive, but when I run the query I created to get additional info I'm only getting about half the population. I also need to return a value from CHECKS (field name: check_amount) database if the payment was a lump sum payment
Or from the recur_payment_amounts (field name: Amount) and if the amount does come from the recur_payment_amounts I also need to pull the field type_payment_code from the recur_payment table.
This is what I have written so far. It currently is only trying to match the original population amount.:
SELECT DISTINCT A.SSN
, SLH.NUMBER_VALUE AS 'SICK LEAVE HOURS'
, SLC.NUMBER_VALUE AS 'SICK LEAVE CREDIT'
, COMDT.DATE_VALUE AS 'RETIREMENT DATE'
, TOTBEN.NUMBER_VALUE AS 'TOTAL BENEFIT AMOUNT'
FROM EMPLOYEE A ,RESULT_SET_DETAIL RSD ,RESULT_SET_DETAIL SLH ,RESULT_SET_DETAIL SLC ,RESULT_SET_DETAIL COMDT ,RESULT_SET_DETAIL TOTBEN ,RECUR_PAYMENT RP ,RECUR_PAYMENT_AMOUNTS RPA ,CHECKS C WHERE RSD.RESULT_NAME= 'TYPEOFCALC' AND RSD.STRING_VALUE = 'FI' AND RSD.BW_ID = A.BW_ID AND RSD.BW_ID = SLH.BW_ID AND RSD.PROCESS_REFERENCE_NUMBER = SLH.PROCESS_REFERENCE_NUMBER AND SLH.RESULT_NAME= 'sickLeaveHours' AND SLC.BW_ID = SLH.BW_ID AND SLC.PROCESS_REFERENCE_NUMBER = SLH.PROCESS_REFERENCE_NUMBER AND SLC.RESULT_NAME= 'ADDTSICKLEAVECONVERT' AND COMDT.RESULT_NAME = 'COMMENCEMENTDATE' AND COMDT.BW_ID = SLH.BW_ID AND COMDT.PROCESS_REFERENCE_NUMBER = SLH.PROCESS_REFERENCE_NUMBER AND (TOTBEN.RESULT_NAME = 'adjustedTotalBenefit' OR TOTBEN.RESULT_NAME = 'ADJUSTEDACCRUEDBENEFIT') AND TOTBEN.BW_ID = SLH.BW_ID AND TOTBEN.PROCESS_REFERENCE_NUMBER = SLH.PROCESS_REFERENCE_NUMBER ORDER BY A.LAST_NAME
Just running that query is only giving a result of 779 rows. It looks like it’s coming from the section where slc.result_name = ‘Addtsickleaveconvert’ and from the section that has the AND OR statement. I’m thinking there has to be a join somewhere, just not sure where to put it or what type of join to use. Also is there a better way to pull data from the same table and the same field? Any help will be appreciated.
Thanks for any help!