?
Solved

SQL 2000> Group By Customer for Max(BillDate)

Posted on 2008-11-12
4
Medium Priority
?
452 Views
Last Modified: 2012-05-05
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!
0
Comment
Question by:jmiller47
  • 3
4 Comments
 
LVL 9

Expert Comment

by:Ernariash
ID: 22940787
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 22940805
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
 
LVL 9

Accepted Solution

by:
Ernariash earned 2000 total points
ID: 22940911

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
 
LVL 7

Author Closing Comment

by:jmiller47
ID: 31515980
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question