CODEWITHSUNDEEP
sqlsql-serverreporting-services
JNM
JNM

Reputation: 1195

SQL Server Rank() by group

I have a data table like this:

Employee1   Product1    ProductGroup1   Quantity    SalesDate
Employee1   Product1    ProductGroup1   Quantity    SalesDate
Employee1   Product2    ProductGroup1   Quantity    SalesDate
Employee1   Product2    ProductGroup1   Quantity    SalesDate
Employee2   Product1    ProductGroup1   Quantity    SalesDate
Employee2   Product1    ProductGroup1   Quantity    SalesDate
Employee2   Product1    ProductGroup1   Quantity    SalesDate
Employee2   Product1    ProductGroup1   Quantity    SalesDate
Employee2   Product2    ProductGroup1   Quantity    SalesDate
Employee2   Product2    ProductGroup1   Quantity    SalesDate

There are multiple employees, multiple products, multiple product groups, multiple sales dates. In Reporting servises i have a matrix, where parent group is employee, child group is Product and column group is sales date. I need to rank products, in order to get first 5 and put other in other list. Problem is, that i have to rank product inside employee group and product can have multiple sales dates, while i need to evalue everything. In SQL now i have: Rank() Over (partition by DataTable.ProductGroup1, DataTable.Employee Order by Sum(Quantity) desc) as Rank But that gives me wrong result, because same product has different rank value, because rank function ranks using quantity in different sales dates. how should i write sql, so it returns me data wilth all sales dates, bus ranks using quantity summed from all dates?

EDIT:
Some datasets to explain what i get and what i need.

//DATA I HAVE
Employee_col    Product_col ProductGroup_col    Quantity_col    SalesDate_col
Employee1       Product1    ProductGroup1       100             2012-01
Employee1       Product1    ProductGroup1       200             2012-02
Employee1       Product2    ProductGroup1       50              2012-01
Employee1       Product2    ProductGroup1       80              2012-02
Employee2       Product1    ProductGroup1       200             2012-01
Employee2       Product1    ProductGroup1       70              2012-02
Employee2       Product2    ProductGroup1       20              2012-01
Employee2       Product2    ProductGroup1       450             2012-02

//RESULT I GET
Employee_col    Product_col ProductGroup_col    Quantity_col    SalesDate_col   Rank_col
Employee1       Product1    ProductGroup1       100             2012-01         2
Employee1       Product1    ProductGroup1       200             2012-02         1
Employee1       Product2    ProductGroup1       50              2012-01         4
Employee1       Product2    ProductGroup1       80              2012-02         3
Employee2       Product1    ProductGroup1       200             2012-01         2
Employee2       Product1    ProductGroup1       70              2012-02         3
Employee2       Product2    ProductGroup1       20              2012-01         4
Employee2       Product2    ProductGroup1       450             2012-02         1

//RESULT I NEED
Employee_col    Product_col ProductGroup_col    Quantity_col    SalesDate_col   Rank_col
Employee1       Product1    ProductGroup1       100             2012-01         1
Employee1       Product1    ProductGroup1       200             2012-02         1
Employee1       Product2    ProductGroup1       50              2012-01         2
Employee1       Product2    ProductGroup1       80              2012-02         2
Employee2       Product1    ProductGroup1       200             2012-01         2
Employee2       Product1    ProductGroup1       70              2012-02         2
Employee2       Product2    ProductGroup1       20              2012-01         1
Employee2       Product2    ProductGroup1       450             2012-02         1

Upvotes: 14

Views: 89510

Answers (2)

skc
skc

Reputation: 113

RANK() over (partition by Employee_col, Product_col, SalesDate_col order by Quantity_col ASC )

Upvotes: 8

podiluska
podiluska

Reputation: 51504

Try this query

select
#t.*, salesrank
from #t
inner join 
(
     select Employee, Product, RANK() over (partition by employee order by sq desc) as salesrank
     from
     (select Employee, Product , SUM (Quantity) sq from #t group by Employee, product) v
) v 
    on #t.product = v.product
    and #t.Employee =v.Employee

Upvotes: 13

Related Questions