Focal Point
How to get highest date in two different SCDs

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7737014336

September 12, 2013, 11:14 AM
JALDbaDev
How to get highest date in two different SCDs
I have a synonym made up of 5 tables: a fact table and 4 dimension tables - 2 of those being a slowly changing dimension. Trying to come up with a query that will get data for the most recent date in both scd's. I can do one, but two is giving me fits.
PolicyHolder Agency
Pol_Cd Agecny_Num
Rec_Eff_Dt Rec_Eff_Dt

When I do this:

Table File Synonym1
SUM
max.Policyholder.Rec_Eff_Dt
max Agency.Rec_Eff_Dt
By lowest Pol_CD
END

It displays the wrong Policyholder.Rec_Eff_Dt but the correct Agency.Rec_Eff_Dt.

Any ideas on how to get both? Thanks!
September 12, 2013, 11:18 AM
JALDbaDev
Sorry this got jumbled up:
SCD1: PolicyHolder Fields are: Pol_Cd Rec_Eff_Dt
SCD2: Agency Fields are: Agency_Num Rec_Eff_Dt
September 12, 2013, 11:25 AM
Prarie
What format are your dates in?


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
September 12, 2013, 12:00 PM
JALDbaDev
HYYMDs
September 13, 2013, 10:04 AM
JALDbaDev
We may try to resolve this through the ETL process instead and just provide a dimension that has the most recent row rather than a history.