Comparing 2 PCs Inventory

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 AMHow about if you use a substring, so your comparison would be something like
this:
inv1.Displayname0 = substring(inv2.Displayname0, 1, len(inv1.Displayname0))
You can use the left function for this too which would be:
inv1.Displayname0 = left(inv2.Displayname0, len(inv1.Displayname0))
Sean
"FStuart" <fstuart1@.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
>