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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use SQL INNER JOIN in complex queries?

Execute the following SQL Server Transact-SQL script in Management Studio Query Editor to demonstrate the architecture of complex stored procedure with multiple INNER JOINs in the main query.

The sproc can be applied as data source to generate a monthly sales detail report in Microsoft SQL Server 2005/2008 Reporting Services or in other BI Delivery Software such as IBM/COGNOS ReportNet.

-- SQL inner join - SQL Server join - SQL multiple inner joins - SQL cte join

USE AdventureWorks

GO

-- Create a stored procedure with two input parameters & several INNER JOINs

CREATE PROCEDURE uspSalesRegisterByMonth

                @Year  INT,

                @Month INT

AS

  BEGIN

-- Build a Common Table Expression(CTE) to summarize items sold for each sales order

    WITH cteItemsSold

         AS (SELECT   SalesOrderID,

                      ItemsSold = sum(OrderQty)

             FROM     Sales.SalesOrderDetail

             GROUP BY SalesOrderID)

    -- Main SELECT query with 12 INNER JOIN-s

    SELECT SOH.SalesOrderNumber               AS SON,

           SOH.PurchaseOrderNumber            AS PO,

           S.Name                             AS Store,

           convert(VARCHAR,SOH.OrderDate,110) AS OrderDate,

           convert(VARCHAR,SOH.ShipDate,110)  AS ShipDate,

           '$' + convert(VARCHAR,TotalDue,1)  AS [Total Due],

           ItemsSold,

           C.FirstName + ' ' + C.LastName     AS SalesStaff,

           SM.Name                            AS ShippingMethod,

           BA.AddressLine1                    AS BillingAddress1,

           isnull(BA.AddressLine2,'')         AS BillingAddress2,

           BA.City                            AS BillingCity,

           BSP.Name                           AS BillingStateProvince,

           BA.PostalCode                      AS BillingPostalCode,

           BCR.Name                           AS BillingCountryRegion,

           SA.AddressLine1                    AS ShippingAddress1,

           isnull(SA.AddressLine2,'')         AS ShippingAddress2,

           SA.City                            AS ShippingCity,

           SSP.Name                           AS ShippingStateProvince,

           SA.PostalCode                      AS ShippingPostalCode,

           SCR.Name                           AS ShippingCountryRegion,

           CC.FirstName + ' ' + CC.LastName   AS CustomerContact,

           CC.Phone                           AS CustomerPhone,

           SOH.AccountNumber

    -- The INNER JOINS are required to get all the detail data gathered

    FROM Person.Address SA

             INNER JOIN Person.StateProvince SSP

               ON SA.StateProvinceID = SSP.StateProvinceID

             INNER JOIN Person.CountryRegion SCR

               ON SSP.CountryRegionCode = SCR.CountryRegionCode

             INNER JOIN Sales.SalesOrderHeader SOH

                        INNER JOIN Person.Contact CC

                          ON SOH.ContactID = CC.ContactID

                        -- INNER JOIN the CTE

                        INNER JOIN cteItemsSold cte

                          ON cte.SalesOrderID = SOH.SalesOrderID

                        INNER JOIN Person.Address BA

                                   INNER JOIN Person.StateProvince BSP

                                     ON BA.StateProvinceID = BSP.StateProvinceID

                                   INNER JOIN Person.CountryRegion BCR

                                     ON BSP.CountryRegionCode = BCR.CountryRegionCode

                          ON SOH.BillToAddressID = BA.AddressID

               ON SA.AddressID = SOH.ShipToAddressID

             INNER JOIN Person.Contact C

                        INNER JOIN HumanResources.Employee E

                          ON C.ContactID = E.ContactID

               ON SOH.SalesPersonID = E.EmployeeID

             INNER JOIN Purchasing.ShipMethod SM

               ON SOH.ShipMethodID = SM.ShipMethodID

             INNER JOIN Sales.Store S

               ON SOH.CustomerID = S.CustomerID

    WHERE    Year(SOH.OrderDate) = @Year

             AND Month(SOH.OrderDate) = @Month

    ORDER BY Store,

             OrderDate DESC

  END

GO

 

-- Test the stored procedure with the INNER JOIN-s

-- Execute stored procedure

EXEC uspSalesRegisterByMonth    2004 ,    3

GO

/* Partial results

 

SON         PO          Store                                     OrderDate

SO65259     PO9251144255      Acclaimed Bicycle Company           03-01-2004

SO65158     PO19285194632     Action Bicycle Specialists          03-01-2004

SO65310     PO6786169596      Advanced Bike Components            03-01-2004

SO65287     PO2349129070      Aerobic Exercise Company            03-01-2004

SO65234     PO10411160249     Another Bicycle Company             03-01-2004

SO65223     PO15805168198     Beneficial Exercises and Activities 03-01-2004

SO65305     PO5481143425      Bicycle Warehouse Inc.              03-01-2004

*/

 

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