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 all orders with 10% discount?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor or Query Analyzer to get a report on all orders with 10% (0.1) discount. The queries demonstrate INNER JOIN GROUP BY with derived table and CTE forms.

-- SQL INNER JOIN with SELECT GROUP BY query - derived table

-- SQL sequence numbering groups

USE Northwind

 

SELECT   o.OrderID,

         SeqNo,

         ProductName,

         o.UnitPrice,

         o.Quantity,

         Amount = o.UnitPrice * o.Quantity,

         Discount = convert(DECIMAL(3,2),Discount)

FROM     Products p

         INNER JOIN [Order Details] o

           ON p.ProductID = o.ProductID

         INNER JOIN (SELECT   count(* ) AS SeqNo,

                        a.OrderID,

                        a.ProductID

               FROM     [Order Details] a

                        JOIN [Order Details] b

                          ON a.ProductID >= b.ProductID

                             AND a.OrderID = b.OrderID

               GROUP BY a.OrderID,

                        a.ProductID) seq

           ON o.OrderID = seq.OrderID

              AND o.ProductID = seq.ProductID

WHERE    o.Discount = 0.1

ORDER BY o.OrderID

GO

/* Partial results

 

OrderID     SeqNo ProductName                         UnitPrice

10248       1     Queso Cabrales                      14.00

10248       2     Singaporean Hokkien Fried Mee       9.80

10248       3     Mozzarella di Giovanni              34.80

10249       1     Tofu                                18.60

10249       2     Manjimup Dried Apples               42.40

10250       1     Jack's New England Clam Chowder     7.70

10250       2     Manjimup Dried Apples               42.40

 

*/

-- SQL INNER JOIN with SELECT GROUP BY query - CTE

 

;WITH CTE AS

(SELECT   count(* ) AS SeqNo,

                        a.OrderID,

                        a.ProductID

               FROM     [Order Details] a

                        JOIN [Order Details] b

                          ON a.ProductID >= b.ProductID

                             AND a.OrderID = b.OrderID

               GROUP BY a.OrderID,

                        a.ProductID) 

SELECT   o.OrderID,

         SeqNo,

         ProductName,

         o.UnitPrice,

         o.Quantity,

         Amount = o.UnitPrice * o.Quantity,

         Discount = convert(DECIMAL(3,2),Discount)

FROM     Products p

         INNER JOIN [Order Details] o

           ON p.ProductID = o.ProductID

         INNER JOIN CTE

           ON o.OrderID = CTE.OrderID

              AND o.ProductID = CTE.ProductID

WHERE    o.Discount = 0.1

ORDER BY o.OrderID

GO

 

 

 
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