SQLTeam.com | Weblogs | Forums

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.