Max date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Max date

Author

Topic

siumui
Yak Posting Veteran

54 Posts

Posted - 2012-04-03 : 11:31:33

Hello everyone.

I want to be able to retrieve the max date for column Payment and a second max date for column Payment for each Account.

Date examples:
Account DaysLate Payment Amount
111 20 2/11/2011 $50.00
111 60 2/28/2011 $12.00
111 5 3/02/2011 $190.00

.....

I need to retrieve the data in the columns like below:
Acct Recent_Days Recent_Date Previous_Days Previous_Date
111 5 3/02/2011 60 2/28/11

Thank you very much

siumui

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-04-03 : 11:44:56

[code]
WITH p AS (
SELECT Account, DaysLate, Payment, Amount,
Row_Number() OVER (partiontion by Account Order By Payment DESC) pmt
)
SELECT Account, DaysLate, Payment, Amoun
FROM p
WHERE pmt < 3
ORDER BY
pmt;[/code]

siumui
Yak Posting Veteran

54 Posts

Posted - 2012-04-03 : 11:53:51

quote:

Originally posted by russell


WITH p AS (
	SELECT	Account, DaysLate, Payment, Amount,
		Row_Number() OVER (partiontion by Account Order By Payment DESC) pmt
)
SELECT	Account, DaysLate, Payment, Amoun
FROM	p
WHERE	pmt < 3
ORDER BY
	pmt;

I'm trying to understand your codes.
Why do you use WHERE pmt < 3?
I guess because I really don't understand your codes that much, so i can't really pictured what's the result will be. I'm doing some reading on the side from your codes, trying to understand row_number.

Thank you very much.

siumui

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-04-03 : 12:13:49

http://msdn.microsoft.com/en-us/library/ms186734.aspx

< 3 means the top 2 in this case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources