"FStuart" <fstua***@maine.rr.com> wrote in message
news:1122056014.704562.15960@g44g2000cwa.googlegroups.com...
> I've decided to try another method to compare 2 PCs inventories via the
> ADD Remove Displayname.
>
> I have the following code I'm trying to use to compare the two and put
> in the Netbios_Name0 column PTLSTANDARD if it is lacking that software
> or the @name2 computer name if it exceeds the standard. It works fine
> when the Displaynames match (the @name2 computer does not have the
> Date/Time data in it) However, when the code finds that the reference
> computer and the @name2 don't match because of the Date/Time data it
> displays it as both lacking and exceeding.
> I'm racking my brain how to institute some form of LIKE where the
> reference computer DISPLAYNAME0% is LIKE @name2 then toss it out of the
> mix.
>
> Here is the code and the output.
> Thanks Fred
>
>
> Declare @name1 varchar(20)
> Declare @name2 varchar(20)
> Set @name1='PTLSTANDARD' /* THE REFERENCE PC */
> Set @name2='PTLWCSG8F11LAB'
>
> select
>  (case when inv1.Displayname0 is not null then @name1 else @name2 end)
> as Netbios_Name0,
>  (case when inv1.Displayname0 is not null then inv1.Displayname0 else
> inv2.Displayname0 end) as DisplayName0
>
> from
> (
> select s.Displayname0, Version0, Installdate0, ProdId0, Publisher0
> from v_GS_ADD_REMOVE_PROGRAMS s
> join v_R_System sys on s.resourceID= sys.resourceID
> where sys.Netbios_Name0 = @name1
> ) as inv1
>
> full outer join
> (
> select s.Displayname0, Version0, Installdate0, ProdId0, Publisher0
> from v_GS_ADD_REMOVE_PROGRAMS s
> join v_R_System sys on s.resourceID= sys.resourceID
> where sys.Netbios_Name0 = @name2
> ) as inv2
>
> on inv1.Displayname0  = inv2.Displayname0
> where (inv1.Displayname0 is NULL) or (inv2.Displayname0 is NULL)
> Order by Netbios_Name0, Displayname0
>
>
>
> Netbios_Name0   Displayname0
> =====================================================================
> PTLSTANDARD Catalog for UnumProv_All_03.1
> PTLSTANDARD Catalog for UnumProv_All_r054
> PTLSTANDARD Client Application Enabler for UDB
> PTLSTANDARD COMTI Client Common 2002.7
> PTLSTANDARD Flash Player 6
> PTLSTANDARD IBM CICS Universal Client
> PTLSTANDARD Norton Location Pointer
> PTLSTANDARD SNA Pointer
> PTLSTANDARD Websphere Client 5.3
> PTLSTANDARD XML Parser 3.0 SP2
> PTLSTANDARD XML_Parser_4.0_SP1
> PTLWCSG8F11LAB Catalog for UnumProv_All_03.1 5/19/2003 2:06:03 PM
> PTLWCSG8F11LAB Catalog for UnumProv_All_r054 6/13/2005 11:01:08 AM
> PTLWCSG8F11LAB Client Application Enabler for UDB 7.2 4/14/2003 8:23:46
> AM
> PTLWCSG8F11LAB COM CommAPI 2003.1 6/5/2003 4:19:54 PM
> PTLWCSG8F11LAB COMTI Client Common 2002.7 11/14/2003 8:09:28 AM
> PTLWCSG8F11LAB Flash Player 6 4/14/2003 8:37:35 AM
> PTLWCSG8F11LAB IBM CICS Universal Client 4/14/2003 8:27:06 AM
> PTLWCSG8F11LAB Norton Location Pointer 5/17/2005 4:13:00 PM
> PTLWCSG8F11LAB SNA Pointer 4/14/2003 8:30:58 AM
> PTLWCSG8F11LAB Websphere Client 5.3 4/14/2003 8:42:01 AM
> PTLWCSG8F11LAB XML Parser 3.0 SP2 4/12/2003 3:34:58 AM
> PTLWCSG8F11LAB XML_Parser_4.0_SP1 4/12/2003 3:47:23 AM
>