Hi Tim, I had a query similar to yours.. And it took a very Long time to run.. The culprit was the sub-query. Try replacing the subquery with an inner join.. Also Change count(*) to count one particular field.. Eg: COUNT(TransactionID). Some table indexes might also Speed things up somewhat..
Hope this is of some use.. Adam -----Original Message----- From: Tim Rox [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2003 1:31 PM To: CFAussie Mailing List Subject: [cfaussie] OT - SQL Mindbender hi there, Am having some SQL headaches with quite a big database. I want to find the top 100 spenders during the previous month for each of a number of 'clubs'. I have a table full of transactions with a clubID, customerID, date, amount of the transaction. Unfortunately I can't use cold fusion for this one, so all the hard work has to be done in SQL. My attempt below seems to take an indefinate amount of time. Any suggestions on how it could be done more efficiently? I feel like the subquery is duplicating a lot of work that the main query is doing. SELECT ClubID, CustomerID, COUNT(*) AS Transactions, SUM(transactionValue) AS TotalValue FROM Transactions t1 WHERE DATEPART(month, transactionDate)=DATEPART(month, DATEADD(month, -1, GETDATE()) AND DATEPART(year, transactionDate)=DATEPART(year, DATEADD(month, -1, GETDATE()) AND MemberID IS IN (SELECT TOP 100 CustomerID, FROM Transactions WHERE DATEPART(month, transactionDate)=DATEPART(month, DATEADD(month, -1, GETDATE())) AND DATEPART(year, transactionDate)=DATEPART(year, DATEADD(month, -1, GETDATE())) AND ClubID = t1.ClubID GROUP BY CustomerID ORDER BY SUM(transactionValue) DESC) GROUP BY ClubID, CustomerID ORDER BY ClubID, totalValue DESC ---------------------- Tim Rox Newgency Pty Ltd 2a Broughton St Paddington 2021 Sydney, Australia Ph (02) 9331 2133 Fax (02) 9331 5199 Mobile: 0411 512 454 http://www.newgency.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
