Using the Most Recent Data

Transact-SQL
1

Hello.

There is a sub query that is causing me heartache.

In order to get the most recent data, I am using a range on a LastModifiedDate field I don't want to have to remember to update it every time I run it.

How can I set a condition that will bring back the most recent data? Thanks.

(Select Max(Case When ccs.SR_BalanceTypeIndex='0' AND ccs.SR_RateTypeIndex='0' Then ccs.SubLoanRate/100 Else '0' End) From CreditCardSplitRateDetail ccs Where ccs.MemberNumber = ln.MemberNumber AND ccs.LoanNumber = ln.LoanNumber AND ccs.LastModifiedDate Between '08/01/2020' AND '08/31/2020')

2

Maybe:

AND ccs.LastModifiedDate >= DATEADD(month,DATEDIFF(month, 0, CURRENT_TIMESTAMP) -1, 0)
AND ccs.LastModifiedDate < DATEADD(month,DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
2 Likes
3

If your date range is always last month:

AND ccs.LastModifiedDate >= dateadd(month, datediff(month, 0, getdate()) - 1, 0)
AND ccs.LastModifiedDate <  dateadd(month, datediff(month, 0, getdate()), 0)

Using BETWEEN can miss or include data if there is a time on the column being checked.

2 Likes
4

@Ifor - beat me by a few seconds...:slight_smile:

5

Thank you both. I think that did the trick.