AL SUI Tax Code - AS OF

[vlfox]

Hi, all !

I'm on ADP Enterprise, Reportsmith 4.0 – SQL and trying to capture each person's SUI Tax Code AS OF 12/31/08. I have the report Effective Date AS OF 12/31/08, but everytime I add the SUI Tax Code field, I get every row a person's been in for the year.

I tried modifying a Derived Field that I'd received here at Tek-Tips; it was originally specific to Emp Count issues, so my attempt at modifying is prob'ly very weak, but here it is:

CASE WHEN
PS_AL_TAX_DATA.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_AL_TAX_DATA INNERALIAS
WHERE INNERALIAS.SUI_TAX_CD = PS_AL_TAX_DATA.SUI_TAX_CD
AND INNERALIAS.EFFDT <=
(rtrim("12/31/")
+
ltrim(<<CalYr>>)))
and
PS_JOB.[[PAYGRP]]=PS_AL_TAX_DATA.PAYGROUP
and
PS_JOB.FILE_NBR=PS_AL_TAX_DATA.FILE_NBR


THEN PS_AL_TAX_DATA.SUI_TAX_CD
ELSE "SCOOBY"
END

I appreciate any tips/suggestions about how to do this correctly.

ThanX for all the great help here; not only for my postings, but all that I read and learn from!

 

[CharlesCook]

This should be just basic Effective Dated tables.

Here is how I did it:

http://CharlesCook.com/Examples/ALSUITaxCode-ASOF.rpt

Also check out this power point:

http://CharlesCook.com/PPS/ADP/ReportSmith/Enterprise/Introduction-To-Effective-Dated-Tables.pps

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting