CODEWITHSUNDEEP
sql-serversql-server-2008
paul
paul

Reputation: 11

SQL - I want to Select the top 5 Volumes of Sales Per Week

Im struggling to find an answer to this that has been posted so far. In SQL Server 2008 im trying Top 5 Sales per calender week. For instance a data set which would read

01/01/2014 | 50 | Item1
01/01/2014 | 40 | Item2
01/01/2014 | 30 | Item3
01/01/2014 | 20 | Item4
01/01/2014 | 10 | Item5
01/01/2014 | 5 | Item6
01/01/2014 | 2 | Item7

08/01/2014 | 50 | Item4
08/01/2014 | 40 | Item3
08/01/2014 | 30 | Item2
08/01/2014 | 20 | Item1
08/01/2014 | 10 | Item5
08/01/2014 | 5 | Item7
08/01/2014 | 2 | Item6

Would only return 

01/01/2014 | 50 | Item1
01/01/2014 | 40 | Item2
01/01/2014 | 30 | Item3
01/01/2014 | 20 | Item4
01/01/2014 | 10 | Item5
08/01/2014 | 50 | Item4
08/01/2014 | 40 | Item3
08/01/2014 | 30 | Item2
08/01/2014 | 20 | Item1
08/01/2014 | 10 | Item5

At present my code is returning the top 5 out of all of the data set and seems to ignore the Week factor

Any help greatly appreciated

**edit apologies my current code results in me getting

01/01/2014 | 50 | Item1
08/01/2014 | 50 | Item4
01/01/2014 | 40 | Item2
08/01/2014 | 40 | Item3
01/01/2014 | 30 | Item3

I will give suggestions below a shot

Upvotes: 1

Views: 933

Answers (2)

mohan111
mohan111

Reputation: 8865

declare @tab table
(
[month] date,
CustomerCode int,
ITEM varchar(20) 
)
insert into @tab
select
'01/01/2014',50 ,'Item1'
 union all 
select
'01/01/2014',40,'Item2' union all
select 
'01/01/2014',30,'Item3' union all
select 
'01/01/2014',20,'Item4' union all 
select 
'01/01/2014',10,'Item4' union all
select
'08/01/2014',50,'Item1' union all
select 
'08/01/2014',40,'Item2' union all
select 
'08/01/2014',30,'Item3' union all 
select
'08/01/2014',40,'Item4' union all
select 
'08/01/2014',10,'Item4' 

;with cte as 
(
 select DENSE_RANK() OVER(partition by  
 datepart(day, datediff(day, 0, [month])/7 * 7)/7 + 1 ORDER BY DatePart(wk, [month])) AS RN,* from @tab
),
 CTE2 AS
(
select *,ROW_NUMBER()OVER(PARTITION BY RN ORDER BY (SELECT NULL))R from cte
)

Select [month],CustomerCode,ITEM from CTE2
WHERE R < 4

Upvotes: 1

Mureinik
Mureinik

Reputation: 311808

You can use datepart to extract the week from the date, and then use rank to get the top five for each week.

You did not provide column names, so I'm going to call them item_date, item_score and item_name:

SELECT item_date, item_score, item_name
FROM   (SELECT *, 
               RANK() OVER (PARTITION BY DATEPART(WEEK, item_date) 
                            ORDER BY item_score DESC) AS rk
        FROM   my_table) t
WHERE  rk <= 5

Note: "top 5" is a bit ambiguous. My solution finds the items with the top five scores each week, regardless of the number of items. If you want strictly no more than five items, you'll have to find another ordering to handle items with duplicate scores (e.g., what would you do if there are six items with the top score on one week?). In any event, in such a case, you should use row_number instead of rank.

Upvotes: 3

Related Questions