Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to model a sales rebate program by varying the Rebate, OrderOver and Year parameters.
 
-- SQL calculate sales rebate - SQL inner join - group by rollup
USE AdventureWorks;
 
DECLARE  @Rebate    INT,
         @OrderOver INT,
         @Year      INT
SET @Rebate = 100
SET @OrderOver = 1000
SET @Year = 2003
SELECT   Store = isnull(s.Name,'Total'),
         Rebate = @Rebate * Count(* )
FROM     Sales.Customer c
         INNER JOIN Sales.SalesOrderHeader O
           ON c.CustomerID = O.CustomerID
         INNER JOIN Sales.Store s
           ON c.CustomerID = s.CustomerID
WHERE    year(OrderDate) = @Year
GROUP BY s.Name,
         c.CustomerID WITH ROLLUP
HAVING   @OrderOver <=  ALL (SELECT   sum(SubTotal)
                             FROM     Sales.SalesOrderHeader oh
                                      INNER JOIN Sales.SalesOrderDetail od
                                        ON oh.SalesOrderID = od.SalesOrderID
                             WHERE    CustomerID = c.CustomerId
                                      AND year(OrderDate) = @Year
                             GROUP BY oh.SalesOrderId)
ORDER BY s.Name
GO
/* Partial results
 
Store                               Rebate
Total                               152400
A Great Bicycle Company             200
Accessories Network                 200
Acclaimed Bicycle Company           200
Acclaimed Bicycle Company           200

*/

------------