October 7, 2007 at 10:30 pm
Hi all,
Can anyone please let me know a way I can generate a report as below.
SalesPerson's Name Total Sales Date Reached 100 Sales
Tom 150 10/09/2007
Ben 210 12/08/2007
Database structure is as below.
Sales Header table
Sales_Header_ID
Sales_Date
Sales_SalesPersonsID
Can anyone please send me SQL query ASAP will be highly appreciated. Thanks.
October 8, 2007 at 1:59 am
What have you tried so far? I would advise you to read about the RANK function and see if that helps you. If you're still having trouble, please post again.
Good luck
John
October 8, 2007 at 2:37 am
May be you can try this one......
SELECT T1.SalesPersonID, COUNT( * ) AS [Total Sales], T2.Date AS [Date Reached 100 Sales]
FROM Sales T1
LEFT JOIN ( SELECT SalesPersonsID, [Date], ROW_NUMBER() OVER( PARTITION BY SalesPersonsID ORDER BY SalesPersonsID, [Date] ) AS RowNumber FROM Sales ) T2
ON T1.SalesPersonID = T2.SalesPersonID AND T2.RowNumber = 100 GROUP BY T1.SalesPersonID, T2.Date
--Ramesh
October 11, 2007 at 11:30 pm
Thanks Ramesh and John. Problem solved.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply