Well, it's not a brain fart. That query is kinda kinky.
Use this as an example, replacing your tables and fields, it should work
BASED ON DATE!
SELECT DISTINCT T.CustomerID, T.TransactionDate, T.ID AS InvoiceID
FROM dbo.Transactions T INNER JOIN
(SELECT MAX(TransactionDate) AS maxdate,
CustomerID
FROM Transactions
WHERE deleted = 0
GROUP BY CustomerID) maxresults ON T.CustomerID
= maxresults.CustomerID AND T.TransactionDate = maxresults.maxdate
WHERE (T.Deleted = 0) AND (T.Status <> 'Pending') AND (T.AccountType =
'Customer')
GROUP BY T.CustomerID, T.TransactionDate, T.ID
Hope this helps.
-----Original Message-----
From: Gaulin, Mark [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 04, 2007 3:39 PM
To: CF-Talk
Subject: RE: SQL Query Help
Need more info: What would the query be to return the last invoice for a
single customer?
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 04, 2007 8:58 AM
To: CF-Talk
Subject: SQL Query Help
having brain fart here ... ughh
scenario: customer table, invoice table
I'd like to do a query to list the last invoice for each customer.
Can someone point me in the right direction please?
TIA,
Jenny
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
The most significant release in over 10 years. Upgrade & see new features.
http://www.adobe.com/products/coldfusion?sdid=RVJR
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274523
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
