September 21, 2012 at 2:25 pm
Hi guys,
I'm working on this query and wonder how could I pull off only biggest sales per corp_address_id. This is my query:
DECLARE @EndDate datetime
DECLARE @StartDate datetime
SET @EndDate = GETDATE()
SET @StartDate = DATEADD(month, -8, @EndDate)
;WITH order_history AS (
SELECT
a.corp_address_id
,a.name
,s.invoice_date
,s.invoice_no
,SUM(s.detail_price) AS sales
FROM
customer
INNER JOIN p21_view_address a ON a.id = customer.customer_id
LEFT OUTER JOIN p21_sales_history_report_view s ON s.customer_id = customer.customer_id
WHERE
s.year_for_period = 2012
AND s.parent_oe_line_uid = 0
AND (invoice_adjustment_type='C' AND s.source_type_cd = 2638 OR NOT (invoice_adjustment_type = 'C' OR invoice_adjustment_type = 'D'))
--AND [p21_sales_history_report_view].vendor_consigned = 'N' AND projected_order = 'N' AND (detail_type IS NULL OR detail_type = 0)
--AND (progress_bill_flag = 'N' OR progress_bill_flag IS NULL )
GROUP BY
a.corp_address_id
,a.name
,s.invoice_date
,s.invoice_no
)
SELECT
order_history.corp_address_id
,name
,order_history.invoice_no
,invoice_date
,sales
FROM order_history
ORDER BY 1
The data table looks like this
corp_address_id name invoice_no sales
10010 Wellservice 123 $5
10010 Wellservice 124 $8
10010 Wellservice 125 $2
I want to have this query edit so that it only show the biggest sales for that corp_id ($8). If you know how, please let me know. Thank you
September 21, 2012 at 2:44 pm
Using just your sample data (not the code you posted), is this what you are looking for?
WITH TestData (
corp_address_id,CompanyName,invoice_no,sales
) AS (
SELECT
corp_address_id,CompanyName,invoice_no,sales
FROM
(VALUES
(10010,'Wellservice',123,5),
(10010,'Wellservice',124,8),
(10010,'Wellservice',125,2))dt(corp_address_id,CompanyName,invoice_no,sales)
), BaseData AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY corp_address_id ORDER BY sales DESC) AS rn,
corp_address_id,
CompanyName,
invoice_no,
sales
FROM
TestData
)
SELECT
corp_address_id,
CompanyName,
invoice_no,
sales
FROM
BaseData
WHERE
rn = 1;
September 21, 2012 at 2:52 pm
Yes and thank you so much. That helps a lot...
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply