Date before the last date record?

[aellistechsupport]

Hello,
So I need to display the record before the most recent record.
I can't figure out how to do this.

For instance, there are several member records with various dates.
They don't want the information from the most recent record, as that can be determined by Last or Max on Group By, they want the record before the most recent record.

[Bob Fitz]

Maybe something like:

SELECT TOP 1 YourTable.YourDateFieldFROM YourTable
WHERE (((YourTable.YourDateField)<DateValue(DMax("YourDateField","YourTable"))))
ORDER BY YourTable.YourDateFieldDESC;

[orange]

Another approach to get the second to last (rank 2) is to use this pattern

SELECT TOP 1 YourTable.YourDateField FROM YourTable
where yourdateField not in 
( SELECT TOP 1 YourTable.YourDateField FROM YourTable)
....

In effect, get the next Top 1 that isn't the Top 1.

[aellistechsupport]

Ok, I am getting only one Member record

SELECT TOP 1 AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.BargainingUnitId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.type, AdvancedDues.dba_local_member_status_effectivedate, AdvancedDues.dba_local_member_status_member_type, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, AdvancedDues.dba_local_bu_rate_effectivedate, AdvancedDues.monthly_factor, AdvancedDues.factorFROM AdvancedDues
WHERE (((AdvancedDues.dba_local_bu_rate_effectivedate)<DateValue(DMax("dba_local_bu_rate_effectivedate","AdvancedDues"))))
GROUP BY AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.BargainingUnitId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.type, AdvancedDues.dba_local_member_status_effectivedate, AdvancedDues.dba_local_member_status_member_type, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, AdvancedDues.dba_local_bu_rate_effectivedate, AdvancedDues.monthly_factor, AdvancedDues.factor
ORDER BY AdvancedDues.dba_local_bu_rate_effectivedate DESC;

It needs to be per MemberID
So for each member, the 2nd to last (max) on dba_local_bu_rate_effectivedate
NOTE: the Monthly_Factor and Factor will have a different value for each dba_local_bu_rate_effectivedate per MemberID

[CJ_London]

sounds like you need to use a couple of subquies - something like

SELECT AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.BargainingUnitId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.type, AdvancedDues.dba_local_member_status_effectivedate, AdvancedDues.dba_local_member_status_member_type, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, AdvancedDues.dba_local_bu_rate_effectivedate, AdvancedDues.monthly_factor, AdvancedDues.factor
FROM AdvancedDues
WHERE AdvancedDues.dba_local_bu_rate_effectivedate=(SELECT TOP 2 dba_local_bu_rate_effectivedate FROM  AdvancedDues as temp1 WHERE MemberID= AdvancedDues.MemberID ORDER BY  dba_local_bu_rate_effectivedate DESC) AND AdvancedDues.dba_local_bu_rate_effectivedate<>(SELECT TOP 1 dba_local_bu_rate_effectivedate FROM  AdvancedDues as temp2 WHERE MemberID= AdvancedDues.MemberID ORDER BY  dba_local_bu_rate_effectivedate DESC)

[aellistechsupport]

Hello Ajax,
Thank you

I am getting At Most One Record Can be Returned by this Subquery

[aellistechsupport]

Hi orange,
so I attempted your suggestion

SELECT TOP 1 AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.BargainingUnitId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.type, AdvancedDues.dba_local_member_status_effectivedate, AdvancedDues.dba_local_member_status_member_type, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, AdvancedDues.dba_local_bu_rate_effectivedate, AdvancedDues.bu_rate_seq, AdvancedDues.monthly_factor, AdvancedDues.factor, AdvancedDues.ssnFROM AdvancedDues
WHERE (((AdvancedDues.dba_local_bu_rate_effectivedate) Not In (SELECT TOP 1 AdvancedDues.dba_local_bu_rate_effectivedate FROM AdvancedDues)))
GROUP BY AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.BargainingUnitId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.type, AdvancedDues.dba_local_member_status_effectivedate, AdvancedDues.dba_local_member_status_member_type, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, AdvancedDues.dba_local_bu_rate_effectivedate, AdvancedDues.bu_rate_seq, AdvancedDues.monthly_factor, AdvancedDues.factor, AdvancedDues.ssn;

It takes a very long time and getting the same result as Bob (though his runs faster)
The result being I get only one memberID

As mentioned it needs to be for each MemberID to display the last rate effective date prior to the most recent date along with the corresponding monthly factor and factor as these figures coincide with the rate effective date

example with the basic fields
1. MemberID: 123 rate effective date: 5/1/2016 monthly factor: $40 factor: $5
2. MemberID: 123 rate effective date: 12/1/2016 monthly factor: $42 factor: $7
3. MemberID: 123 rate effective date: 3/1/2017 monthly factor: $45 factor: $8

Need the result to show the 2nd record as it's the record information PRIOR to the most recent

[CJ_London]

OK change

.....AdvancedDues.dba_local_bu_rate_effectivedate=(SELECT TOP 2 dba_local_bu_rate_effectivedate....

to

.....AdvancedDues.dba_local_bu_rate_effectivedate IN (SELECT TOP 2 dba_local_bu_rate_effectivedate....

regards speed of operation, all fields used for sorting or criteria should be indexed

[aellistechsupport]

Ajax,
that gave me two records for the 1st MemberID

This is what I ended up doing:
1. Query1 - Get the Max on Rate Effective, Monthly_Factor, Factor

SELECT AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.BargainingUnitId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.type, AdvancedDues.dba_local_member_status_effectivedate, AdvancedDues.dba_local_member_status_member_type, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, Max(AdvancedDues.dba_local_bu_rate_effectivedate) AS MaxOfdba_local_bu_rate_effectivedate, Max(AdvancedDues.bu_rate_seq) AS MaxOfbu_rate_seq, Max(AdvancedDues.monthly_factor) AS MaxOfmonthly_factor, Max(AdvancedDues.factor) AS MaxOffactor, AdvancedDues.ssnFROM AdvancedDues
GROUP BY AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.BargainingUnitId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.type, AdvancedDues.dba_local_member_status_effectivedate, AdvancedDues.dba_local_member_status_member_type, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, AdvancedDues.ssn;

then

2. Query2 -

SELECT AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.BargainingUnitId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.type, AdvancedDues.dba_local_member_status_effectivedate, AdvancedDues.dba_local_member_status_member_type, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, Max(AdvancedDues.dba_local_bu_rate_effectivedate) AS MaxOfdba_local_bu_rate_effectivedate1, Max(AdvancedDues.bu_rate_seq) AS MaxOfbu_rate_seq1, Max(AdvancedDues.monthly_factor) AS MaxOfmonthly_factor1, Max(AdvancedDues.factor) AS MaxOffactor1, AdvancedDues.ssnFROM AdvancedDues LEFT JOIN ObtainBalances ON (AdvancedDues.MemberId = ObtainBalances.MemberId) AND (AdvancedDues.[dba_local_bu_rate_effectivedate] = ObtainBalances.[MaxOfdba_local_bu_rate_effectivedate])
GROUP BY AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.BargainingUnitId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.type, AdvancedDues.dba_local_member_status_effectivedate, AdvancedDues.dba_local_member_status_member_type, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, AdvancedDues.ssn, ObtainBalances.MaxOfdba_local_bu_rate_effectivedate
HAVING (((ObtainBalances.MaxOfdba_local_bu_rate_effectivedate) Is Null));

May not be the best, it is giving me the result sought.

if anyone can write a better and faster way, please let me know.

Thank you

[CJ_London]

Since you get two records, that implies you have not used the full code I provided. You have not provided it so I can't suggest where you are going wrong.

But you have a solution so good luck with your project

[aellistechsupport]

Yea, I don't know, out of the 3 suggestions, 1st two produced just one record. Yours produced an error and on the 2nd go, this is what I did

SELECT TOP 2 AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.BargainingUnitId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.type, AdvancedDues.dba_local_member_status_effectivedate, AdvancedDues.dba_local_member_status_member_type, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, AdvancedDues.dba_local_bu_rate_effectivedate, AdvancedDues.bu_rate_seq, AdvancedDues.monthly_factor, AdvancedDues.factor, AdvancedDues.ssnFROM AdvancedDues
WHERE (((AdvancedDues.dba_local_bu_rate_effectivedate) In (SELECT TOP 2 AdvancedDues.dba_local_bu_rate_effectivedate FROM AdvancedDues)))
GROUP BY AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.BargainingUnitId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.type, AdvancedDues.dba_local_member_status_effectivedate, AdvancedDues.dba_local_member_status_member_type, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, AdvancedDues.dba_local_bu_rate_effectivedate, AdvancedDues.bu_rate_seq, AdvancedDues.monthly_factor, AdvancedDues.factor, AdvancedDues.ssn;

it produced 2 records of the same MemberID.

[CJ_London]

that is not the query I provided. The whole query is (correct for the IN)

SELECT AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.BargainingUnitId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.type, AdvancedDues.dba_local_member_status_effectivedate , AdvancedDues.dba_local_member_status_member_type, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, AdvancedDues.dba_local_bu_rate_effectivedate, AdvancedDues.monthly_factor, AdvancedDues.factor
FROM AdvancedDues
WHERE AdvancedDues.dba_local_bu_rate_effectivedate IN (SELECT TOP 2 dba_local_bu_rate_effectivedate FROM AdvancedDues as temp1 WHERE MemberID= AdvancedDues.MemberID ORDER BY dba_local_bu_rate_effectivedate DESC) AND AdvancedDues.dba_local_bu_rate_effectivedate<>(SEL ECT TOP 1 dba_local_bu_rate_effectivedate FROM AdvancedDues as temp2 WHERE MemberID= AdvancedDues.MemberID ORDER BY dba_local_bu_rate_effectivedate DESC)

[aellistechsupport]

I get the same error message:

At Most One Record Can be Returned by this Subquery

[CJ_London]

OK, change

....AND AdvancedDues.dba_local_bu_rate_effectivedate<>(SELECT TOP 1 dba_local_bu_rate_effectivedate....

to

....AND AdvancedDues.dba_local_bu_rate_effectivedate NOT IN (SELECT TOP 1 dba_local_bu_rate_effectivedate....

[aellistechsupport]

Ok, this is giving results and no longer an error.

It takes awhile to run
To see if it's producing the correct result, I did a quick query against this to see what I get for a particular MemberID and I get no results for this Member
And there should be ... :/