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 find customers to call about missing part?

Execute the following SQL Server T-SQL script in Microsoft Management Studio Query Editor to find the customers and assigned sales person:

USE AdventureWorks;

 

WITH cteMissingPart(SalesPerson,OrderAmount,SalesPersonID,CustomerID)

     AS (SELECT   C.LastName + ', ' + C.FirstName,

                  CONVERT(MONEY,SUM(SD.UnitPrice * SD.OrderQty)),

                  SH.SalesPersonID,

                  SH.CustomerID

         FROM     Person.Contact C

                  INNER JOIN HumanResources.Employee E

                    ON E.ContactID = C.ContactID

                  INNER JOIN Sales.SalesOrderHeader SH

                    ON SH.SalesPersonID = E.EmployeeID

                  INNER JOIN Sales.SalesOrderDetail SD

                    ON SH.SalesOrderId = SD.SalesOrderId

                  INNER JOIN Production.Product PP

                    ON SD.ProductID = PP.ProductID

                       AND PP.Name LIKE 'HL Bottom%'

         GROUP BY C.LastName + ', ' + C.FirstName,

                  SH.SalesPersonID,

                  SH.CustomerID)

SELECT 'No Action'                      AS ACTION,

       CONVERT(VARCHAR,S.SalesPersonID) AS SalesPerson,

       S.SalesQuota,

       CONVERT(MONEY,0)                 AS OrderAmount,

       0                                AS CustomerID

FROM   Sales.SalesPerson S

WHERE  S.SalesPersonID NOT IN (SELECT SalesPersonID

                               FROM   cteMissingPart)

UNION

SELECT 'Call Customer' AS ACTION,

       MP.SalesPerson,

       S.SalesQuota,

       MP.OrderAmount,

       MP.CustomerID

FROM   cteMissingPart MP

       INNER JOIN Sales.SalesPerson S

         ON MP.SalesPersonID = S.SalesPersonID;

/*

ACTION            SalesPerson       SalesQuota  OrderAmount CustomerID

.....

Call Customer     Tsoflias, Lynn    250000.00   145.788     267

Call Customer     Tsoflias, Lynn    250000.00   218.682     123

Call Customer     Tsoflias, Lynn    250000.00   218.682     213

Call Customer     Tsoflias, Lynn    250000.00   218.682     249

Call Customer     Tsoflias, Lynn    250000.00   291.576     15

Call Customer     Tsoflias, Lynn    250000.00   291.576     688

Call Customer     Tsoflias, Lynn    250000.00   364.47      682

.....

*/

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