Hello.
I thought I figured it out, but the results are not reflecting what I am asking for.
Here is the code:
Select
ccs.MemberNumber,
ccs.LoanNumber,
ccs.SubloanRate,
ccs.LastModifiedDate
From
CreditCardSplitRateDetail ccs
Where
ccs.MemberNumber='XXX0460'
AND ccs.LoanNumber='12X'
AND ccs.SR_BalanceTypeIndex='0' AND ccs.SR_RateTypeIndex='0' AND ccs.SequenceNumber='0'
AND ccs.LastModifiedDate = (Select Max(ccs2.LastModifiedDate) From CreditCardSplitRateDetail ccs2 Where ccs2.CreditCardSplitRateDetailKey=ccs.CreditCardSplitRateDetailKey)
An the results:
I just want the most recent date ... but I am getting all three still.
Thanks for your help.
Select
MemberNumber,
LoanNumber,
SubloanRate,
LastModifiedDate
From (
Select
ccs.MemberNumber,
ccs.LoanNumber,
ccs.SubloanRate,
ccs.LastModifiedDate,
Row_Number() Over(Partition by ccs.MemberNumber Order by ccs.LastModifiedDate Desc) As RowNum
From
CreditCardSplitRateDetail ccs
Where
ccs.MemberNumber='XXX0460'
AND ccs.LoanNumber='12X'
AND ccs.SR_BalanceTypeIndex='0'
AND ccs.SR_RateTypeIndex='0'
AND ccs.SequenceNumber='0'
) as derived
Where RowNum = 1
Order by MemberNumber
Thank you. That worked ... just seems like a lot of work to get the outcome!
I appreciate your response/solution.
You're welcome. That's the cleanest way I know to do it, that is, with the least I/O and code.
You could also use this little trick:
Select Top 1 With Ties
ccs.MemberNumber
, ccs.LoanNumber
, ccs.SubloanRate
, ccs.LastModifiedDate
From CreditCardSplitRateDetail ccs
Where ccs.MemberNumber = 'XXX0460'
And ccs.LoanNumber = '12X'
And ccs.SR_BalanceTypeIndex = '0'
And ccs.SR_RateTypeIndex = '0'
And ccs.SequenceNumber = '0'
Order By
row_number() Over(Partition by ccs.MemberNumber Order by ccs.LastModifiedDate Desc);
1 Like
I guess I should have stated that I used the "derived" table style to allow for multiple MemberNumbers to be listed.
The single "Top (1)" without a derived table with work only with one and only one MemberNumber. If you wanted to list the TOP 1 for, say, all MemberNumbers, that code won't have to be rewritten (and I suspect jeff would rewrite it to be a derived table using ROW_NUMBER() in the inner query).
The 'Top 1 With Ties' will handle multiple member numbers - since the partition is by member number. The Order By results in a value of 1 for the latest date per member number - and the Ties includes each member number.
As with anything - there are situations where one or the other will perform better and in those cases I will utilize the other method.
hi when i tried to do this i got the result
i noticed that when the
+++ CreditCardSplitRateDetailKey +++
is the same for all records of the same member number
i get the correct result
Dont know how your data is !!!
It always helps a lot to see your data OR sample data
Anyhow here's my sample data
please click arrow to the left for sample data script .. with same CreditCardSplitRateDetailKey
drop table CreditCardSplitRateDetail
go
create table CreditCardSplitRateDetail
(
CreditCardSplitRateDetailKey int not null,
MemberNumber varchar(50),
LoanNumber varchar(50),
SR_BalanceTypeIndex varchar(50),
SR_RateTypeIndex varchar(50),
SequenceNumber varchar(50),
SubloanRate float,
LastModifiedDate datetime
)
go
insert into CreditCardSplitRateDetail select 1111,'XXX0460','12X','0','0','0',9.1500,'2018-01-16 23:17:00.000'
insert into CreditCardSplitRateDetail select 1111,'XXX0460','12X','0','0','0',8.9000,'2017-12-18 23:32:00.000'
insert into CreditCardSplitRateDetail select 1111,'XXX0460','12X','0','0','0',9.9000,'2019-10-28 23:44:00.000'
go
go
select * from CreditCardSplitRateDetail
go
please click arrow to the left for SQL of Moxioron
SELECT ccs.membernumber,
ccs.loannumber,
ccs.subloanrate,
ccs.lastmodifieddate
FROM creditcardsplitratedetail ccs
WHERE ccs.membernumber = 'XXX0460'
AND ccs.loannumber = '12X'
AND ccs.sr_balancetypeindex = '0'
AND ccs.sr_ratetypeindex = '0'
AND ccs.sequencenumber = '0'
AND ccs.lastmodifieddate = (SELECT Max(ccs2.lastmodifieddate)
FROM creditcardsplitratedetail ccs2
WHERE ccs2.creditcardsplitratedetailkey = ccs.creditcardsplitratedetailkey)