none
SQL Loop to Exclude Records

    Question

  • I need help with automating a process in SQL Server.  I want to use a loop to exclude certain records.

    1.        Calculate the impact  of each record to the “Average Selling Price” in the attached excel file.
      1.        For example, If you were to exclude the Aguirre purchase of SKU A in the US, what would be the “Average Selling Price” for A in the US?
      2.       Additionally, after itinerating over the 4 transaction for SKU B in the US, what is the ranking of each transaction regarding the impact to the ASP?

    country sku customer units sales unit price Average Selling Price
    us a aguirre 10 100 10 15.55555556
    us a wirt 100 2000 20
    us a emrich 3 50 16.66667
    us b aguirre 34 100 2.941176 9.190627744
    us b wirt 67 2000 29.85075
    us b emrich 34 50 1.470588
    us b jory 20 50 2.5

    

     
    Thursday, June 20, 2013 10:46 PM

Answers

  • You dont need to loop to exclude some records, mention the inequality in WHERE clause and try group by to get AVG.

    Post sample DDL, data and expected output for better understanding.


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, June 21, 2013 12:04 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. If you had been polite and knew RDBMS, perhaps this is what you would have posted?

    CREATE TABLE Sales

    (country_code CHAR(2) NOT NULL,

    sku CHAR(1) NOT NULL,

    customer_name VARCHAR(15) NOT NULL,

    PRIMARY KEY (sku, customer_name), --- had to guess at it!

    sale_units INTEGER NOT NULL

    CHECK (sale_units > 0),

    unit_price DECIMAL (10,4) NOT NULL,

    CHECK (unit_price > 0.00));

    Notice that we do not materialize computations in the table. This is one of the many ways tables are not spreadsheets. Guessing at what you did not tell us, I think your data can be re-written to this. Commercial math is usually done to 4 decimal places and not with floating point.

    INSERT INTO Sales

    VALUES

    ('US', 'A', 'aguirre', 10, 10.0000),

    ('US', 'A', 'wirt', 100, 20.0000),

    ('US', 'A', 'emrich' , 3, 16.6666),

    ('US', 'B', 'aguirre', 34, 2.94117),

    ('US', 'B', 'emrich', 34, 1.47059),

    ('US', 'B', 'jory', 20, 2.50000);

    >> I need help with automating 'A', process in SQL Server. I want to use 'B', loop [sic] to exclude certain records [sic]. <<

    Rows are totally different from records. SQL is a declarative language so we do not use loops; we use sets.

    >> Calculate the impact of each record [sic] to the “Average Selling Price” in the attached Excel file. <<

    Tables are not files and we do not care about spreadsheets; this is an SQL Forum. First, write a query for the average sale price by SKU and country code:

    SELECT country_code, sku,

    SUM(sale_units * unit_price)/ SUM(sale_units)

    AS sale_price_avg

    FROM Sales

    GROUP BY country_code, sku;

    This gives us a simple basis

    US

    A

    19.03

    US

    B

    2.27

    >> For example, If you were to exclude the Aguirre purchase of SKU 'A' in the US, what would be the “Average Selling Price” for A in the US? <<

    Modify the first query a little. We can make the second SELECT * as complicated as we wish.

    SELECT country_code, sku,

    SUM(sale_units * unit_price)/ SUM(sale_units)

    AS sale_price_avg

    FROM (SELECT * FROM Sales

    EXCEPT

    SELECT * FROM Sales

    WHERE country_code = 'US'

    AND sku = 'A'

    AND customer_name = 'aguirre') AS X

    GROUP BY country_code, sku;

    US

    A

    19.9

    US

    B

    2.27

    Make those constants into parameters in a procedure and you are pretty much done. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, June 21, 2013 12:17 AM

All replies

  • You dont need to loop to exclude some records, mention the inequality in WHERE clause and try group by to get AVG.

    Post sample DDL, data and expected output for better understanding.


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, June 21, 2013 12:04 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. If you had been polite and knew RDBMS, perhaps this is what you would have posted?

    CREATE TABLE Sales

    (country_code CHAR(2) NOT NULL,

    sku CHAR(1) NOT NULL,

    customer_name VARCHAR(15) NOT NULL,

    PRIMARY KEY (sku, customer_name), --- had to guess at it!

    sale_units INTEGER NOT NULL

    CHECK (sale_units > 0),

    unit_price DECIMAL (10,4) NOT NULL,

    CHECK (unit_price > 0.00));

    Notice that we do not materialize computations in the table. This is one of the many ways tables are not spreadsheets. Guessing at what you did not tell us, I think your data can be re-written to this. Commercial math is usually done to 4 decimal places and not with floating point.

    INSERT INTO Sales

    VALUES

    ('US', 'A', 'aguirre', 10, 10.0000),

    ('US', 'A', 'wirt', 100, 20.0000),

    ('US', 'A', 'emrich' , 3, 16.6666),

    ('US', 'B', 'aguirre', 34, 2.94117),

    ('US', 'B', 'emrich', 34, 1.47059),

    ('US', 'B', 'jory', 20, 2.50000);

    >> I need help with automating 'A', process in SQL Server. I want to use 'B', loop [sic] to exclude certain records [sic]. <<

    Rows are totally different from records. SQL is a declarative language so we do not use loops; we use sets.

    >> Calculate the impact of each record [sic] to the “Average Selling Price” in the attached Excel file. <<

    Tables are not files and we do not care about spreadsheets; this is an SQL Forum. First, write a query for the average sale price by SKU and country code:

    SELECT country_code, sku,

    SUM(sale_units * unit_price)/ SUM(sale_units)

    AS sale_price_avg

    FROM Sales

    GROUP BY country_code, sku;

    This gives us a simple basis

    US

    A

    19.03

    US

    B

    2.27

    >> For example, If you were to exclude the Aguirre purchase of SKU 'A' in the US, what would be the “Average Selling Price” for A in the US? <<

    Modify the first query a little. We can make the second SELECT * as complicated as we wish.

    SELECT country_code, sku,

    SUM(sale_units * unit_price)/ SUM(sale_units)

    AS sale_price_avg

    FROM (SELECT * FROM Sales

    EXCEPT

    SELECT * FROM Sales

    WHERE country_code = 'US'

    AND sku = 'A'

    AND customer_name = 'aguirre') AS X

    GROUP BY country_code, sku;

    US

    A

    19.9

    US

    B

    2.27

    Make those constants into parameters in a procedure and you are pretty much done. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, June 21, 2013 12:17 AM