sql server 2012 - ranking orders by year, month, supplier and location

Question

I have a table containing the following.

pickup_date, Supplier_id, Location

pickup_date is a datetime, and supplier id is a number. Location is a string.

I would like to output a list ordered using the two datepart fields below (to match other data) giving a result per month for all suppliers. The last column should show the most frequent location for each supplier.

I believe where I am going wrong is the ordering of the group_by

datepart(year, b.pickup_date) Year,
datepart(month, b.pickup_date) Month,

I am Expecting: Year, Month, Supplier_Id, "MostfrequentPickupLocation".

Can you please update the post to include your expected result set? — Kane, Nov 11 '13 at 08:43
Apologies: I am Expecting: Year, Month, Supplier_Id, "MostfrequentPickupLocation" (didnt know how to edit original question) — finngeraghty, Nov 11 '13 at 08:47

score 1 · Accepted Answer · answered Nov 11 '13 at 08:56

1

Try this

SELECT 
  YEAR(pickup_date) AS 'PickupYear', 
  MONTH(pickup_date) AS 'PickupMonth', 
  Supplier_id,
  Location,
  COUNT(*)
FROM MyTable
GROUP BY   
  YEAR(pickup_date), 
  MONTH(pickup_date), 
  Supplier_id,
  Location

Here's the SQL Fiddle that I used: http://sqlfiddle.com/#!6/d9751/6

answered Nov 11 '13 at 08:56

Kane

16,471
11
61
86

@finngeraghty no problems, you can accept the answer if you found it helpful :) use the tick icon – Kane Nov 11 '13 at 09:15

score 0 · Answer 2 · answered Nov 11 '13 at 09:16

In case you want to show only one record per supplier:

WITH cte AS
(
  SELECT 
    YEAR(pickup_date) AS 'Year', 
    MONTH(pickup_date) AS 'Month', 
    Supplier_id,
    Location,
    COUNT(*) as NoAppeareances,
    ROW_NUMBER() OVER (PARTITION BY Supplier_id ORDER BY COUNT(*) DESC) RowNumber 
FROM MyTable
GROUP BY   
  YEAR(pickup_date), 
  MONTH(pickup_date), 
  Supplier_id,
  Location
)
SELECT Year, Month, Supplier_id, NoAppeareances as MostfrequentPickupLocation
FROM cte
WHERE RowNumber = 1

sql server 2012 - ranking orders by year, month, supplier and location

2 Answers2