Grabbing the Most Recent Data

1

Hello.

I am using a subquery trying to grab the most recent data, but, having no luck in doing so.

Here is the normal query, that I am able to get the information:
Select
ccr.RateIndexRate + ccr.RateIndexMargin,
Max(ccr.LastModifiedDate)

From
CreditCardSplitRateDetail ccr

Where
ccr.RateIndexID = 1
AND ccr.SR_RateTypeIndex = 0
AND ccr.SR_BalanceTypeIndex = 0

Group By
ccr.RateIndexRate,
ccr.RateIndexMargin

I need this in a subquery, but , can't seem to wrap my head on how to do it.
(Select Case When Max(ccr.LastModifiedDate) Then Max(ccr.RateIndexRate + ccr.RateIndexRate) Else 0 End From CreditCardSplitRateDetail ccr Where ccr.MemberNumber = ln.MemberNumber AND ccr.LoanNumber = ln.LoanNumber AND ccr.RateIndexID = 1 AND ccr.SR_RateTypeIndex = 0 AND ccr.SR_BalanceTypeIndex = 0)

2

What do you get when you run the subquery?

Select 
  Case When Max(ccr.LastModifiedDate)   
    Then Max(ccr.RateIndexRate + ccr.RateIndexRate) 
    Else 0 End 
  From CreditCardSplitRateDetail ccr 
 Where ccr.MemberNumber = ln.MemberNumber 
AND ccr.LoanNumber = ln.LoanNumber 
AND ccr.RateIndexID = 1 
AND ccr.SR_RateTypeIndex = 0 
AND ccr.SR_BalanceTypeIndex = 0
3

When I run the actual query, I get:

6.9500 2018-03-03 02:16:00.000

Which is the RateIndexRate + the RateIndexMargin and the most recent LastModifiedDate

But, I haven't figured it out how to get the subquery to work.

4

hi hope this helps .. copy paste .. plug and play ..

5

Thank you. But, none of those examples spoke to my issue.

6
1 Like
7

I tried this:

(Select Max(ccr.RateIndexRate + ccr.RateIndexMargin) From CreditCardSplitRateDetail ccr Where ccr.MemberNumber = ln.MemberNumber AND ccr.LoanNumber = ln.LoanNumber AND (ccr.RateIndexID = 1 OR ccr.RateIndexID = 8) AND ccr.SR_RateTypeIndex = 0 AND ccr.SR_BalanceTypeIndex = 0 AND ccr.LastModifiedDate = Max(ccr.LastModifiedDate))

But got the "an Aggregate may not appear" message

8

You are trying to return 2 columns from a sub-query, which isn't allowed in the select portion. A column can return only one column.

With that in mind - you have a couple of options:

  1. CTE - put the sub-query into a CTE and include the columns that are needed to join to the outer table. Then reference the CTE and join to the data.
  2. Derived Table - same as CTE except the statement is included in the FROM.
  3. OUTER/CROSS APPLY - put the sub-query in an apply and correlate the sub-query to the outer query (i.e. WHERE sub.somecolumn = outer.somecolumn)

Since you have not include the full query - not much more I can provide.

9

Thanks for the response.

I tried this, but got the same 'An Aggregate may not appear in the WHERE clause" message.

(Select Max(ccr.RateIndexRate)
From CreditCardSplitRateDetail ccr
Where ccr.MemberNumber = ln.MemberNumber
AND ccr.LoanNumber = ln.LoanNumber
AND (ccr.RateIndexID = 1 OR ccr.RateIndexID = 8)
AND ccr.SR_RateTypeIndex = 0
AND ccr.SR_BalanceTypeIndex = 0
AND ccr.LastModifiedDate = (select Max(ccr.LastModifiedDate) From CreditCardSplitRateDetail))

The desired result of the subquery is the most recent ccr.RateIndexRate.

10

Here is the fully query.

Select
ln.MemberNumber,
ln.LoanNumber,
ln.OpenDate,

(Select (ccr.RateIndexRate)
From CreditCardSplitRateDetail ccr
Where ccr.MemberNumber = ln.MemberNumber
AND ccr.LoanNumber = ln.LoanNumber
AND (ccr.RateIndexID = 1 OR ccr.RateIndexID = 8)
AND ccr.SR_RateTypeIndex = 0
AND ccr.SR_BalanceTypeIndex = 0
AND ccr.LastModifiedDate = (select Max(ccr.LastModifiedDate) From CreditCardSplitRateDetail))

From
Loan ln

11 
Select ln.MemberNumber
     , ln.LoanNumber
     , ln.OpenDate
     , ir.RateIndexRate
  From Loan                             ln

 Cross Apply (Select Top (1)
                     ccr.RateIndexRate
                From CreditCardSplitRateDetail           ccr
               Where ccr.SR_RateTypeIndex = 0
                 And ccr.SR_BalanceTypeIndex = 0
                 And ccr.MemberNumber = ln.MemberNumber
                 And ccr.LoanNumber = ln.LoanNumber
                 And ccr.RateIndexID In (1, 8)
               Order By
                     ccr.LastModifiedDate desc
             )                          ir

 Where ... -- something missing???

One way is the above cross apply which gets the latest value. With that said - you are not filtering or grouping so you should be able to do something like this:

Select ln.MemberNumber
     , ln.LoanNumber
     , ln.OpenDate
     , RateIndexRate = first_value(ir.RateIndexRate) Over(Partition By ln.MemberNumber, ln.LoanNumber, ln.OpenDate Order By ln.LastModifiedDate desc)
  From Loan                             ln
 Where ... -- something missing???

May need to adjust the partition and/or use last_value.

12

Hey Jeff. First of all, thank you for your comprehensive response!

Looks like this did the trick as a subquery:

Rate_Index = (Select Top (1) ccr.RateIndexRate
From CreditCardSplitRateDetail ccr
Where ccr.SR_RateTypeIndex = 0
And ccr.SR_BalanceTypeIndex = 0
And ccr.MemberNumber = ln.MemberNumber
And ccr.LoanNumber = ln.LoanNumber
And ccr.RateIndexID In (1, 8)
Order By
ccr.LastModifiedDate desc
)

Thank you for your assistance.

13

You may find that the cross apply performs better - but at a minimum you should test.