Top Sales Help Required

[andyj]

Hi all,

I'm looking for some help with something I've got myself a bit lost on

I need to create a ranking list of salespeople, BUT only their "TOP" 3 sales count... I then need to display this on a winforms app (for a scoreboard) and be able to print out the result.

There will be two tables. Salespeople and Transactions.
(I've simplified the tables for the purpose of the question).

Salespeople will consist of
EmployeeID
Name

Transactions will consist of
TransactionID
EmployeeID
Amount

So, If an employee made 4 sales with amounts of $130, $475, $25 and $248 I need to show the top 3 sales (i.e. not the $25) and the total of those top 3 sales. And this is for each salesperson, ranked in order of best 3 sales.

Any help woud be hugely appreciated. This is not for a highly paid job, but actually a voluntary position I hold running a tournament.

Thanks

Andy

 

[aBsOlUt]

There you go:

    Protected Const connString As String = "Data Source=YourDataSource;Initial Catalog=SalesDB;Integrated Security=SSPI;"
    Protected sqlCon As New SqlClient.SqlConnection(connString)
    Protected sqlCom As New SqlClient.SqlCommand()


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sqlDA As New SqlClient.SqlDataAdapter
        Dim dTable As New Data.DataTable

        With sqlCom
            .CommandText = "SET ROWCOUNT 3" & _
                           "SELECT * from Transactions " & _
                           "WHERE EmployeeID = 1 " & _
                           "ORDER BY Amount DESC"
            .Connection = sqlCon
            .CommandTimeout = 5
        End With

        sqlCon.Open()

        sqlDA.SelectCommand = sqlCom
        sqlDA.Fill(dTable)

        DataGridView1.DataSource = dTable.DefaultView
    End Sub

Make sure you reset your ROW COUNT after so you do not retrieve only 3 rows. Also, include a dataGridView in your form so you can see the results that are bound. It would be faster to use a dataReader but the above solution allows you to UPDATE your data as well.

 

[andyj]

Thanks aBsOlUt

That's looking pretty good, but that gives me the results in the datagridview with each transaction in a row. I need to group those transactions so that the look like below

EmployeeId - TopSale1 - TopSale2 - TopSale3 - Top3SalesTot

rather than
EmployeeId - Amount
EmpolyeeId - Amount
EmployeeId - Amount

And a further question...

How do I then do this for each employee that has transactions in the table ?

I hope that all makes sense!

Many Thanks

Andy

 

[cjard]

Please state what database system is in use. The SQL syntax I generate to solve the problem may be require use of vendor proprietary statistical functions. (I'm really hoping you say Oracle)

 

[andyj]

I'm using SQL Server 2000 or 2005...

 

[cjard]

ROFL, youre not sure which? OK.. well, let's hope this syntax is good for both. I dont actually have either so we're going to have to work together on this one. I take it TransactionID is the single primary key column of the Transaction table, yes?

 

[andyj]

I have both! Planning on SQL 2000 though.

Yes TransactionID would be the single PK.

Thanks

Andy

 

[cjard]

it wasnt needed, but there is a good chance this code WILL NOT WORK on sqls 2000 because i need the analytic function

If you really must use SQLS2000 i'll see if i can help change it to not use the analytic function, not easy though.

select
  employeeid,
  sum(best) as best,
  sum(secondbest) as secondbest,
  sum(thirdbest) as thirdbest,
  sum(best+secondbest+thirdbest) as total
from
(
  select 
    employeeid,
    case when ranking = 1 then freight else 0 end as best,
    case when ranking = 2 then freight else 0 end as secondbest,
    case when ranking = 3 then freight else 0 end as thirdbest
  from
  (
    SELECT
      employeeid, 
      freight,
      row_number() over(partition by employeeid order by freight desc) as ranking 
    from 
      orders
  ) rankingresult
  where
    ranking < 4
) summingresult
group by 
  employeeid

In making this query we first rank the totals by person:

    SELECT
      employeeid, 
      freight,
      row_number() over(partition by employeeid order by freight desc) as ranking 
    from 
      orders

every sale is given a position ranking so the highest sale gets 1:

empid, amount, rank
1, 1000, 1
1, 500, 2
1, 250, 3
1, 125, 4
2, 1000, 1
2, 500, 2
2, 250, 3
2, 125, 4

then we cut off all except the top 3 with a where < 4 clause
and we make 3 columns for best, seond and third, so our results become like:
1, 1000, 0, 0
1, 0, 500, 0
1, 0, 0, 250
2, ...

then we group and sum each column and add the total:

1, 1000, 500, 250 , 1750
2, ...

 

[andyj]

That SQL is WAY beyond me! but it works!

I have a real good chance of getting this app up and running now.

THANKS!

Andy

 

[andyj]

OK, its going really well but there is something else that has me stumped

Where would I put the INNER JOIN statement to pull in the name from the "salespeople" table ?

I've tried a few places, but I'm getting myself in a muddle with where to put it and where to use table qualifiers.

TIA

Andy

 

[cjard]

General format of a sql statement is:

SELECT
(what list of selections)
FROM
(what block of data)
WHERE
(some restiction conditions that talk about the block, if any are false the row is filtered)


your block of data is the entire query i wrote. you want to add to this a join on the other table.. so:

SELECT
(what)
FROM
(my data, remember to put it in brackets and give the block a name like i do)
INNER JOIN
(blah blah)
WHERE
...



remember that a join creates another block of data, and selecting from it creates a sub-block that is usable in other queries. here are the sub blocks in my query:

[LEFT]select
  employeeid,
  sum(best) as best,
  sum(secondbest) as secondbest,
  sum(thirdbest) as thirdbest,
  sum(best+secondbest+thirdbest) as total
from
[COLOR=blue][B](
  select 
    employeeid,
    case when ranking = 1 then freight else 0 end as best,
    case when ranking = 2 then freight else 0 end as secondbest,
    case when ranking = 3 then freight else 0 end as thirdbest
  from[/B][/COLOR]
[B][COLOR=red]  (
    SELECT
      employeeid, 
      freight,
      row_number() over(partition by employeeid order by freight desc) as ranking 
    from 
      orders
  ) rankingresult
[/COLOR][COLOR=blue]  where
    ranking < 4
) summingresult[/COLOR][/B]
group by 
  employeeid
[/LEFT]

where possible i indent like this:

select
  ...
from
(
  select
    ...
  from
    table1
    inner join
    table2
 
) block_name
inner join
table3

so that the brackets define a block of data at the same indent level as other tables they join

 

[andyj]

I've tried it, but getting nowhere.

SELECT     BowlerId, 
                  sum(best) AS best, 
                  sum(secondbest) AS secondbest, 
                  sum(thirdbest) AS thirdbest, sum(best + secondbest + thirdbest) AS total
 FROM         
            (
                 SELECT     BowlerId, 
                 CASE WHEN ranking = 1 THEN Series ELSE 0 END AS best, 
                 CASE WHEN ranking = 2 THEN Series ELSE 0 END AS secondbest, 
                 CASE WHEN ranking = 3 THEN Series ELSE 0 END AS thirdbest
            FROM          
               (
                      SELECT     SEOEntries.BowlerId, 
                      Series, 
                      row_number() OVER (partition BY SEOEntries.BowlerId ORDER BY Series DESC) AS ranking
                 FROM          
                    SEOEntries
            ) rankingresult
            WHERE     ranking < 4
 ) summingresult
     GROUP BY
         BowlerId

If I wrap that around another SELECT statement, including brackets and a name I still dont get the result.

Here is my simple code that pulls in the Names, I just can't work out how to incorporate it into the above. I think your explanation above is excellent, I'm just being too dumb to put it all together!!!!

SELECT     SEOEntries.BowlerId, ptbctou_dotnet.SEOBowlers.Forename, ptbctou_dotnet.SEOBowlers.Surname
FROM         ptbctou_dotnet.SEOBowlers INNER JOIN
                      SEOEntries ON ptbctou_dotnet.SEOBowlers.BowlerId = SEOEntries.BowlerId

 

[cjard]

You can incorporate it at any level actually, with equivalent results. Here i will outline how to do it as i explained.



Here is your code, i tidied it up a bit:

SELECT     
  SEOEntries.BowlerId, 
  ptbctou_dotnet.SEOBowlers.Forename, 
  ptbctou_dotnet.SEOBowlers.Surname
FROM         
  ptbctou_dotnet.SEOBowlers 
  INNER JOIN
  SEOEntries 
  ON 
    ptbctou_dotnet.SEOBowlers.BowlerId = SEOEntries.BowlerId

I'm actually going to do some more tidying:

SELECT     
  b.BowlerId, 
  b.Forename, 
  b.Surname
FROM         
  ptbctou_dotnet.SEOEntries e
  INNER JOIN
  ptbctou_dotnet.SEOBowlers b
  ON 
    e.BowlerId = b.BowlerId

see how the aliasing helps? (hopefully i didnt break anything!)

now lets think. you have a bunch of data that you tag the bowler names onto. note i turned the inner join round. no real reason i just like to arrange these thigns in my mind.. i have a block of data that i'm sticking the bowler names onto.. rather than a bunch of names i'm sticking the bowler data onto. in inner joins it doesnt matter, but i like to have my sqls reading like an english document.

here is the data we want to replace :

SELECT     
  b.BowlerId, 
  b.Forename, 
  b.Surname
FROM         
[B]  ptbctou_dotnet.SEOEntries[/B] e
  INNER JOIN
  ptbctou_dotnet.SEOBowlers b
  ON 
    e.BowlerId = b.BowlerId

you know this code works, and it puts the names on etc. well, i gave you a query, that needs names putting on. you'll laugh at the simplcity of this:

SELECT     
  b.BowlerId, 
  b.Forename, 
  b.Surname
FROM         
[B]  ([/B]
[B]    ptbctou_dotnet.SEOEntries[/B]
[B]  )[/B] e
  INNER JOIN
  ptbctou_dotnet.SEOBowlers b
  ON 
    e.BowlerId = b.BowlerId

now:

SELECT     
  b.BowlerId, 
  b.Forename, 
  b.Surname,
[B][COLOR=red]  e.*[/COLOR][/B]
FROM         
  (
[B]  select[/B]
[B]    employeeid,[/B]
[B]    sum(best) as best,[/B]
[B]    sum(secondbest) as secondbest,[/B]
[B]    sum(thirdbest) as thirdbest,[/B]
[B]    sum(best+secondbest+thirdbest) as total[/B]
[B]  from[/B]
[B]  ([/B]
[B]    select [/B]
[B]      employeeid,[/B]
[B]      case when ranking = 1 then freight else 0 end as best,[/B]
[B]      case when ranking = 2 then freight else 0 end as secondbest,[/B]
[B]      case when ranking = 3 then freight else 0 end as thirdbest[/B]
[B]    from[/B]
[B]    ([/B]
[B]      SELECT[/B]
[B]        employeeid, [/B]
[B]        freight,[/B]
[B]        row_number() over(partition by employeeid order by freight desc) as ranking [/B]
[B]      from [/B]
[B]        orders[/B]
[B]    ) rankingresult[/B]
[B]    where[/B]
[B]      ranking < 4[/B]
[B]  ) summingresult[/B]
[B]  group by [/B]
[B]    employeeid[/B]
  ) e
  INNER JOIN
  ptbctou_dotnet.SEOBowlers b
  ON 
    e.BowlerId = b.BowlerId

its like find/replace.
note the red addition to the select list, so our list actually looks interesting

 

[andyj]

Perfect!

Thanks cjard! sorry for the late reply I have been away.

Have just tried this out and read through it a few times, not only does it work but with your explanations I'm starting to understand it

Andy