How to get only the biggest sales for the data

  • 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

  • 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;

  • 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