SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 EXTRACTING THE LATEST DATE ONLY
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jlara
Starting Member

Panama
4 Posts

Posted - 09/04/2006 :  10:57:09  Show Profile  Click to see jlara's MSN Messenger address  Reply with Quote
Hi everyone..

I'me new to the forum. I have an issue and I wouldn't be here if I didn't need this help..

I have a table which has several date records under the same ID. For example, I have several Pay Dates from a customer and his ID to identify the payment. Of course, I have MANY MANY different ID's and MANY MANY different pay dates for each ID.

How could I extract ONLY the latest pay date for each different ID..????

Thanks for the help...

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/04/2006 :  11:05:05  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote

Select * from <Your Table > Tbl1 From 
<Date_Col> = (Select Max(<Date_Col>) From <Your Table> Tbl2 Where Tbl1.ID = Tbl2.ID)



Replace <Your Table> in the query with your table.. and <Date_Col> with the transaction date column.. and finally replace id with one in your table..

Chirag
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 09/04/2006 :  11:07:35  Show Profile  Reply with Quote
Generally something like this:

SELECT *
FROM CustomerPayments AS C1
WHERE pay_date = (
SELECT MAX(pay_date)
FROM CustomerPayments AS C2
WHERE C1.customer_id = C2.customer_id
)



Edited by - Arnold Fribble on 09/04/2006 11:08:01
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/04/2006 :  11:10:41  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
quote:
Originally posted by Arnold Fribble Generally something like this:

SELECT *
FROM CustomerPayments AS C1
WHERE pay_date = (
SELECT MAX(pay_date)
FROM CustomerPayments AS C2
WHERE C1.customer_id = C2.customer_id
)








Chirag
Go to Top of Page

jlara
Starting Member

Panama
4 Posts

Posted - 09/04/2006 :  11:33:06  Show Profile  Click to see jlara's MSN Messenger address  Reply with Quote
Thank you for the fast reply. Your help with a couple of modifications got for me exactly what I needed.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000