Video Screencast Help

Software Compliance Report By Cost Center Help

Created: 10 Oct 2013 • Updated: 25 Oct 2013

Dear All,

Please help. I don't know why the records duplicated, Any suggestions. Please help . Thanks

DECLARE @TrusteeScope nvarchar(max)
SET @TrusteeScope = '%TrusteeScope%'

SELECT 
CostXUser.[Cost Center Code], CostXUser.[Cost Center Name], 

p.Name AS [Software Product], ISNULL(compliance.[Purchased Licenses], 0) AS Purchased,

ISNULL(borrowed.[Borrowed License], 0) AS [Borrowed License],
ISNULL(donated.[Donated License], 0) AS [Donated License], ISNULL(install.Installed, 0) AS Installed,
ISNULL(compliance.[Non Inventoried Installs], 0) AS [Non-Inventoried Installs], ISNULL(compliance.[Purchased Licenses], 0) + ISNULL(borrowed.[Borrowed License], 0) -ISNULL(donated.[Donated License], 0) - ISNULL(install.Installed, 0) - ISNULL(compliance.[Non Inventoried Installs], 0) AS Compliance

FROM        
(
SELECT DISTINCT
                      ajs12_Inv_Cost_Center_Details.[Cost Center Code], ajs12_Inv_Cost_Center_Details.Description AS [Cost Center Name],
                       [dca11_Cost Center Ownership]._ResourceGuid AS _ComputerGuid
FROM         dbo.Inv_Cost_Center_Ownership AS [dca11_Cost Center Ownership] LEFT OUTER JOIN
                      dbo.Inv_Cost_Center_Details AS ajs12_Inv_Cost_Center_Details ON
                      [dca11_Cost Center Ownership].Owner = ajs12_Inv_Cost_Center_Details._ResourceGuid CROSS JOIN
                      dbo.Inv_Global_Windows_Users AS ajs9_Inv_Global_Windows_Users LEFT OUTER JOIN
                      dbo.Inv_Global_User_General_Details AS [ajs10_Primary User] ON ajs9_Inv_Global_Windows_Users._ResourceGuid = [ajs10_Primary User]._ResourceGuid
) AS CostXUser INNER JOIN
                         

(
SELECT _ResourceGuid, _ComputerResourceGuid, SUM(1) AS Installed
FROM dbo.Inv_SoftwareProduct_InstallationInfo GROUP BY _ResourceGuid, _ComputerResourceGuid) AS install

ON CostXUser._ComputerGuid = install._ComputerResourceGuid

RIGHT OUTER JOIN dbo.vSoftwareProduct AS p
   LEFT OUTER JOIN dbo.Inv_SoftwareProduct_ComplianceInfo AS compliance ON compliance._ResourceGuid = p.Guid LEFT OUTER JOIN
                         

(SELECT     _ResourceGuid, SUM(SharingNumber) AS [Borrowed License]
                            FROM          dbo.Inv_SoftwareProduct_LicenseSharing
                            WHERE      (IsBorrower = 1)
                            GROUP BY _ResourceGuid) AS borrowed ON borrowed._ResourceGuid = p.Guid LEFT OUTER JOIN
                          (SELECT     _ResourceGuid, SUM(SharingNumber) AS [Donated License]
                            FROM          dbo.Inv_SoftwareProduct_LicenseSharing AS Inv_SoftwareProduct_LicenseSharing_1
                            WHERE      (IsBorrower = 0)
                            GROUP BY _ResourceGuid) AS donated ON donated._ResourceGuid = p.Guid ON install._ResourceGuid = p.Guid
where
 p.guid in (select _ResourceGuid from inv_Software_Product_License pl where pl.IsLicensable = 1)  AND
  p.Guid in (
              SELECT
                [ResourceGuid] FROM [ScopeMembership]
              WHERE
                [ScopeCollectionGuid] IN (
                  SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@TrusteeScope)
                )
            ) order by
  p.Name

Operating Systems: