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/
