DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to model a sales rebate incentive program?

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

*/

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

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE