Selecting the 2 most recent dates

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

Selecting the 2 most recent dates

Author

Topic

velvet_tiger
Starting Member

12 Posts

Posted - 2008-03-31 : 13:43:11

Hi guys,

I am having a problem in outputting the last two dates that a bill has been sent out.

Background:
A customers recieves a bill 4 times a year. The dates for issuing these bills are not fixed and I dont know the date that bills are usually sent out.

The database that I am using stores all of the dates that a bill would have been sent out.

Issue:
A customer recently requested to see the dates of last 2 bills along with their value.

I know that you can view a customer last date by using the following statement.

Select max(issuedate)LastBill
from statements

but i don't know how i could output the last 2 bills that were issued

Any ideas....??

Velvet.

I know that in order to select the

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-31 : 13:49:00

[code]Select top 2
issuedate,
BillAmount
from
statements
where
CustID = @CustID
order by
issuedate desc[/code]

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-31 : 13:51:34

if you are using SQL 2005

SELECT t.CustomerID,t.issuedate
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY issuedate DESC)AS RowNo,
CustomerID,
issuedate
FROM statements)t
WHERE t.RowNo<=2

if sql 2000

SELECT t.CustomerID,t.issuedate
FROM
(SELECT count(*) + 1 FROM statements 
WHERE customerid=s.customerid
AND issuedate>s.issuedate)AS RowNo,
CustomerID,
issuedate
FROM statements s)t
WHERE t.RowNo<=2

this will give last two bills for each customer.

velvet_tiger
Starting Member

12 Posts

Posted - 2008-03-31 : 14:13:43

Hi Michael,

I tried your script and I am getting an error in your code. It is telling that it does not recognise RowNo. I made some changes so the query now looks like this.


SELECT t.CardNumber
	,t.IssuedDate
	,(	SELECT count(*) + 1 
	 	FROM statements s
		WHERE CardNumber=s.CardNumber
		      AND IssuedDate>s.IssuedDate
	)AS RowNo
	,CardNumber
	,IssuedDate

FROM statements t

WHERE RowNo<=2

Oh and i am using sql 2000

Thanks alot,
velvet

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-04-01 : 13:48:30

It wasn't Michael that posted using ROW_NUMBER(); it was visakh16. ROW_NUMBER is SQL Server 2005.

If you're using SQL Server 2000 (which would have been nice to know in your original post), use Michael's SQL instead.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources