Complex Query to obtain result from prior date based on another date?

[aellistechsupport]

Hello,
Trying to display where the dba_local_bu_rate_effectivedate is one prior to the dba_local_member_status_effectivedate per MemberId



There are multiple dba_local_bu_rate_effectivedate

MemberId    dba_local_member_status_effectivedate    dba_local_bu_rate_effectivedate    monthly_factor    factor
002361234    12/1/1997    6/1/1992    36.79    8.49
002361234    12/1/1997    1/1/1994    41.12    9.49
002361234    12/1/1997    6/1/1995    42.81    9.88
002361234    12/1/1997    3/1/1997    43.81    10.11
002361234    12/1/1997    8/1/1998    44.33    10.23
002361234    12/1/1997    8/1/2001    47.02    10.85
002361234    12/1/1997    6/1/2002    47.52    10.97
002361234    12/1/1997    1/1/2005    48.52    11.2
002361234    12/1/1997    1/1/2006    49.02    11.32
002361234    12/1/1997    12/1/2006    50.02    11.55
002361234    12/1/1997    1/1/2009    51.02    11.79

What I have so far but not working as hoped

SELECT        MemberId, JobclassId, BargainingUnitId, EffectiveDateMbrPosition, dba_local_bu_jobclass_effectivedate, type, dba_local_member_status_effectivedate,                          dba_local_member_status_member_type, bu_jobclass_seq, dba_local_bu_rate_member_type, MAX(dba_local_bu_rate_effectivedate) AS LastRateEffectiveDate, 
                         MAX(bu_rate_seq) AS LastRateSeq, MAX(monthly_factor) AS LastMonthlyFactor, MAX(factor) AS LastFactor, ssn
FROM            dbo.AdvancedDues
GROUP BY MemberId, JobclassId, BargainingUnitId, EffectiveDateMbrPosition, dba_local_bu_jobclass_effectivedate, type, dba_local_member_status_effectivedate, 
                         dba_local_member_status_member_type, bu_jobclass_seq, dba_local_bu_rate_member_type
HAVING        (MAX(dba_local_bu_rate_effectivedate) < dba_local_member_status_effectivedate)

[aellistechsupport]

Unless there's a better way, I think I have it

SELECT        MemberId, JobclassId, BargainingUnitId, EffectiveDateMbrPosition, dba_local_bu_jobclass_effectivedate, type, dba_local_member_status_effectivedate,                          dba_local_member_status_member_type, bu_jobclass_seq, dba_local_bu_rate_member_type, MAX(dba_local_bu_rate_effectivedate) AS LastRateEffectiveDate, 
                         MAX(bu_rate_seq) AS LastRateSeq, MAX(monthly_factor) AS LastMonthlyFactor, MAX(factor) AS LastFactor, ssn
FROM            dbo.AdvancedDues
WHERE        (dba_local_bu_rate_effectivedate < dba_local_member_status_effectivedate)
GROUP BY MemberId, JobclassId, BargainingUnitId, EffectiveDateMbrPosition, dba_local_bu_jobclass_effectivedate, type, dba_local_member_status_effectivedate, 
                         dba_local_member_status_member_type, bu_jobclass_seq, dba_local_bu_rate_member_type,

[orange]

see this topic
Get the value in another record

at this link from Allen Browne