SQL 2000> Group By Customer for Max(BillDate)

Hello,
I have a single table with each record being bill information.
Example (I have excluded some columns that would not be relevant)
BILLID (int) (Primary Key)
CUST_ACCT_NO (int)
BILL_AMOUNT (money)
BILL_DATE (Datetime)

Each customer will have several bills (one each month).  I am trying to find the amount of the last bill only. I would like to list the CUST_ACCT_NO, BILLAMOUNT, BILL_DATE only where the bill date for each customer is the max bill date (their last bill only).

I know this is somewhat simple, but I am just not getting it.
Thanks in advance!
LVL 7
jmiller47Asked:
Who is Participating?
 
ErnariashConnect With a Mentor Commented:

This query will give you the last LAST BILLDATE by customer,
but since your BILLID is a PK your las Billid sould be your LAST BILLDATE (faster query)

--------Last bill id 
SELECT * FROM BILLS B 
WHERE EXISTS 
( SELECT 1 FROM ( 
		SELECT CUST_ACCT_NO, MAX(BILLID) LASTBILLID 
		FROM BILLS 
		GROUP BY CUST_ACCT_NO 
) L 
WHERE B.CUST_ACCT_NO = L.CUST_ACCT_NO AND B.BILLID = L.LASTBILLID 
) 
 
--------Last bill date 
SELECT * FROM BILLS B 
WHERE EXISTS 
(	SELECT 1 FROM 
	( 
			SELECT CUST_ACCT_NO, MAX(BILL_DATE) LASTBILLDATE FROM BILLS 
			GROUP BY CUST_ACCT_NO 
		) L 
	WHERE B.CUST_ACCT_NO = L.CUST_ACCT_NO AND B.BILLDATE = L.LASTBILLDATE 
)

Open in new window

0
 
ErnariashCommented:
I hope this will hepl you


SELECT * FROM BILLS B 
WHERE EXISTS 
( SELECT 1 FROM ( 
SELECT CUST_ACCT_NO, MAX(BILL_DATE) LASTBILLDATE FROM BILLS 
) L 
WHERE B.CUST_ACCT_NO = L.CUST_ACCT_NO AND B.LASTBILLDATE = L.LASTBILLDATE 
)

Open in new window

0
 
ErnariashCommented:
Sorry I missed the group by ...
SELECT * FROM BILLS B 
WHERE EXISTS 
( SELECT 1 FROM ( 
		SELECT CUST_ACCT_NO, MAX(BILL_DATE) LASTBILLDATE FROM BILLS 
		GROUP BY CUST_ACCT_NO
) L 
WHERE B.CUST_ACCT_NO = L.CUST_ACCT_NO AND B.LASTBILLDATE = L.LASTBILLDATE 
)

Open in new window

0
 
jmiller47Author Commented:
Thank you so much for the quick response. I tried your first solution and wondered what I did wrong. Then I saw your immediate reply. :)

Everything worked great and I really appreciate it!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.