SQL Query Help Needed

Discussion in 'SQL' started by titan90, Nov 14, 2006.

  1. titan90 New Member

    I am looking to use a query that will divide workstations into one of three automatic collections based upon the letter the computer name begins with. My goal is to use these collections for patching roll outs.

    For example:
    Collection 1 = A-H
    Collection 2 = I-O
    Collection 3 = P-Z

    These need to be Workstations only - no servers.

    With my basic SQL knowledge I am grabbing the computer name and doing > or = (letter) but am curious if there is a better way.

    Thanks.

    Titan90
    titan90, Nov 14, 2006
    #1

  2. titan90 New Member

    Got it in a Report - but need it as a collection

    well here is what I came up with as a report:

    SELECT [vResourceEx].[Name] AS 'Name', [Inv_AeX_AC_Identification].[OS Name] AS 'OS Name', [Inv_AeX_AC_Identification].[OS Type] AS 'OS Type', [Inv_AeX_AC_Identification].[System Type] AS 'System Type' FROM [vResourceEx] INNER JOIN [Inv_AeX_AC_Identification] ON [vResourceEx].[Guid] = [Inv_AeX_AC_Identification].[ResourceGuid] WHERE [vResourceEx].[IsManaged] = 1 AND ([vResourceEx].[Name] >= 'rl' AND [vResourceEx].[Name] <= 'Z') AND [Inv_AeX_AC_Identification].[OS Version] = '5.1' ORDER BY [vResourceEx].[Name] ASC

    I will now focus on turning this into a collection query - but it's a start.

    titan90
    titan90, Nov 15, 2006
    #2

  3. jperry784 New Member

    I did not change anything in your collection except for the section highlighted below. For each collection, change the range '[a-h]%' to the range you need.

    SELECT [vResourceEx].[Name] AS 'Name',
    [Inv_AeX_AC_Identification].[OS Name] AS 'OS Name',
    [Inv_AeX_AC_Identification].[OS Type] AS 'OS Type',
    [Inv_AeX_AC_Identification].[System Type] AS 'System Type'
    FROM [vResourceEx] INNER JOIN [Inv_AeX_AC_Identification]
    ON [vResourceEx].[Guid] = [Inv_AeX_AC_Identification].[_ResourceGuid]
    WHERE [vResourceEx].[IsManaged] = 1
    AND [vResourceEx].[Name] like '[a-h]%'
    AND [Inv_AeX_AC_Identification].[OS Version] = '5.1'
    ORDER BY [vResourceEx].[Name] ASC
    jperry784, Nov 15, 2006
    #3

  4. jperry784 New Member

    oh, forgot you want it as a collection:

    SELECT [vResourceEx].[Guid]
    FROM [vResourceEx] INNER JOIN [Inv_AeX_AC_Identification]
    ON [vResourceEx].[Guid] = [Inv_AeX_AC_Identification].[_ResourceGuid]
    WHERE [vResourceEx].[IsManaged] = 1
    AND [vResourceEx].[Name] like '[a-h]%'
    AND [Inv_AeX_AC_Identification].[OS Version] = '5.1'
    ORDER BY [vResourceEx].[Name] ASC
    jperry784, Nov 15, 2006
    #4

  5. titan90 New Member

    I got and error when trying to get this query to run as a collection - but pulled the "Order by" statement out and it worked like a charm.

    Thanks so much for the help.


    titan90
    titan90, Nov 15, 2006
    #5

Share This Page