SELECT SOH.SalesOrderNumber, S.Name AS Store, SOH.OrderDate, C.FirstName AS SalesFirstName, C.LastName AS SalesLastName, E.JobTitle AS SalesTitle,
SOH.PurchaseOrderNumber, SM.Name AS ShipMethod, BA.AddressLine1 AS BillAddress1, BA.AddressLine2 AS BillAddress2, BA.City AS BillCity,
BA.PostalCode AS BillPostalCode, BSP.Name AS BillStateProvince, BCR.Name AS BillCountryRegion, SA.AddressLine1 AS ShipAddress1,
SA.AddressLine2 AS ShipAddress2, SA.City AS ShipCity, SA.PostalCode AS ShipPostalCode, SSP.Name AS ShipStateProvince, SCR.Name AS ShipCountryRegion,
PPP.PhoneNumber AS CustPhone, CC.FirstName AS CustFirstName, CC.LastName AS CustLastName
FROM Person.Address AS SA INNER JOIN
Person.StateProvince AS SSP ON SA.StateProvinceID = SSP.StateProvinceID INNER JOIN
Person.CountryRegion AS SCR ON SSP.CountryRegionCode = SCR.CountryRegionCode RIGHT OUTER JOIN
Sales.SalesOrderHeader AS SOH LEFT OUTER JOIN
Sales.Customer AS SC ON SOH.CustomerID = SC.CustomerID LEFT OUTER JOIN
Person.Person AS CC ON SC.PersonID = CC.BusinessEntityID INNER JOIN
Person.PersonPhone AS PPP ON CC.BusinessEntityID = PPP.BusinessEntityID LEFT OUTER JOIN
Person.Address AS BA INNER JOIN
Person.StateProvince AS BSP ON BA.StateProvinceID = BSP.StateProvinceID INNER JOIN
Person.CountryRegion AS BCR ON BSP.CountryRegionCode = BCR.CountryRegionCode ON SOH.BillToAddressID = BA.AddressID ON
SA.AddressID = SOH.ShipToAddressID LEFT OUTER JOIN
Person.Person AS C RIGHT OUTER JOIN
HumanResources.Employee AS E ON C.BusinessEntityID = E.BusinessEntityID ON SOH.SalesPersonID = E.BusinessEntityID LEFT OUTER JOIN
Purchasing.ShipMethod AS SM ON SOH.ShipMethodID = SM.ShipMethodID LEFT OUTER JOIN
Sales.Store AS S ON SOH.SalesPersonID = S.BusinessEntityID
WHERE (SOH.SalesOrderNumber = @SalesOrderNumber) |