Post reply

Total nos of sales done with date on which 100 sales reached

  • October 7, 2007 at 10:30 pm

    #178174

    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

    #739912

    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

    #739919

    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

    #741440

    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