April 1, 2014 at 9:55 am
Good Morning
I would like for each of the queries to have all of the selected fields shown horizontally in one table. All of the tables share the "RequestId" field
For example,
Commercial Created | Commercial Closed | Commercial UserId | Residential Created | Residential Closed | Residential UserId | Other Created | Other Closed | Other UserId.
SELECT
T1.RequestId
,T1.Process
,T1.Entered AS 'Commercial Created'
,T1.Closed AS 'Commercial Closed'
,T3.UserId AS 'Commercial UserId'
FROM [Sales].[dbo].[vSalesProcessDetail] T1
LEFT JOIN [Sales].[dbo].[vSalesReport] T2
ON T1.RequestId = T2.RequestId
LEFT JOIN [Sales].[dbo].[vSalesUserId] T3
ON T1.RequestId = T3.RequestId
WHERE T1.Process = 'Commercial'
AND T2.RequestId = //Each "Process" share the RequestId
(SELECT
T1.RequestId
,T1.Process
,T1.Entered AS 'Residential Created'
,T1.Closed AS 'Residential Closed'
,T3.UserId AS 'Residential UserId'
FROM [Sales].[dbo].[vSalesProcessDetail] T1
LEFT JOIN [Sales].[dbo].[vSalesReport] T2
ON T1.RequestId = T2.RequestId
LEFT JOIN [Sales].[dbo].[vSalesUserId] T3
ON T1.RequestId = T3.RequestId
WHERE T1.Process = 'Residential')
AND T2.RequestId =
(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] T1
LEFT JOIN [Sales].[dbo].[vSalesReport] T2
ON T1.RequestId = T2.RequestId
LEFT JOIN [Sales].[dbo].[vSalesUserId] T3
ON T1.RequestId = T3.RequestId
WHERE T1.Process = 'Other')
Not sure where the edit the code. To trying various things for about 2 days now.
Thank you for your help in advance.
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply