asked on

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

PortletPaul

Appears to be a continuation of
https://www.experts-exchange.com/questions/28929707/SQl-Query-Consolidate-Rows.html

ASKER CERTIFIED SOLUTION

Mauro Cazabonnet

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

PortletPaul

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 Cazabonnet

ASKER

Came up with solution :)