Query and Report Puzzle

Hi Brian,
You have two easy options.
1) Within Crystal, you would create this as a 'crosstab' report, which "pivots" on the deduction code to put the values into columns/buckets.
2) You can also use 'CASE' statements within your SQL to create a "poor man's" crosstab:
... case when ded_code = 'HEAL' then dist_amt else 0 end as health_amt ...
You would then summarize by employee and suppress the details

BTW, you might find that it's easier to create this report from PAYDEDUCTN instead of PRDISTRIB.

In these cases, I find that loading a temp table is the easiest. Do your PRDISTRIB pull into a temp table. Join that table to DEPTARTMENT, update the dept name on your temp table, then pull a crosstab query from the temp table. This should also increase performance because you won't be doing a crosstab off of Lawson tables.

The relationship between EMPLOYEE and DEPTCODE is on company/process_level/department.

I'm manually coding this due to using ODBC. So I've got to refresh my memory for writing cross-tab queries. Basically what I want to do is search for Employees that meet a certain criteria and list their deductions in the following format, so I'm looking at some rather complex SQL, maybe. The first line is a header and each person listed is a detail "row" resulting from a join, to be determined. Each deduction amount and its deduction code are individual records (coming from the ONETMDED table), that is each person has anywhere from 0-6 records forming their row in the detail.


DED CODE DED AMT DED CODE DED AMT DED CODE DED AMT

Mary Brown HFGD 15.25 HFTS 44.10 JFFC 53.10
401K 80.11 SSHG 16.16 KLL1 90.00

Steve Cash HFGD 19.20 HFTS 18.44 JFFC 55.54
401K 28.74 SSHG 59.15 KLL1 75.11

Pat Lomax HFGD 22.64 HFTS 50.50 JFFC 45.66
401K 30.69 KLL1 75.41

There are a few different ways to tackle this.

One is to use nested subqueries, where each column is a separate SELECT query, JOINed to a larger SELECT query...like this (in this example, I'm using PAYDEDUCTN, you'd be using ONETMDED and could leave out the PAYMASTR fields...):
SELECT
PAY.COMPANY AS COMPANY,
PAY.EMPLOYEE AS EMPLOYEE,
PAY.PROCESS_LEVEL AS PROCESS_LEVEL,
PAY.DEPARTMENT AS DEPARTMENT,
PROD.EMPLOYEE.EMP_STATUS AS EMP_STATUS,
PROD.EMPLOYEE.JOB_CODE AS JOB_CODE,
PROD.EMPLOYEE.SALARY_CLASS AS SALARY_CLASS,
CHAR(PAY.CHECK_DATE,USA) AS CHECK_DATE,
CHAR(PAY.PER_END_DATE,USA) AS PER_END_DATE,
PAY.GROSS_PAY AS GROSS_PAY,
HOURS.HOURS_WORKED,
BONUS_PAY.BONUS_PAY,
OTHER_PAY.OTHER_PAY,
TCFU_DED.DED_AMT AS ER_FUTA_AMT,
TCMC_DED.DED_AMT AS ER_MEDICARE_AMT,
TCSS_DED.DED_AMT AS ER_SOCSEC_AMT,
SUI.DED_AMT AS ER_SUI_AMT
FROM
PROD.PAYMASTR PAY
LEFT OUTER JOIN PROD.EMPLOYEE
ON ( PAY.COMPANY = PROD.EMPLOYEE.COMPANY
AND PAY.EMPLOYEE = PROD.EMPLOYEE.EMPLOYEE )
LEFT OUTER JOIN
( SELECT COMPANY,EMPLOYEE,CHECK_ID, SUM(WAGE_AMOUNT) AS BONUS_PAY FROM PROD.PRTIME
WHERE PAY_SUM_GRP IN ('ABN','CBN','OBN','QBN','RBN','SBN','SBO','ZBN')
GROUP BY COMPANY,EMPLOYEE,CHECK_ID) BONUS_PAY
ON ( PAY.COMPANY = BONUS_PAY.COMPANY
AND PAY.EMPLOYEE = BONUS_PAY.EMPLOYEE
AND PAY.CHECK_ID = BONUS_PAY.CHECK_ID )
LEFT OUTER JOIN
( SELECT COMPANY,EMPLOYEE,CHECK_ID, SUM(WAGE_AMOUNT) AS OTHER_PAY FROM PROD.PRTIME
WHERE PAY_SUM_GRP IN ('AUT','OEA','GIF','NSO','QME','REL','SEV','TAX','ZNS','ZRL','ZTF')
GROUP BY COMPANY,EMPLOYEE,CHECK_ID) OTHER_PAY
ON ( PAY.COMPANY = OTHER_PAY.COMPANY
AND PAY.EMPLOYEE = OTHER_PAY.EMPLOYEE
AND PAY.CHECK_ID = OTHER_PAY.CHECK_ID )
LEFT OUTER JOIN
( SELECT COMPANY,EMPLOYEE,CHECK_ID, SUM(R_HOURS) AS HOURS_WORKED FROM PROD.PRTIME
GROUP BY COMPANY,EMPLOYEE,CHECK_ID) HOURS
ON ( PAY.COMPANY = HOURS.COMPANY
AND PAY.EMPLOYEE = HOURS.EMPLOYEE
AND PAY.CHECK_ID = HOURS.CHECK_ID )
LEFT OUTER JOIN
( SELECT COMPANY,EMPLOYEE,CHECK_ID, SUM(DED_AMT) AS DED_AMT FROM PROD.PAYDEDUCTN
WHERE DED_CODE = 'TCMC'
GROUP BY COMPANY,EMPLOYEE,CHECK_ID,DED_CODE) TCMC_DED
ON ( PAY.COMPANY = TCMC_DED.COMPANY
AND PAY.EMPLOYEE = TCMC_DED.EMPLOYEE
AND PAY.CHECK_ID = TCMC_DED.CHECK_ID)
LEFT OUTER JOIN
( SELECT COMPANY,EMPLOYEE,CHECK_ID,SUM(DED_AMT) AS DED_AMT FROM PROD.PAYDEDUCTN
WHERE DED_CODE = 'TCSS'
GROUP BY COMPANY,EMPLOYEE,CHECK_ID,DED_CODE) TCSS_DED
ON ( PAY.COMPANY = TCSS_DED.COMPANY
AND PAY.EMPLOYEE = TCSS_DED.EMPLOYEE
AND PAY.CHECK_ID = TCSS_DED.CHECK_ID )
LEFT OUTER JOIN
( SELECT COMPANY,EMPLOYEE,CHECK_ID,SUM(DED_AMT) AS DED_AMT FROM PROD.PAYDEDUCTN
WHERE DED_CODE = 'TCFU'
GROUP BY COMPANY,EMPLOYEE,CHECK_ID) TCFU_DED
ON ( PAY.COMPANY = TCFU_DED.COMPANY
AND PAY.EMPLOYEE = TCFU_DED.EMPLOYEE
AND PAY.CHECK_ID = TCFU_DED.CHECK_ID )
LEFT OUTER JOIN
( SELECT SUI.COMPANY,SUI.EMPLOYEE,SUI.CHECK_ID,SUM(DED_AMT) AS DED_AMT FROM PROD.PAYDEDUCTN SUI
LEFT OUTER JOIN PROD.DEDCODE DED
ON ( SUI.COMPANY = DED.COMPANY
AND SUI.DED_CODE = DED.DED_CODE )
WHERE DED.CALC_TYPE = 'T'
AND DED.ADJUST_PAY = 'C'
AND DED.TAX_CATEGORY = 10
GROUP BY SUI.COMPANY,SUI.EMPLOYEE,SUI.CHECK_ID) SUI
ON ( PAY.COMPANY = SUI.COMPANY
AND PAY.EMPLOYEE = SUI.EMPLOYEE
AND PAY.CHECK_ID = SUI.CHECK_ID )
WHERE
(
( PAY.STATUS = 9 )
)
ORDER BY
COMPANY,
EMPLOYEE,
CHECK_DATE

Another way to do it is to use CASE statements to populate the columns (this is probably more efficient) but harder to read...

SELECT EMPLOYEE,
SUM(CASE WHEN DED_CODE = 'DED1' THEN DED_AMT ELSE 0 END) AS DED1_AMT,
SUM(CASE WHEN DED_CODE = 'DED2' THEN DED_AMT ELSE 0 END) AS DED2_AMT
FROM lawson.ONETMDED
GROUP BY EMPLOYEE
HAVING
SUM(CASE WHEN DED_CODE = 'DED1' THEN DED_AMT ELSE 0 END) <> 0
OR
SUM(CASE WHEN DED_CODE = 'DED2' THEN DED_AMT ELSE 0 END) <> 0

I chose to keep it simple for now and just do "case when DED_CODE ='FSAH' then DED_AMT AS FSAH" to create a new field that I can place anywhere on the report, but I either get "FROM not in expected place" or "Missing Operator" errors.

SELECT
EMPLOYEE.EMPLOYEE,
EMPLOYEE.FICA_NBR,
EMPLOYEE.LAST_NAME,
EMPLOYEE.ADDR1,
EMPLOYEE.CITY,
EMPLOYEE.ADDR2,
EMPLOYEE.DEPARTMENT,
EMPLOYEE.TERM_DATE,
substr(DEPARTMENT,4,2),
EMPLOYEE.ZIP,
trim(employee.city) || ', ' || trim(employee.state) || ' ' || trim(employee.zip) as city_st_zip,
EMPLOYEE.STATE,
EMPLOYEE.PROCESS_LEVEL,
EMPLOYEE.FIRST_NAME,
trim(last_name) || ', ' || trim(first_name) || ' ' ||
case when trim(middle_init) is null
then trim(middle_init)
else trim(middle_init) || '.' end,
ONETMDED.EMPLOYEE,
ONETMDED.DED_CODE,
ONETMDED.DED_AMT,
case when DED_CODE ='FSAH' then DED_AMT AS FSAH
FROM EMPLOYEE EMPLOYEE INNER
JOIN ONETMDED ONETMDED ON EMPLOYEE.EMPLOYEE = ONETMDED.EMPLOYEE
WHERE ONETMDED.RECORD_TYPE ='A'

You need to complete the CASE statement (you need the ELSE and END) for FSAH:
case when DED_CODE ='FSAH' then DED_AMT ELSE 0 END AS FSAH

Your case statement is incomplete. Look again at John's sample code (missing "else" and "end").