February 13, 2015 at 7:47 am
I have an invoice table with customer, date, sales location, and invoice total. I want to total the invoices by location by customer and see what location sells the most to each customer. My table looks like this.
CustDate LocAmt
A1/1/2014 1520
A1/1/2014 1560
A1/1/2014 2575
A1/1/2014 3580
B1/30/2014 15100
B1/30/2014 15200
B1/30/2014 2575
B1/30/2014 25150
B1/30/2014 35250
I want to get this, because for customer A, location 35 had the largest SUM of sales and for customer B, location 15 had the largest SUM.
CustLoc
A35
B15
If I have to use CTEs to get there, I can do that too. Just a little lost on how to get only the one location per customer.
February 13, 2015 at 8:37 am
jcobb 20350 (2/13/2015)
I have an invoice table with customer, date, sales location, and invoice total. I want to total the invoices by location by customer and see what location sells the most to each customer. My table looks like this.CustDate LocAmt
A1/1/2014 1520
A1/1/2014 1560
A1/1/2014 2575
A1/1/2014 3580
B1/30/2014 15100
B1/30/2014 15200
B1/30/2014 2575
B1/30/2014 25150
B1/30/2014 35250
I want to get this, because for customer A, location 35 had the largest SUM of sales and for customer B, location 15 had the largest SUM.
CustLoc
A35
B15
If I have to use CTEs to get there, I can do that too. Just a little lost on how to get only the one location per customer.
Hi and welcome to the forums. I put your data together into something consumable. This is something you should do in the future. Not quite sure how you want your output here. For Cust A both Loc 15 and 35 have a sum of 80. Which one do you want? Or do you need both of them? Here is some code that is close but not exact due to the uncertainty of the desired output.
with Something(Cust, MyDate, Loc, Amt)
as
(
select 'A', CAST('1/1/2014' as DATE), 15, 20 union all
select 'A', '1/1/2014', 15, 60 union all
select 'A', '1/1/2014', 25, 75 union all
select 'A', '1/1/2014', 35, 80 union all
select 'B', '1/30/2014', 15, 100 union all
select 'B', '1/30/2014', 15, 200 union all
select 'B', '1/30/2014', 25, 75 union all
select 'B', '1/30/2014', 25, 150 union all
select 'B', '1/30/2014', 35, 250
)
, SortedValues as
(
select Cust
, Loc
, sum(Amt) as Amt
, ROW_NUMBER() over (PARTITION by Cust order by sum(Amt) desc) as RowNum
from Something
group by Cust, Loc
)
select Cust
, Loc
, *
from SortedValues
--where RowNum = 1
order by SortedValues.Cust
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 13, 2015 at 12:41 pm
Fantastic. I need to read up and understand the PARTITION BY a little more, but this did exactly what I needed. This is a snippet of what will be in the final query. Thanks so much.
; WITH Sales AS
(SELECT h.customer_id AS 'Cust'
, h.sales_location_id AS 'Loc'
, SUM(h.total_amount) AS 'Total'
, ROW_NUMBER() OVER (PARTITION BY h.customer_id ORDER BY SUM(h.total_amount) DESC) AS RowNum
FROM invoice_hdr h
WHERE h.invoice_date BETWEEN '1-1-15' AND '1-31-15'
AND h.customer_id IN (10720006, 10008601)
GROUP BY h.sales_location_id
, h.customer_id)
SELECT Sales.Cust
, Sales.Loc
FROM Sales
WHERE Sales.RowNum = 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply