Report Help Needed

pstage's picture
pstage
March 23rd, 2009

Normal
0

false
false
false

EN-US
X-NONE
X-NONE

MicrosoftInternetExplorer4

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}

I have two reports that I need to combine. Just beating my head into the wall at this point. Can anyone tell me if there is an easy way to merge these into one?

 

The first is just a simple report that lists network printers with the fields below. Works fine.

 

SELECT T0.[Name] AS 'Name', T1.[Connection] AS 'Connection', T1.[Dept. ID] AS 'Dep', T1.[Office Location] AS 'Office Location', T1.[Type] AS 'Type', T1.[MAC Address] AS 'MAC Address', T0.[Guid] FROM [vResourceEx] T0 INNER JOIN [Inv_LPH_Printer_Details] T1 ON T0.[Guid] = T1.[_ResourceGuid] WHERE T0.[ResourceTypeGuid] = '8e4f62f4-948c-4071-87a0-f39edffa9795' ORDER BY T0.[Name] ASC

 

This is a report that lists all assets with a status of disposed.

 

SELECT     i1.Name AS Asset, rt.Name AS [resource type], i2.Name AS

Status, i1.Guid

FROM         ResourceAssociation ra INNER JOIN

                      Item i1 ON i1.Guid = ra.ParentResourceGuid INNER

JOIN

                      Item i2 ON i2.Guid = ra.ChildResourceGuid INNER

JOIN

                      ItemResource ir ON ra.ParentResourceGuid = ir.Guid

INNER JOIN

                      ResourceType rt ON rt.Guid = ir.ResourceTypeGuid

WHERE     (ra.ResourceAssociationTypeGuid =

'3028166F-C0D6-41D8-9CB7-F64852E0FD01') AND (i2.Name LIKE 'disposed')

 

What I need is the information in the top report, that excludes all assets that have a status of disposed. Both work independently, but I can’t get the SOBs to play nicely together.  So, how do I add a restriction to exclude all assets that have been disposed/retired?

 

Any help would be appreciated.

Filed under: , ,
0 votes
artusod's picture
artusod
33 weeks 6 days ago

try this:SELECT T0.[Name] AS

try this:

SELECT T0.[Name] AS 'Name', T1.[Connection] AS 'Connection', T1.[Dept. ID] AS 'Dep', T1.[Office Location] AS 'Office Location',
 T1.[Type] AS 'Type', T1.[MAC Address] AS 'MAC Address',
 T0.[Guid]
FROM [vResourceEx] T0 INNER JOIN [Inv_LPH_Printer_Details] T1 ON T0.[Guid] = T1.[_ResourceGuid]
WHERE T0.[ResourceTypeGuid] = '8e4f62f4-948c-4071-87a0-f39edffa9795' and T0.Guid NOT IN (
     SELECT   Guid
    FROM   Item
    WHERE  [Name] LIKE 'disposed'
    )
ORDER BY T0.[Name] ASC

Dan

0 votes