SELECT SOH.SalesOrderNumber, S.BusinessEntityID, S.Name,
SOH.SalesOrderID, SOH.SalesPersonID, SOH.TotalDue,
SOH.OrderDate, SOH.PurchaseOrderNumber,
SOH.BillToAddressID, SOH.ShipToAddressID, SOH.ShipMethodID,
SM.Name AS ShipMethod, BA.AddressLine1 AS BillAddress1, BA.City AS BillCity,
BA.PostalCode AS BillPostalCode, BSP.Name AS BillStateProvince,
BCR.Name AS BillCountryRegion,
SA.AddressLine1 AS ShipAddress1,SA.City AS ShipCity, SA.PostalCode AS ShipPostalCode,
SSP.Name AS ShipStateProvince, SCR.Name AS ShipCountryRegion,
e.JobTitle, per.[FirstName] + N' ' + per.[LastName] AS [SalesPerson],
ph.PhoneNumber,
SD.SalesOrderDetailID, SD.OrderQty, SD.UnitPrice,
CASE WHEN SD.UnitPriceDiscount IS NULL THEN 0 ELSE SD.UnitPriceDiscount END
AS UnitPriceDiscount,
SD.LineTotal, SD.CarrierTrackingNumber, P.Name as ProductName, P.ProductNumber
FROM [Sales].[SalesOrderHeader] SOH
INNER JOIN Sales.Customer C ON SOH.CustomerID = C.CustomerID
INNER JOIN Sales.Store S ON C.StoreID = S.BusinessEntityID
INNER JOIN Person.Address SA ON SA.AddressID = SOH.ShipToAddressID -- Shipping address
INNER JOIN Person.StateProvince SSP ON SA.StateProvinceID = SSP.StateProvinceID
INNER JOIN Person.CountryRegion SCR ON SSP.CountryRegionCode = SCR.CountryRegionCode
INNER JOIN Person.Address BA ON SOH.BillToAddressID = BA.AddressID -- Billing Address
INNER JOIN Person.StateProvince BSP ON BA.StateProvinceID = BSP.StateProvinceID
INNER JOIN Person.CountryRegion BCR ON BSP.CountryRegionCode = BCR.CountryRegionCode
INNER JOIN Purchasing.ShipMethod SM ON SOH.ShipMethodID = SM.ShipMethodID
INNER JOIN [Sales].[SalesPerson] sp ON sp.[BusinessEntityID] = SOH.[SalesPersonID]
INNER JOIN [HumanResources].[Employee] e ON SOH.[SalesPersonID] = e.[BusinessEntityID]
INNER JOIN [Person].[Person] per ON per.[BusinessEntityID] = sp.[BusinessEntityID]
INNER JOIN Person.PersonPhone ph ON per.[BusinessEntityID] = ph.[BusinessEntityID]
INNER JOIN Sales.SalesOrderDetail SD ON SD.SalesOrderID = SOH.SalesOrderID
INNER JOIN Production.Product P ON SD.ProductID = P.ProductID
WHERE (SOH.SalesOrderID BETWEEN (@SalesOrderIDStart) AND (@SalesOrderIDEnd))