I've NET_SALES Table.
it's very large table.
I'm selecting couple of fields to cmplete my task.
I need to rank all invoice total by customer for every sales rep.
So sales rep ABC will have 50 custoemrs. and 1 sales rep can sell more than 1 time to 1 customer.
so for ABC, MonthToDate_Rank for custoemr AAA will be 1 and total will be 1000$
MonthToDate_Rank for custoemr AAB will be 2 and total will be 900$
For Rep ABC1 MonthToDate_Rank for custoemr DEF will be 1 and total will be 1000$
MonthToDate_Rank for custoemr DEG will be 2 and total will be 900$
I can do it with using ROW_NUMBER() OVER(partition by rep_name order by sum(total_sale)
and I've where condition which will select values only for current month.
I also calculate the same with QuarterToDate_Rank, YearToDate_Rank. but all are in different rows.
Now, there is new requirement that I've to calclate everything in 1 row.
So ABC rep will have customer AAA with MTD_Rank =1 , QTD_Rank = 24 , YTD_Rank = 1000
I've to calcualate everything together.
View Complete Post