SQL Query Consolidate Rows Parts

Looking to consolidate the output rows from @MachineOut where company codes are all the same (cant select a max) and check another table for last detected company code

Last Detected Table Data

MACHINE1	USER2	03327-9715131
MACHINE2	USER1	03361-9724327

Open in new window


MachineGUID	Region	Machine	NBK	DevicePurpose	CompanyCode	CompanyCodeCnt	MailCode	Model	Status
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03017-9714083	2	NY1-100-05-00	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03017-9714159	1	IL4-540-28-01	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03327-9715129	1	473-808-02-71	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03327-9715131	2	473-808-02-71	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03455-1000611	2	737-807-39-01	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03455-1000636	2	737-807-39-01	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03455-9954551	1	737-801-18-01	Tier 1	IN USE
VMWARE-42 34 13 91 57 92 71 DB-DC 81 37 70 B9 5B 36 31	APAC	MACHINE2	USER1,USER2,USER3	Not Available	03017-9724709	3	839-000-00-00	Tier 1	IN USE
VMWARE-42 34 13 91 57 92 71 DB-DC 81 37 70 B9 5B 36 31	APAC	MACHINE2	USER1,USER2,USER3	Not Available	03361-9724327	3	839-000-00-00	Tier 1	IN USE
VMWARE-42 34 13 91 57 92 71 DB-DC 81 37 70 B9 5B 36 31	APAC	MACHINE2	USER1,USER2,USER3	Not Available	Not Available	1	Not Available	Tier 1	IN USE

Open in new window


Current Output from Query

Select mo1.MachineGUID, mo1.Region, mo1.Machine, mo1.NBK, mo1.DevicePurpose, mo1.CompanyCode, mo1.CompanyCodeCnt, mo1.MailCode, mo1.Model, mo1.[Status] from @MachineOut mo1
      WHERE CompanyCodeCnt > 1 AND CompanyCodeCnt IN (SELECT MAX(CompanyCodeCnt) FROM @MachineOut mo Where mo.Machine = mo1.Machine)
      GROUP BY MachineGUID, Region, Machine, NBK, DevicePurpose, CompanyCode, CompanyCodeCnt, MailCode, Model, [Status]
      HAVING SUM(LEN(mo1.NBK) - LEN(REPLACE(mo1.NBK,',','')) + 1) > 2

MachineGUID	Region	Machine	NBK	DevicePurpose	CompanyCode	CompanyCodeCnt	MailCode	Model	Status
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03017-9714083	2	NY1-100-05-00	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03327-9715131	2	473-808-02-71	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03455-1000611	2	737-807-39-01	Tier 1	IN USE
VMWARE-42 1D B7 57 69 9D 05 A8-FC 36 3D 8C FD 15 F1 40	APAC	MACHINE1	USER1,USER2,USER3	Not Available	03455-1000636	2	737-807-39-01	Tier 1	IN USE
VMWARE-42 34 13 91 57 92 71 DB-DC 81 37 70 B9 5B 36 31	APAC	MACHINE2	USER1,USER2,USER3	Not Available	03017-9724709	3	839-000-00-00	Tier 1	IN USE
VMWARE-42 34 13 91 57 92 71 DB-DC 81 37 70 B9 5B 36 31	APAC	MACHINE2	USER1,USER2,USER3	Not Available	03361-9724327	3	839-000-00-00	Tier 1	IN USE

Open in new window


Need Output like this

Region	Machine	NBK	DevicePurpose	CompanyCode	CompanyCodeCnt	MailCode	Model	Status
APAC	MACHINE1	USER1,USER2,USER3	Not Available	03327-9715131	2	473-808-02-71	Tier 1	IN USE
APAC	MACHINE2	USER1,USER2,USER3	Not Available	03361-9724327	3	839-000-00-00	Tier 1	IN USE

Open in new window

Thanks in advance

M
LVL 4
Mauro CazabonnetSenior .NET Software EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Mauro CazabonnetSenior .NET Software EngineerAuthor Commented:
Thought I share the end result
Query takes a few minutes to complete

DECLARE @count int, @i int, @currutcdate datetime

		DECLARE @LastLoggedOn TABLE 
		(
			Machine VARCHAR(100),
			NBK  VARCHAR(100),
			CompanyCode VARCHAR(100)
		)

		INSERT INTO @LastLoggedOn
		SELECT DISTINCT tcm.[Machine]      
			  ,tcw.[LastConnectedUser]
			  ,vhr.[CompanyCode]     
		  FROM [CitrixGEN2].[dbo].[tblCitrixWorkers] tcw WITH (NOLOCK)
		  INNER JOIN [CitrixGEN2].[dbo].[tblCitrixMachine] tcm WITH (NOLOCK) ON tcw.[MachineID] = tcm.MachineID
		  LEFT JOIN [HRData].[dbo].[vwHRData] vhr WITH (NOLOCK) ON SUBSTRING(tcw.[LastConnectedUser],CHARINDEX('\',tcw.[LastConnectedUser]) + 1,LEN(tcw.[LastConnectedUser])) = vhr.[NBK]
		  WHERE tcw.[LastConnectedUser] IS NOT NULL AND vhr.[CompanyCode] IS NOT NULL
		  UNION
		  SELECT DISTINCT tcm.[Machine]      
			  ,tcw.[LastConnectedUser]
			  ,vhr.[CompanyCode]     
		  FROM [Citrix].[dbo].[tblCitrixWorkers] tcw WITH (NOLOCK)
		  INNER JOIN [Citrix].[dbo].[tblCitrixMachine] tcm WITH (NOLOCK) ON tcw.[MachineID] = tcm.MachineID
		  LEFT JOIN [HRData].[dbo].[vwHRData] vhr WITH (NOLOCK) ON SUBSTRING(tcw.[LastConnectedUser],CHARINDEX('\',tcw.[LastConnectedUser]) + 1,LEN(tcw.[LastConnectedUser])) = vhr.[NBK]
		  WHERE tcw.[LastConnectedUser] IS NOT NULL AND vhr.[CompanyCode] IS NOT NULL

		  ;WITH CTEDup AS(
				  SELECT *,
					 RN = ROW_NUMBER()OVER(PARTITION BY Machine ORDER BY NBK ASC)
				  FROM @LastLoggedOn
				)		
				DELETE FROM CTEDup WHERE RN > 1  

			DECLARE @Machine TABLE
			(	
				MachineGUID	nvarchar(100),
				Region varchar(20),
				Machine nvarchar(50),
				NBK varchar(20),
				DevicePurpose varchar(50),
				CompanyCode nvarchar(50),
				MailCode nvarchar(50),
				Model varchar(20),
				[Status] varchar(10)
			) 

			DECLARE @MachineOut TABLE
			(
				MachineGUID			VARCHAR(500),
				Region				VARCHAR(100),
				Machine				VARCHAR(100),
				NBK					VARCHAR(MAX),
				DevicePurpose		VARCHAR(100),
				CompanyCode			VARCHAR(100),
				CompanyCodeCnt		INT,
				MailCode			VARCHAR(100),
				Model				VARCHAR(100),
				[Status]			VARCHAR(100)	
			);

			DECLARE @MachineOut2 TABLE
			(
				MachineGUID			VARCHAR(500),
				Region				VARCHAR(100),
				Machine				VARCHAR(100),
				NBK					VARCHAR(MAX),
				DevicePurpose		VARCHAR(100),
				CompanyCode			VARCHAR(100),
				CompanyCodeCnt		INT,
				MailCode			VARCHAR(100),
				Model				VARCHAR(100),
				[Status]			VARCHAR(100)	
			);

			DECLARE @MachineOut3 TABLE
			(
				Machine		VARCHAR(100),
				TotCount	INT
			);

			CREATE table #MachineOutFinal
			(
				MachineGUID			VARCHAR(500),
				Region				VARCHAR(100),
				Machine				VARCHAR(100),
				NBK					VARCHAR(MAX),
				DevicePurpose		VARCHAR(100),
				CompanyCode			VARCHAR(100),
				MailCode			VARCHAR(100),
				Model				VARCHAR(100),
				[Status]			VARCHAR(100)	
			);

			INSERT INTO @Machine
			SELECT DISTINCT 'VMWARE-' + CONCAT(UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 1,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 3,2)),space(1),
			UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 5,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 7,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 10,2)),space(1),
			UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 12,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 15,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 17,5)),
			space(1),UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 22,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 25,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 27,2)),
			space(1),UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 29,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 31,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 33,2)),
			space(1),UPPER(SUBSTRING(vcv.MachineGUID COLLATE DATABASE_DEFAULT, 35,2))) COLLATE DATABASE_DEFAULT as MachineGUID, vcv.Region, vcv.Machine,
			CASE WHEN nbk.[NBK] IS NULL THEN 'ID-NA' ELSE UPPER(RTRIM(LTRIM(nbk.[NBK]))) END as NBK, CASE WHEN tur.DevicePurpose IS NULL THEN 'Not Available' ELSE tur.DevicePurpose END as DevicePurpose,
			CASE WHEN hrd.CompanyCode IS NULL THEN 'Not Available' ELSE hrd.CompanyCode END [CompanyCode] , CASE WHEN hrd.MailCode IS NULL THEN 'Not Available' ELSE hrd.MailCode END [MailCode] , 'Tier ' + CONVERT(varchar(5), vcv.Tier) as Model, 
			'IN USE' as Status 
			FROM [vCenterGEN2].[dbo].[vwvCenter] vcv WITH (NOLOCK) 
			LEFT JOIN [CitrixGEN2].[dbo].[vwCitrixWorkers] w WITH (NOLOCK) on w.[Machine] = vcv.[Machine]
			LEFT JOIN [CitrixGEN2].[dbo].[tblCitrixNBK] nbk WITH (NOLOCK) on nbk.[NBK] = w.[StandardID]
			LEFT JOIN
			( SELECT DISTINCT tur.[MachineName], tur.[DevicePurpose]
				FROM [CitrixGEN2].[dbo].[tblUserRequest] tur WITH (NOLOCK) WHERE tur.[DevicePurpose] IS NOT NULL
				AND  tur.[AllocationDate] IN (SELECT MAX(tor.AllocationDate) FROM [CitrixGEN2].[dbo].[tblUserRequest] tor WITH (NOLOCK) WHERE tor.MachineName = tur.MachineName)
			) tur ON vcv.Machine = tur.MachineName
			LEFT JOIN [HRData].[dbo].[vwHRData] hrd WITH (NOLOCK) ON hrd.[NBK] COLLATE DATABASE_DEFAULT = nbk.[NBK]
			WHERE vcv.Active = 1 AND vcv.MachineIsTemplate = 0 AND vcv.EnvironmentID NOT IN (16)
			AND vcv.build NOT LIKE '%Linux 2.6x Kernel%' AND vcv.build NOT LIKE '%Red Hat%' AND vcv.build NOT LIKE '%Windows 2008%'
			AND vcv.build NOT LIKE '%Ubuntu-64%' AND vcv.build NOT LIKE '%SLES 11 64 bit%' AND vcv.build NOT LIKE '%FreeBSD%'
			AND vcv.build NOT LIKE '%Server%' AND vcv.VICFolder NOT LIKE '%Infrastructure%' AND vcv.VICFolder NOT LIKE '%server%'

			UNION

			SELECT DISTINCT 'VMWARE-' + CONCAT(UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 1,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 3,2)),space(1),
			UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 5,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 7,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 10,2)),space(1),
			UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 12,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 15,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 17,5)),
			space(1),UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 22,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 25,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 27,2)),
			space(1),UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 29,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 31,2)),space(1),UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 33,2)),
			space(1),UPPER(SUBSTRING(vcv.MachineGUID  COLLATE DATABASE_DEFAULT, 35,2))) COLLATE DATABASE_DEFAULT aS MachineGUID, vcv.Region, vcv.Machine COLLATE DATABASE_DEFAULT as Machine,
			CASE WHEN nbk.[NBK] IS NULL THEN 'ID-NA' ELSE UPPER(RTRIM(LTRIM(nbk.[NBK]))) END as NBK, 'Not Available' as DevicePurpose, CASE WHEN hrd.CompanyCode IS NULL THEN 'Not Available' ELSE hrd.CompanyCode END [CompanyCode],
			CASE WHEN hrd.MailCode IS NULL THEN 'Not Available' ELSE hrd.MailCode END [MailCode], CASE WHEN vcv.[machineCpuCount] = '1' THEN 'Tier 1'
			WHEN vcv.[machineCpuCount] = '2' THEN 'Tier 2'
			WHEN vcv.[machineCpuCount] = '4' THEN 'Tier 3'
			ELSE 'Not Available' END as Model,  
			'IN USE' as Status
			FROM [vCenter].[dbo].[vwvCenter] vcv WITH (NOLOCK)
			LEFT JOIN [Citrix].[dbo].[vwCitrixWorkers] w WITH (NOLOCK) on w.[Machine] = vcv.[Machine] 
			LEFT JOIN [Citrix].[dbo].[tblCitrixNBK] nbk WITH (NOLOCK) on nbk.[NBK] = w.[StandardID]
			LEFT JOIN [HRData].[dbo].[vwHRData] hrd WITH (NOLOCK) ON hrd.[NBK] COLLATE DATABASE_DEFAULT = nbk.[NBK]
			WHERE vcv.Active = 1 AND vcv.MachineIsTemplate = 0 AND vcv.EnvironmentID NOT IN (16) 
			AND vcv.build NOT LIKE '%Linux 2.6x Kernel%' AND vcv.build NOT LIKE '%Red Hat%' AND vcv.build NOT LIKE '%Windows 2008%'
			AND vcv.build NOT LIKE '%Ubuntu-64%' AND vcv.build NOT LIKE '%SLES 11 64 bit%' AND vcv.build NOT LIKE '%FreeBSD%'
			AND vcv.build NOT LIKE '%Server%' AND vcv.VICFolder NOT LIKE '%Infrastructure%' AND vcv.VICFolder NOT LIKE '%server%';

			WITH cteMachineCompanyCode AS
			(
				SELECT MachineGUID, CompanyCode, COUNT(CompanyCode) as CompanyCodeCnt, MailCode,
					COUNT(MachineGUID) AS [Count],
					ROW_NUMBER() OVER(ORDER BY COUNT(MachineGUID) DESC) AS RowNumber
				FROM @Machine	
				GROUP BY MachineGUID, CompanyCode, MailCode	
			)
			INSERT INTO @MachineOut
			SELECT DISTINCT M.MachineGUID, M.Region, M.Machine,
				STUFF(
				(
					SELECT ',' + NBK
					FROM @Machine
					WHERE MachineGUID = M.MachineGUID 
					ORDER BY NBK
					FOR XML PATH('')
				), 1, 1, '') AS NBK,
				M.DevicePurpose,
				MC.CompanyCode, MC.CompanyCodeCnt, MC.MailCode, M.Model, M.Status	
			FROM @Machine AS M
			INNER JOIN cteMachineCompanyCode AS MC
				ON M.MachineGUID = MC.MachineGUID	
	
				INSERT INTO @MachineOut2
				Select mo1.MachineGUID, mo1.Region, mo1.Machine, mo1.NBK, mo1.DevicePurpose, mo1.CompanyCode, mo1.CompanyCodeCnt, 'Not Available' as [MailCode], mo1.Model, mo1.[Status] from @MachineOut mo1
				WHERE CompanyCodeCnt > 1 AND CompanyCodeCnt IN (SELECT MAX(CompanyCodeCnt) FROM @MachineOut mo Where mo.Machine = mo1.Machine)	 
				GROUP BY mo1.MachineGUID, mo1.Region, mo1.Machine, mo1.NBK, mo1.DevicePurpose, mo1.CompanyCode, mo1.CompanyCodeCnt, mo1.MailCode, mo1.Model, mo1.[Status]
				HAVING SUM(LEN(mo1.NBK) - LEN(REPLACE(mo1.NBK,',','')) + 1) > 2

				INSERT INTO @MachineOut3
				SELECT Machine, COUNT(Machine) as TotCount FROM @MachineOut2		
				GROUP BY Machine
				HAVING COUNT(Machine) > 1
	
				INSERT INTO #MachineOutFinal
				Select TOP 1 mo2.MachineGUID, mo2.Region, mo2.Machine, mo2.NBK, mo2.DevicePurpose, lg.CompanyCode, mo2.[MailCode], mo2.Model, mo2.[Status] from @MachineOut2 mo2
				INNER JOIN @LastLoggedOn lg ON mo2.Machine = lg.Machine AND mo2.CompanyCode = lg.CompanyCode
				WHERE mo2.Machine IN (SELECT mo3.Machine FROM @MachineOut3 mo3 WHERE mo3.Machine = mo2.Machine)
				UNION
				Select mo1.MachineGUID, mo1.Region, mo1.Machine, mo1.NBK, mo1.DevicePurpose, mo1.CompanyCode, 'Not Available' as [MailCode], mo1.Model, mo1.[Status] from @MachineOut mo1
				WHERE CompanyCodeCnt > 1 AND CompanyCodeCnt IN (SELECT MAX(CompanyCodeCnt) FROM @MachineOut mo Where mo.Machine = mo1.Machine)	 
				AND mo1.Machine NOT IN (SELECT mo3.Machine FROM @MachineOut3 mo3 WHERE mo3.Machine = mo1.Machine)
				GROUP BY mo1.MachineGUID, mo1.Region, mo1.Machine, mo1.NBK, mo1.DevicePurpose, mo1.CompanyCode, mo1.CompanyCodeCnt, mo1.MailCode, mo1.Model, mo1.[Status]
				HAVING SUM(LEN(mo1.NBK) - LEN(REPLACE(mo1.NBK,',','')) + 1) > 2		
				UNION
				Select MachineGUID, Region, Machine, NBK, DevicePurpose, CompanyCode, MailCode, Model, [Status] from @MachineOut
				where CompanyCodeCnt = 1 AND NBK NOT LIKE '%,%'	
				UNION
				Select mco.MachineGUID, mco.Region, mco.Machine, mco.NBK, mco.DevicePurpose, lg.CompanyCode, mco.MailCode, mco.Model, mco.[Status] from @MachineOut mco
				INNER JOIN @LastLoggedOn lg ON mco.Machine = lg.Machine AND mco.CompanyCode = lg.CompanyCode
				WHERE mco.CompanyCodeCnt = 2 
				GROUP BY mco.MachineGUID, mco.Region, mco.Machine, mco.NBK, mco.DevicePurpose, lg.CompanyCode, mco.MailCode, mco.Model, mco.[Status]
				HAVING SUM(LEN(mco.NBK) - LEN(REPLACE(mco.NBK,',','')) + 1) = 2

				SET @currutcdate = GETUTCDATE()

				TRUNCATE TABLE [Reporting].[dbo].[tblHVDReconciliation]
				INSERT INTO [Reporting].[dbo].[tblHVDReconciliation]
				SELECT mof.MachineGUID,	mof.Region,	mof.Machine, mof.NBK, CASE WHEN mof.[DevicePurpose] = 'Not Available' AND NBK <> 'ID-NA' AND mof.[Machine] IN 
				(SELECT tdp.[Machine] FROM [Reporting].[dbo].[tblDevicePurpose] tdp WITH (NOLOCK) WHERE tdp.[Machine] COLLATE DATABASE_DEFAULT = mof.[Machine])
				THEN tdp.[DevicePurpose]  ELSE 'Not Available' END as [DevicePurpose], mof.CompanyCode, mof.MailCode, mof.Model, mof.[Status], @currutcdate as [dateStampUTC] FROM #MachineOutFinal mof
				LEFT JOIN [Reporting].[dbo].[tblDevicePurpose] tdp WITH (NOLOCK) ON tdp.[Machine] COLLATE DATABASE_DEFAULT = mof.[Machine]

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
I have not been able to assist you over the last few days, sorry, but I can't leave without making some observations that (I hope) will help.

1. Do NOT use "select distinct" when you are using "union".

select distinct ....
union
select distinct
that should be:

select ....
union
select ...
UNION (with "all") removes all duplicates, you are ADDING work by including "select distinct"

2. "union ALL" is faster than "union"

if you are doing something like this

select 1, * from tablea where field = 'x'
union
select 2, * from tableb where field = 'y'

i.e. you can NEVER get a row from the top query that is the same as the bottom query, then use UNION ALL

select 1, * from tablea where field = 'x'
UNION ALL
select 2, * from tableb where field = 'y'

3. case expressions

I have seen this being used:

case when somevalue = 'x' then something-A
else
       case when somevalue = 'y' then something-B
       else something-C
       end
end

That added case expression is NOT NEEDED, just do this instead

case when somevalue = 'x' then something-A
       when somevalue = 'y' then something-B
       else something-C
end

4.
   a.  Learn how to use ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)
   b.  Learn how to use COUNT() OVER(PARTITION BY ... )

Both of these could be useful I think, but just have not had the time to prove it for your question.

5. If you are building temp tables, adding indexes to to those tables can improve your overall performance. I don't know how much data you are dealing with, or why your current query is slow, but this option is available.

6. Use an execution plan to look for tuning. Learn how to get an execution plan and how to use these. It is an execution plan that tells you why a query is slow.

Hope these tips prove useful.
Mauro CazabonnetSenior .NET Software EngineerAuthor Commented:
Came up with solution :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.