A noob to SQL and need help writing a query

1

Hello,

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
, A.BW_ID
, A.EMPLOYEE_ID
, A.FIRST_NAME
, A.LAST_NAME
, 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!

2

First off, please rewrite your query to use explicit joins. e.g. isntead of:

select foo from a, b where a.bar = b.fum

write

select foo from a
inner join b on a.bar = b.fum

Second, you're probably right that one of the joins is limiting your result set. To track it down, do one join, then two, then three, etc. to see when the result set gets smaller. Then, you have the join causing the reduction.

3

To add to what @gbritton suggested, I would also suggest going through the where criteria if the join does not cause the problem.

4

This needs a rewrite for getting the RSD values. Also, in the query as posted, you weren't referencing RPA and C, so I removed those, but if you need to verify that those table have a matching row(s), we can add an WHERE EXISTS check to the main query.

SELECT  A.SSN
, A.BW_ID
, A.EMPLOYEE_ID
, A.FIRST_NAME
, A.LAST_NAME
, RSD_VALUES.[SICK LEAVE HOURS]
, RSD_VALUES.[SICK LEAVE CREDIT]
, RSD_VALUES.[RETIREMENT DATE]
, RSD_VALUES.[TOTAL BENEFIT AMOUNT]

FROM	 EMPLOYEE		A
LEFT OUTER JOIN (
    SELECT RSD.BW_ID,
        SUM(CASE WHEN RSD.RESULT_NAME = 'sickLeaveHours' THEN RSD.NUMBER_VALUE ELSE 0 END) AS 'SICK LEAVE HOURS',
        SUM(CASE WHEN RSD.RESULT_NAME = 'ADDTSICKLEAVECONVERT' THEN RSD.NUMBER_VALUE ELSE 0 END) AS 'SICK LEAVE CREDIT',
        SUM(CASE WHEN RSD.RESULT_NAME = 'COMMENCEMENTDATE' THEN RSD.NUMBER_VALUE ELSE 0 END) AS 'RETIREMENT DATE',
        SUM(CASE WHEN RSD.RESULT_NAME IN ('ADJUSTEDACCRUEDBENEFIT', 'adjustedTotalBenefit')
                 THEN RSD.NUMBER_VALUE ELSE 0 END) AS 'TOTAL BENEFIT AMOUNT'
    FROM RESULT_SET_DETAIL RSD
    WHERE
        RSD.PROCESS_REFERENCE_NUMBER = (
            SELECT RSD2.PROCESS_REFERENCE_NUMBER
            FROM RESULT_SET_DETAIL RSD2
            WHERE
                RSD2.BW_ID = RSD.BW_ID AND
                RSD2.RESULT_NAME= 'TYPEOFCALC' AND 
                RSD2.STRING_VALUE = 'FI'
            )
    GROUP BY RSD.BW_ID
) AS RSD_VALUES ON
    RSD_VALUES.BW_ID = A.BW_ID

ORDER BY A.LAST_NAME
5

Hi,

I modified the query a little as the above kept giving me an error:

select DISTINCT TOP (10)
A.FIRST_NAME
,(CASE when rsd.RESULT_NAME = 'sickLeaveHours' and rsd.STAR_ID = 'dbCalcProcessor' then rsd.NUMBER_VALUE end) as 'Sick Leave',
(CASE when rsd.RESULT_NAME = 'addtSickLeaveConvert' and rsd.STAR_ID = 'dbCalcProcessor' then rsd.NUMBER_VALUE end) as 'Sick Leave Credit',
(CASE when rsd.RESULT_NAME = 'commencementDate' and rsd.STAR_ID = 'dbCalcProcessor' then rsd.DATE_VALUE end) as 'Commencement Date',
(CASE when rsd.RESULT_NAME = 'adjustedAccruedBenefit' and rsd.STAR_ID = 'dbCalcProcessor' then rsd.NUMBER_VALUE end) as 'Amount'
from RESULT_SET_DETAIL rsd
inner join EMPLOYEE a on a.BW_ID = rsd.BW_ID

where rsd.BW_ID in (select rsd2.BW_ID from RESULT_SET_DETAIL rsd2
where rsd2.STAR_ID = 'dbCalcProcessor'
and rsd2.RESULT_NAME = 'typeOfCalc'
and rsd2.STRING_VALUE = 'FI'
and rsd2.PROCESS_REFERENCE_NUMBER = rsd.PROCESS_REFERENCE_NUMBER)

But it seems to be throwing everything in a separate row

FIRST_NAME Sick Leave Sick Leave Credit Commencement Date Amount
Carol NULL NULL NULL NULL
Carol NULL NULL 2014-07-01 00:00:00.000 NULL
Carol 1234.56700000 NULL NULL NULL
Carol NULL NULL NULL $$$.xx
Carol NULL 0.50000000 NULL NULL

In the example above I was expecting it to appear as without the ... Its the only way I could get them to line up in the columns:
FIRST_NAM.......Sick Leave .....Sick Leave Credit ....Commencement Date........... Amount
Carol ..................1234.5678........ 0.50 .........................2014-07-01 ......................... $$$.xx

How would I get it all to appear on one line and show 0 for null on sick leave, sick leave credit and amount?

6

That's exactly what my query did -- added a subquery so that all the values could be easily shown in one line. You're going to have complex GROUPing and other issues if you try to do this without using a subquery.

7

This is what the query finally ended up as:

Select DISTINCT e.SSN

              , e.EMPLOYEE_ID		AS 'Employee ID'
              , e.FIRST_NAME		AS 'First Name'
              , e.LAST_NAME			AS 'Last Name'
              ,(CASE when rpa.AMOUNT is null then d.CHECK_AMOUNT
					else rpa.AMOUNT end) as 'Check Amount'
			  ,(CASE when rpa.AMOUNT is null then d.check_date
					else rpa.first_date end) as 'Check Date'
			  ,(case	when rp.installment_type = 'Q' then 'QDRO Life Annuity'
						when rp.installment_type = 'S' then 'Survivor Annuity'
						when rp.installment_type = 'M' then 'Maximum'
						when rp.installment_type = 'A' then 'Option A'
						when rp.installment_type = 'B' then 'Option B'
						when rp.installment_type = 'C' then 'Option C'
						when rp.installment_type = 'D' then 'Option D'
						when rp.installment_type = 'NO'then 'None'
						when d.check_type = 'L' then 'Lump Sum'
						when d.check_type = 'I' then 'Roth IRA Rollover'
						when d.check_type = 'R' then 'Qualified Plan Rollover'
						when d.check_type = 'T' then 'IRA Rollover'
						else 'No type on file' end) as 'Payment Type',

                         max(a.sick_leave) AS 'Sick Leave',
                         max(a.Sick_Leave_Credit) AS 'Sick Leave Credit',
                         max(a.Commencement_Date) AS 'Commencement Date'

from employee e
inner join (select rsd.bw_id,
(CASE when rsd.RESULT_NAME = 'sickLeaveHours' and rsd.STAR_ID = 'dbCalcProcessor' then rsd.NUMBER_VALUE end) as Sick_Leave,
(CASE when rsd.RESULT_NAME = 'addtSickLeaveConvert' and rsd.STAR_ID = 'dbCalcProcessor' then rsd.NUMBER_VALUE end) as Sick_Leave_Credit,
(CASE when rsd.RESULT_NAME = 'commencementDate' and rsd.STAR_ID = 'dbCalcProcessor' then rsd.DATE_VALUE end) as Commencement_Date

from RESULT_SET_DETAIL rsd
where rsd.BW_ID in (select rsd2.BW_ID from RESULT_SET_DETAIL rsd2
where rsd2.STAR_ID = 'dbCalcProcessor'
and rsd2.RESULT_NAME = 'typeOfCalc'
and rsd2.STRING_VALUE = 'FI'
and rsd2.PROCESS_REFERENCE_NUMBER = rsd.PROCESS_REFERENCE_NUMBER)
and rsd.RESULT_NAME in ('sickLeaveHours','addtSickLeaveConvert','commencementDate','adjustedAccruedBenefit')) a
on a.BW_ID = e.BW_ID
left join CHECKS d on e.BW_ID = d.BW_ID
left JOIN RECUR_PAYMENT_AMOUNTS RPA ON E.BW_ID = RPA.BW_ID

and rpa.FIRST_DATE = (select MIN(first_date) from RECUR_PAYMENT_AMOUNTS rpa2
where rpa2.BW_ID = rpa.BW_ID)
left join RECUR_PAYMENT rp on rp.BW_ID = a.BW_ID
and rp.BW_ID = rpa.bw_id

group by e.SSN,e.BW_ID, e.EMPLOYEE_ID, e.FIRST_NAME, e.last_name, D.CHECK_DATE, D.CHECK_AMOUNT, RPA.FIRST_DATE, RPA.AMOUNT, rp.INSTALLMENT_TYPE, d.CHECK_TYPE

ORDER BY E.LAST_NAME,E.FIRST_NAME