Filtering Sales Data Dynamically Based on Customer ID and Sale Amount Using SQL

Setup Instructions

Ensure you have:

1. An SQL database setup (e.g., PostgreSQL, MySQL).
2. A sales table with columns customer_id and sale_amount.

SQL Queries

1. Create the Table

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    customer_id INT,
    sale_amount DECIMAL(10, 2)
);

2. Insert Sample Data

INSERT INTO sales (sale_id, customer_id, sale_amount) VALUES
(1, 101, 500.00),
(2, 102, 150.75),
(3, 101, 300.50),
(4, 103, 1200.00),
(5, 104, 85.00);

3. Query to Filter by Customer ID

SELECT * FROM sales
WHERE customer_id = 101;

4. Query to Filter by Sale Amount

SELECT * FROM sales
WHERE sale_amount > 100.00;

5. Combined Filter by Customer ID and Sale Amount

SELECT * FROM sales
WHERE customer_id = 101 AND sale_amount > 100.00;

Dynamic Filtering Example

Assume inputs for filtering:

-- Replace `@customerID` and `@saleAmount` with dynamic values
SET @customerID = 101;
SET @saleAmount = 100.00;

SELECT * FROM sales
WHERE customer_id = @customerID AND sale_amount > @saleAmount;

Apply these instructions directly to filter, create, and manage sales data using SQL.

Introduction to the CASE Statement

SQL Query Example

Here is a practical implementation using a CASE statement to filter sales data dynamically based on customer ID and sale amount.

SELECT
    customer_id,
    sale_amount,
    CASE
        WHEN customer_id = 1 AND sale_amount > 100 THEN 'High Value Customer'
        WHEN customer_id = 1 THEN 'Regular Customer'
        WHEN sale_amount > 100 THEN 'High Value Sale'
        ELSE 'Regular Sale'
    END AS sale_category
FROM
    sales
WHERE
    sale_amount > 50;

Explanation

• SELECT specifies the columns to fetch.
• CASE evaluates conditions and returns a category string.
• WHEN clauses define the conditions:
  • WHEN customer_id = 1 AND sale_amount > 100 THEN 'High Value Customer'
  • WHEN customer_id = 1 THEN 'Regular Customer'
  • WHEN sale_amount > 100 THEN 'High Value Sale'
  • ELSE 'Regular Sale' handles any cases not caught by the above conditions.
• FROM sales: the table to query.
• WHERE sale_amount > 50: filters out rows where sale_amount is 50 or less.

Practical Use

• Run this query in your SQL environment.
• It categorizes the sales records dynamically.
• Filter data based on specific conditions.

This implementation is practical and ready to use in real-life scenarios requiring dynamic sales data categorization.

Filtering Sales Data Dynamically Using SQL

1. Basic Query

Use the following SQL query to filter sales data based on customer ID and sale amount. The WHERE clause contains conditions to filter results conditionally.

SQL Code

SELECT
    sale_id,
    customer_id,
    sale_amount
FROM
    sales
WHERE
    customer_id = @customer_id
    AND sale_amount >= @minimum_sale_amount;

2. Using CASE for Dynamic Logic

To add more conditional logic dynamically in your query, you can use the CASE statement within the WHERE clause to handle more complex conditions.

SQL Code

SELECT
    sale_id,
    customer_id,
    sale_amount
FROM
    sales
WHERE
    customer_id = @customer_id
    AND (
        CASE
            WHEN @use_minimum_amount = 1 THEN sale_amount >= @minimum_sale_amount
            ELSE 1=1
        END
    );

3. Combining Multiple Conditions

If you need to filter based on multiple conditional parameters, the query could be extended as shown below.

SQL Code

SELECT
    sale_id,
    customer_id,
    sale_amount
FROM
    sales
WHERE
    customer_id = @customer_id
    AND (
        CASE
            WHEN @use_minimum_amount = 1 THEN sale_amount >= @minimum_sale_amount
            ELSE 1 = 1
        END
    )
    AND (
        CASE
            WHEN @use_maximum_amount = 1 THEN sale_amount <= @maximum_sale_amount
            ELSE 1 = 1
        END
    );

4. Full Dynamic Filtering Example

A full, realistic example to filter sales data dynamically taking multiple conditions such as customer ID, minimum sale amount and maximum sale amount into account.

SQL Code

SELECT
    sale_id,
    customer_id,
    sale_amount
FROM
    sales
WHERE
    customer_id = @customer_id
    AND (
        CASE
            WHEN @filter_minimum_amount = 1 THEN sale_amount >= @minimum_sale_amount
            ELSE 1 = 1
        END
    )
    AND (
        CASE
            WHEN @filter_maximum_amount = 1 THEN sale_amount <= @maximum_sale_amount
            ELSE 1 = 1
        END
    )
    AND (
        CASE
            WHEN @filter_by_date = 1 THEN sale_date >= @start_date AND sale_date <= @end_date
            ELSE 1 = 1
        END
    );

By setting the parameters (@customer_id, @minimum_sale_amount, @maximum_sale_amount, @start_date, @end_date, etc.), you can dynamically control the data filtering based on your requirements.

Combining Conditions for Dynamic Filtering in SQL

SELECT
    customer_id,
    sale_amount,
    product_id,
    sale_date
FROM
    sales
WHERE
    (customer_id = @customer_id OR @customer_id IS NULL)
    AND
    (sale_amount >= @min_sale_amount OR @min_sale_amount IS NULL)
    AND
    (sale_amount <= @max_sale_amount OR @max_sale_amount IS NULL);

• @customer_id, @min_sale_amount, @max_sale_amount are placeholders for dynamic filtering criteria.
• The OR @parameter IS NULL allows the filter to be ignored if the parameter is not provided.

Use this template to dynamically filter sales data based on the conditions you need.