Stored Procedures for Automatic Sales Commission Calculation
Creates stored procedures that automate commission calculations with complex tiered rules and bonuses.
Develop SQL stored procedures that calculate sales commissions considering tiered quotas, product mix bonuses, cancellation clawbacks, and team splits.
At a glance
Access
Free prompt
Open to copy without upgrading.
Prompt objective
Develop SQL stored procedures that calculate sales commissions considering tiered quotas, product mix bonuses, cancellation clawbacks, and team splits.
Real use case
Insurance company Proteção Total has 200 agents with 5 different commission tiers, quarterly product mix bonuses, and clawback rules for cancellations within the first 90 days — manual calculation takes 5 days and frequently contains errors.
Customize these fields first
Replace the placeholders with your own context before you run the prompt. That usually improves the first output more than adding more instructions later.
Prompt
Develop stored procedures to automate sales commission calculations for [COMPANY NAME], with [NUMBER] salespeople and the following rules:\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Commission rules:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n1. Tiered commission by quota attainment:\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n - Up to 80% of quota: [X%] commission\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n - 80-100%: [Y%]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n - 100-120%: [Z%] (accelerator)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n - Above 120%: [W%] (super accelerator)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n2. Product mix bonus: salesperson who meets quota in [NUMBER] categories receives +[X%]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n3. Clawback: cancellation within [DAYS] days reverses [X%] of commission\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n4. Split: sales with 2 salespeople split [X/Y]% according to role (hunter/farmer)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Procedure 1 — sp_calculate_monthly_commission:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Input: @month, @year\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Logic: for each salesperson, calculate net sales, apply tiers, add bonuses\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Output: table with salesperson_id, gross_sales, cancellations, net_sales, % attainment, tier, base_commission, mix_bonus, clawback, final_commission\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Exception handling and execution logging\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Procedure 2 — sp_simulate_commission:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Input: @salesperson_id, @additional_sale_amount\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Output: commission projection if they close one more sale (useful for motivation)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Shows how much is needed to reach the next tier\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Procedure 3 — sp_commission_report:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Consolidated report by team and region\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Salesperson ranking\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Month-over-month comparison\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Total commission expense projection\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Extras:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Audit table (who ran it, when, calculated values)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Rounding function compliant with Brazilian labor regulations\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Job/Event for automatic execution on day [DAY] of each month\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nDialect: [PostgreSQL/MySQL]. Complete, commented code ready for production.
Open directly in an AI — the text is pre-filled:
How to use this prompt
- 1Replace the key placeholders first: COMPANY NAME, NUMBER, X%, Y%.
- 2Replace any bracketed placeholders like [this] with your own context.
- 3Add extra background information when you want more tailored results.
- 4Combine multiple prompts in one conversation when you need a richer output.
- 5Save your best-performing prompts so they are easy to reuse later.
Next best step
Open the guide first, then branch only if you still need more.
A guide for technical builders choosing between prompts, coding workflows, and agent-based implementation.
If this prompt is close but not quite right, generate variants next. If the job is recurring, move into the course library after the guide.
Related prompts
View allSales analysis queries with CTEs and window functions
Writes advanced SQL queries using CTEs and window functions for sales performance analysis.
Best for
Create a set of reusable SQL queries that analyze sales by period, product, seller, and region using CTEs (Common Table Expressions) and window functions for rankings and comparisons.
Database Schema Design for Recurring Subscription Systems
Designs an optimized database schema for a subscription business with SaaS metrics.
Best for
Create a complete relational model for a recurring subscription system, including tables, indexes, constraints, and ready-to-use queries for calculating MRR, churn, and LTV.
Slow Query Optimization with EXPLAIN ANALYZE and Indexes
Diagnoses and optimizes slow SQL queries using execution plans, strategic indexes, and query rewriting.
Best for
Learn to identify SQL query performance bottlenecks using EXPLAIN ANALYZE, create efficient indexes, and rewrite queries to reduce response time.
BigQuery Queries for E-commerce Conversion Funnel and Attribution Analysis
BigQuery queries to analyze conversion funnels, channel attribution, and browsing behavior in e-commerce.
Best for
Build conversion funnel analysis queries in BigQuery (Google Analytics 4 export) to identify drop-off points, optimize conversion rates, and attribute revenue to acquisition channels.
Explore other prompt categories
Move sideways into adjacent libraries when the current category is not the full answer.
Free browsing stays open. Premium prompts unlock the reusable workflow layer.
Use the guides and role paths to validate the job first. Upgrade when you want the full prompt text, editable premium prompts, and the surrounding course paths in one place.
Free access
- Browse guides, role paths, and category pages.
- Preview prompts before you decide to upgrade.
- Find the right starting point without friction.
Membership access
- Unlock premium prompts and the full copy text.
- See more workflow paths and course connections.
- Keep the reusable templates in one place.
