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
we can use rank function and group by in the same query set but all the columns should be contained in either aggregate function or the Group by clause.
The RANK() function is a window function that assigns a rank to each row in the partition of a result set. The rank of a row is determined by one plus the number of ranks that come before it. RANK() OVER ( PARTITION BY <expr1>[{,<expr2>...}] ORDER BY <expr1> [ASC|DESC], [{,<expr2>...}] )
To partition rows and rank them by their position within the partition, use the RANK() function with the PARTITION BY clause. SQL's RANK() function allows us to add a record's position within the result set or within each partition.
Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5).
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
RANK() over (partition by Employee_col, Product_col, SalesDate_col order by Quantity_col ASC )
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With