Displaying correct $Amount
-
Hey there folks. Was wondering if I could get a little help on this one.
Based on the last query, out of the 3 listed below in the screen shot, I'm trying to display the corresponding $Amount for the associated record for MAX Renewal Date.
Currently the Last query results are showing the MAX Renewal date which is what Id like BUT its also showing the MAX Amount which is NOT what I want. I would like to get the corresponding Amount based on the MAX Date that was fond.
The results I'm looking for are:
Paid Last - 2017-04-17 AND Amount $25
-
Please check out the following link...
Forum Etiquette: How to post data/code on a forum to get the best help
-
this would probably work better as an outer apply to a correlated subquery instead of a left outer join since you want multiple columns from the same row, something like:
...
OUTER APPLY
(SELECT TOP 1 [Renewal Date] AS [Paid Last], ISNULL([Amount],0) AS Amount
FROM EAA_DUES d
WHERE d.[Member ID] = m.[Member ID]
ORDER BY [Renewal Date] DESC) md
... -
Without proper test data, this is untested code.
However, I believe that this will give you what you need
WITH cteData AS (
SELECT
m.[Member ID]
, [Renewal Date]
, [Amount]
, rn = ROW_NUMBER() OVER (PARTITION BY m.[Member ID]
ORDER BY [Renewal Date] DESC)
FROM EAA_MEMBERS m
LEFT JOIN EAA_DUES d
ON m.[Member ID] = d.[Member ID]
WHERE m.[Member ID] = 110
)
SELECT
[Member ID]
, [Renewal Date]
, [Amount]
FROM cteData
WHERE rn = 1; - Jason A. Long - Wednesday, April 19, 2017 11:18 AM
Jason,
Thank you for the link however I must say I'm not sure I know specifically what area in this topic I should be reading? There are a ton of posts and unless I've done something blatantly wrong, or inappropriate etc... a much better use of our time would be to please point out my mistake so that I can correct as its not as obvious to me what the issue here is. Thank you, and I'd like to learn from my honest mistake prior to making any additional errors going forward. Kind Regards! - netguykb - Wednesday, April 19, 2017 1:28 PM
Jason pointed you to the discussion on the article when he should have pointed you to the article itself.
Best Practices ArticleDrew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Wednesday, April 19, 2017 1:45 PM
DOH!!! Sorry about that... I just grabbed from my bookmarked links w/o opening. Not sure why I'd have the discussion bookmarked rather than the main article... Mea Culpa.
- Jason A. Long - Thursday, April 20, 2017 8:04 AM
The Latin is actually Mea Culpa... fyi...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Thursday, April 20, 2017 10:48 AM
Good catch... Corrected. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply