I have this set of data using Microsoft SQL Server Management Studio
Category|pet name| date |food price|vet expenses|vat
A | jack |2017-08-28| 12.98 | 2424 |23
A | jack |2017-08-29| 2339 | 2424 |23
A | smithy |2017-08-28| 22.35 | 2324 |12
A | smithy |2017-08-29| 123.35 | 2432 |23
B | casio |2017-08-28| 11.38 | 44324 |32
B | casio |2017-08-29| 2.24 | 3232 |43
B | lala |2017-08-28| 343.36 | 42342 |54
B | lala |2017-08-29| 34.69 | 22432 |54
C | blue |2017-08-28| 223.02 | 534654 |78
C | blue |2017-08-29| 321.01 | 6654 |67
C | collie |2017-08-28| 232.05 | 4765 |43
C | collie |2017-08-29| 233.03 | 4654 |65
What I want to do is rank by food price, but group by category, order by category, pet name, date and then rank by vet expenses, but group by category, order by category, pet name, date and then rank by vat, but group by category, order by category, pet name, date.
I'm thinking this will be a join statement for the table above?
Something exactly like below:
Category|pet name| date |food price|vet expenses|vat|Rankfp|Rankve|Rankvat
A | jack |2017-08-28| 12.98 | 2424 |23 | 2 | 1 |1
A | jack |2017-08-29| 2339 | 2424 |23 | 1 | 2 |1
A | smithy |2017-08-28| 22.35 | 2324 |12 | 1 | 2 |2
A | smithy |2017-08-29| 123.35 | 2432 |22 | 2 | 1 |2
B | casio |2017-08-28| 11.38 | 44324 |32 | 2 | 1 |2
B | casio |2017-08-29| 2.24 | 3232 |43 | 2 | 2 |2
B | lala |2017-08-28| 343.36 | 42342 |54 | 1 | 2 |1
B | lala |2017-08-29| 34.69 | 22432 |54 | 1 | 1 |1
C | blue |2017-08-28| 223.02 | 534654 |78 | 2 | 1 |1
C | blue |2017-08-29| 321.01 | 6654 |67 | 1 | 1 |1
C | collie |2017-08-28| 232.05 | 4765 |43 | 1 | 2 |2
C | collie |2017-08-29| 233.03 | 4654 |65 | 2 | 2 |2
NB: this is not needed in the final output but to make it more readable I have ordered the outcome by category, pet name, date:
Category|pet name| date |food price|vet expenses|vat|Rankfp|Rankve|Rankvat
A | jack |2017-08-28| 12.98 | 2424 |23 | 2 | 1 |1
A | smithy |2017-08-28| 22.35 | 2324 |12 | 1 | 2 |2
A | jack |2017-08-29| 2339 | 2424 |23 | 1 | 2 |1
A | smithy |2017-08-29| 123.35 | 2432 |22 | 2 | 1 |2
B | casio |2017-08-28| 11.38 | 44324 |32 | 2 | 1 |2
B | lala |2017-08-28| 343.36 | 42342 |54 | 1 | 2 |1
B | lala |2017-08-28| 343.36 | 42342 |54 | 1 | 2 |1
B | lala |2017-08-29| 34.69 | 22432 |54 | 1 | 1 |1
C | blue |2017-08-28| 223.02 | 534654 |78 | 2 | 1 |1
C | collie |2017-08-28| 232.05 | 4765 |43 | 1 | 2 |2
C | blue |2017-08-29| 321.01 | 6654 |67 | 1 | 1 |1
C | collie |2017-08-29| 233.03 | 4654 |65 | 2 | 2 |2
The code I have below only ranks by category, but does not group by food price, vet expenses and vat.
RANK ()OVER(PARTITION BY [Category], [Date] order by [Category] ,[Pet Name],[Date]) as 'Rank'
Would it be a case of grouping the costs separately then left joining the rankings on to the original data?
(I will be using pivots and slicers in excel so want to have all the data on one table/query)
After walking away with some time to refresh my brain i had a eureka moment and solved this. It was actually easy when I thought about it.
so
the code to get the desired table goes something like this:
select *
, rank ()OVER(PARTITION BY [Category], [date] order by [food price], [Category] ,[pet name],[date]) as 'Rankfp'
, rank ()OVER(PARTITION BY [Category], [date] order by [vet expenses], [Category] ,[pet name], [date]) as 'Rankve'
, rank ()OVER(PARTITION BY [Category], [date] order by [vat], [Category] ,[pet name], [date]) as 'Rankvat'
from petcost
order by [category, [pet name]