April 1, 2014 at 12:20 pm
Without DDL and sample data it makes it difficult to give a good answer. (see link in my signature)
based on what you have provided you could create a CTE for each type (commercial, residential, and other) and then join the CTEs.
this should get you started.
WITH cte_commercial
AS (SELECT T1.RequestId,
T1.Process,
T1.Entered AS 'Commercial Created',
T1.Closed AS 'Commercial Closed',
T3.UserId AS 'Commercial UserId'
FROM Sales.dbo.vSalesProcessDetail AS T1
LEFT JOIN Sales.dbo.vSalesReport AS T2
ON T1.RequestId = T2.RequestId
LEFT JOIN Sales.dbo.vSalesUserId AS T3
ON T1.RequestId = T3.RequestId
WHERE T1.Process = 'Commercial'), cte_residential
AS (SELECT T1.RequestId,
T1.Process,
T1.Entered AS 'Residential Created',
T1.Closed AS 'Residential Closed',
T3.UserId AS 'Residential UserId'
FROM Sales.dbo.vSalesProcessDetail AS T1
LEFT JOIN Sales.dbo.vSalesReport AS T2
ON T1.RequestId = T2.RequestId
LEFT JOIN Sales.dbo.vSalesUserId AS T3
ON T1.RequestId = T3.RequestId
WHERE T1.Process = 'Residential'), cte_other
AS (SELECT TOP 1000 T1.RequestId,
T1.Process,
T1.Entered AS 'Other Created',
T1.Closed AS 'Other Closed',
T3.UserId AS 'Other UserId'
FROM Sales.dbo.vSalesProcessDetail AS T1
LEFT JOIN Sales.dbo.vSalesReport AS T2
ON T1.RequestId = T2.RequestId
LEFT JOIN Sales.dbo.vSalesUserId AS T3
ON T1.RequestId = T3.RequestId
WHERE T1.Process = 'Other')
SELECT *
FROM cte_commercial AS a
FULL OUTER JOIN cte_residential AS b
ON a.RequestId = b.RequestId
FULL OUTER JOIN cte_other AS c
ON a.RequestId = c.RequestId;
Viewing post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply