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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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