February 6, 2015 at 9:40 am
Hello Friends
All machines should have three groups(grp1,grp2,grp3)
but some have two groups and some might have all three and some might have 1 or zero.
So I have data like this
MachineName, GrpName
Machine1 grp1
Machine1 grp2
Machine1 grp3
Machine2 grp1
Machine2 grp2
Machine3 grp3
Machine4 grp2
Machine4 grp3
I am trying to write a query to see missing groups for all machines from the above data
So
Machine1 has all three we can skip or say all three exist
MachineName GrpName
Machine1 all three exist--if we can do this good if not fine we can just skip since it has all three.
Machin2 Grp3 missing
Machine3 Grp1 and Grp2 missing
Machine4 grp1 missing
Any help on this will be appreciated...
February 6, 2015 at 9:46 am
Is there a separate table with a list of the machines to check? I ask because you said a machine may have zero groups. Zero groups would mean zero rows in the table you described.
In fact, it would be helpful to have separate tables for both the available groups that should be present, and the machines that should have the groups.
February 6, 2015 at 9:52 am
Sorry. I said it wrong all of the machines should have one ore more groups.
Thanks again...
February 6, 2015 at 11:39 am
Hi,
Try something like this:
with
CTE_Groups as
(
select distinct GrpName
from MyTable
),
CTE_Machines as
(
select distinct MachineName
from MyTable
)
select
m.MachineName,
g.GrpName
from CTE_Groups as g
cross join CTE_Machines as m
where
not exists (select 1 from MyTable as t
where
t.MachineName = m.MachineName and
t.GrpName = g.GrpName)
Hope this helps.
February 6, 2015 at 12:17 pm
A sample with closer to the format you requested
CREATE TABLE #MachineGroups (
MachineName VARCHAR(10),
GroupName VARCHAR(10)
)
INSERT #MachineGroups (MachineName,GroupName)
SELECT 'Machine1', 'grp1'
UNION ALL
SELECT 'Machine1', 'grp2'
UNION ALL
SELECT 'Machine1', 'grp3'
UNION ALL
SELECT 'Machine2', 'grp1'
UNION ALL
SELECT 'Machine2', 'grp2'
UNION ALL
SELECT 'Machine3', 'grp3'
UNION ALL
SELECT 'Machine4' ,'grp2'
UNION ALL
SELECT 'Machine4', 'grp3'
;
WITH GetMachineGroupPossibles AS (
SELECT MachineName, GroupName
FROM
(SELECT DISTINCT MachineName FROM #MachineGroups) m
CROSS JOIN
(SELECT DISTINCT GroupName FROM #MachineGroups) g
)
SELECT o.MachineName, COALESCE(STUFF((
SELECT ',' + p.GroupName
FROM GetMachineGroupPossibles p
LEFT JOIN #MachineGroups g
ON g.MachineName = p.MachineName
AND g.GroupName = p.GroupName
WHERE g.MachineName IS NULL
AND o.MachineName = p.MachineName
ORDER BY p.GroupName
FOR XML PATH ('')),1,1,''),'Has all groups') MissingGroups
FROM GetMachineGroupPossibles o
GROUP BY o.MachineName
DROP TABLE #MachineGroups
February 6, 2015 at 1:35 pm
Thanks very much. Really appreciate your help!
That's what I was looking for...
Thanks Again...
February 8, 2015 at 6:01 pm
It is possible to avoid multiple scans of your table if you know there are only 3 groups and you don't care about machines that are missing all of them.
SELECT MachineName
,Grps=CASE SUM(CASE GroupName WHEN 'grp1' THEN 1 WHEN 'grp2' THEN 2 WHEN 'grp3' THEN 4 ELSE 0 END)
WHEN 7 THEN 'All present'
WHEN 6 THEN 'grp1 missing'
WHEN 5 THEN 'grp2 missing'
WHEN 4 THEN 'grp1,grp2 missing'
WHEN 3 THEN 'grp3 missing'
WHEN 2 THEN 'grp1,grp3 missing'
WHEN 1 THEN 'grp2,grp3 missing'
END
FROM #MachineGroups a
GROUP BY MachineName
ORDER BY MachineName;
Using Nevyn's sample data.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 9, 2015 at 2:22 am
Hi,
you can also do this usinf Except -
;WITH CTE AS
(
SELECT MachineName, GroupName
FROM
(SELECT distinct A.MachineName
FROM #MachineGroups A)C
CROSS JOIN
(SELECT distinct B.GroupName
FROM #MachineGroups B)D
)
SELECT C.MachineName, C.GroupName
FROM CTE C
EXCEPT
SELECT M.MachineName, M.GroupName
FROM #MachineGroups M
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 18, 2015 at 7:35 am
Hello Nevyn,
Is there a way I can do to this query and get the machines that doesn't have any of the groups?
Machines with all groups missing.
Thanks In Advance!
March 18, 2015 at 7:43 am
Hello Dwain,
I would need machines that doesn't have all these three groups also.
This is an Ad-hoc query that runs rarely once in quarter or so..
Thanks In Advance!
March 18, 2015 at 5:51 pm
chinn (3/18/2015)
Hello Dwain,I would need machines that doesn't have all these three groups also.
This is an Ad-hoc query that runs rarely once in quarter or so..
Thanks In Advance!
In order to do that you need to do a LEFT JOIN from your machines (parent) table to the Machine Groups table (my CTE) and add a CASE (WHEN 0) that reports all groups missing.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply