Max Effect Date from table 2 w attribute but include everything from table 1

[John1Chr]

I have a scenerio where I'm trying to separate out depid's that have an ET attribute. In addition, I need to ensure that for that Depid it has a "A" active status for it's max effective date.

I am linking an expenditure table to an CFAttrib table by depid to find those depid's that have the ET attribute assigned. Problem is that most depids in the expenditure table don't have this ET attribute assigned but I want to include those depids so that I can separate out their costs in a report (Non ET costs vs ET Costs).


Expend table DepIds
E6031
B2034
E4413
G4634
G9K32

CFAttrib table DepId with ET Attribute-Status-MaxEffDt
B2034-A-1/2/2012
B2034-I-1/8/2012
G9K32-A-2/1/2012

I want to include G9K32 as ET Costs. (ie. it is "A" active ET Costs) I want to include B2034 as NON-ET Costs and if it is on the expend table I still want to include it's costs. I want to include E6031, E4413, G4634 as non-ET costs.

Currently, I am grouping by DepId in my report so I'm thinking maybe put a formula there. In addition, I have formulas that separate out the ET from the nonET costs by just grabbing if the DeptId has an ET Attribute assigned.

If {CFAttrib.Attrib} = "ET" then {Expend.Amnt} else 0 - ET Costs

If isnull({CFAttrib.Attrib}) then {Expend.Amnt} else 0 - Non ET Costs

I need to factor the status and Maxeffect date from the CFAttrib table to get the correct assignment of costs for the DepID. Would it be easiest to modify the formulas above?

 

[lbass]

You should have a left join FROM the ET table TO the CFAttrib table, with no selection criteria on the CFAttrib table. Then assuming you have created a SQL expression (let's call it {%maxdt} to determine the maxeffectivedt for each dep ID, you should be able to use formulas like this:

//{@ET}:
if {CFAttrib.Status} = "A" and
{CFAttrib.date} = {%maxdt} then
{Expend.Amnt}

//{@Non-ET}:
if isnull({CFAttrib.Attrib}) or
(
{CFAttrib.date} = {%maxdt} and
{CFAttrib.Status} <> "A"
) then
{Expend.Amnt}

-LB