TSQL Help

  • 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...

  • 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.

  • Sorry. I said it wrong all of the machines should have one ore more groups.

    Thanks again...

  • 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.

  • 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

  • Thanks very much. Really appreciate your help!

    That's what I was looking for...

    Thanks Again...

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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/

  • 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!

  • 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!

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply