Rakesh k Rakesh k - 4 years ago 82
SQL Question

How do you partition results weekly in SQL?

I have to find the 3 highest spending customers from

Customer
(customer name, id) and
order
(order id, order amt, order date) for every week. If I run the query today, it should show the top 3 for all weeks for which order date exists.

I am thinking about doing a
Partition by
over the date (weekly), but I can't find any method to do that? Has anyone done a weekly partition of results?

I know it's not right, but this is what I have:

Select Top 3 customer_name, id OVER (partition by [week])
(
Select c.customer_name, c.id, o.order_amt,
from customer c
Join Order o
on c.id=o.id
group by c.id
)

Answer Source

According to your table structure, where orders.order_id is customer.id use this statement

select
    *
from
(
    select
        details.*
        ,dense_rank() over (partition by week_num order by order_amt desc) as rank_num
    from
    (
        select
            c.id as customer_id
            ,c.name
            ,sum(o.order_amt) as order_amt
            ,datepart(WEEK,o.order_date) as week_num    
        from customer c
        join orders o on c.id=o.order_id
        group by c.id,c.name,datepart(WEEK,o.order_date)
    )details

)dets 
where dets.rank_num<=3

Updated : changed statement to use just 2 tables

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download