stored procedures - SQL Server - How would I rank products from the last month? -

i've started flesh things out bit, i'm having trouble adding in sort of window function intended result of having data, products, called in code ranked sales in last month. if given, appreciate it!

here being asked:

the ceo know sales based on last month's sales. please provide query rank products number of orders in last month. there should no skipped numbers.

here i've got far:

select      p.name 'product name',     isnull(count(distinct o.orderid), 0) 'orders count',     sum(isnull(o.ordertotal, 0)) 'orders total',     sum (isnull(oi.orderitemquantity, 0)) 'item total'       product p  inner join      orderitem oi on oi.productid = p.productid  inner join      orders o on o.orderid = oi.orderid group      p.name 

this need in stored procedure well, great too.

you can cte , rank() function

create procedure yourprocedure (@thedate datetime = null)  if @thedate null  begin    set @thedate = getdate() end  ;with cte as( select      p.name 'product name',     isnull(count(distinct o.orderid), 0) 'orders count',     sum(isnull(o.ordertotal, 0)) 'orders total',     sum (isnull(oi.orderitemquantity, 0)) 'item total'       product p  inner join      orderitem oi on oi.productid = p.productid  inner join      orders o on o.orderid = oi.orderid     --here limiting previous month based off month passed in     somedatefield >= dateadd(month, datediff(month, 0, @thedate)-1, 0)     ,     somedatefield < dateadd(month, datediff(month, 0, @thedate), 0) group      p.name)  select     *     ,dense_rank() on (order [orders count]) rk cte 

dense_rank() doesn't skip numbers, rank() depending on dataset.

example

declare @table table (id int identity (1,1), item int) insert @table values (1), (2), (3), (3), (3), (3), (4), (5), (6)  select      *      ,rank() on (order item) therank     ,dense_rank() on (order item) thedenserank @table  +----+------+---------+--------------+ | id | item | therank | thedenserank | +----+------+---------+--------------+ |  1 |    1 |       1 |            1 | |  2 |    2 |       2 |            2 | |  3 |    3 |       3 |            3 | |  4 |    3 |       3 |            3 | |  5 |    3 |       3 |            3 | |  6 |    3 |       3 |            3 | |  7 |    4 |       7 |            4 | --notice difference starting here |  8 |    5 |       8 |            5 | |  9 |    6 |       9 |            6 | +----+------+---------+--------------+ 

also, sounds homework--and if is, i'd suggest putting in question prevent assumptions.