Combining two reports

[barbaraphillips]

We track our software usage & purchases by Department. I have added a Department code to Software Purchase information in the Comments field. I am able to create a report to show me the count of purchases by Departments

Select Cast(tblSublicensesOrders.Comments As NVARCHAR(100)) DEPT,
    tblLicenses.softwareName,
    Sum(tblSublicensesOrders.Nrlicenses) As PurchasedLicenses
  From tblSublicensesOrders
    Inner Join tblLicenses On tblLicenses.LicenseidID =
      tblSublicensesOrders.LicenseidID
  Group By Cast(tblSublicensesOrders.Comments As NVARCHAR(100)),
    tblLicenses.softwareName

I am also able to create a report to show me Software in Use by Department (based on OU groups)

Select Top 1000000 tblADusers.Department,
  tblLicenses.softwareName,
  count(*) As InUseCount
From tblLicenses
  Left Join tblSoftwareUni On tblSoftwareUni.softwareName =
    tblLicenses.softwareName
  Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblADusers On tblAssets.Username = tblADusers.Username
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where (tblAssetCustom.State = 1 Or tblAssetCustom.State = 2) And
  (tblADComputers.OU Like '%Rockwall%' Or tblADComputers.OU Like '%Shared%' Or
    tblADComputers.OU Like '%EMS%') And tblADusers.Userdomain = 'Greenville'
Group By tblADusers.Department,
  tblLicenses.softwareName

How do I combine these two reports to show me Department, SoftwareName, In Use, Purchased -- all grouped by Department??

[Tom_P · Accepted solution]

Hi,

We received and answered this question via email. For everyone else's benefit, I'm pasting the SQL statement we sent via email below.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Memory,
  tblAssets.Processor,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAntivirus.DisplayName As AntiVirus,
  tblAntivirus.productUpToDate,
  tblOperatingsystem.Caption As OperatingSystem,
  tblOperatingsystem.Organization,
  Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric))
  As [Total size (GB)],
  Sum(Cast(Cast(tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
  1024 As Numeric)) As [Free in GB]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblAssets.Domain Like '%lekpas%' And tblAssetCustom.State = 1 And
  tblDiskdrives.DriveType = 3
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10,
  tblAssets.IPAddress,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Memory,
  tblAssets.Processor,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAntivirus.DisplayName,
  tblAntivirus.productUpToDate,
  tblOperatingsystem.Caption,
  tblOperatingsystem.Organization
Order By tblAssets.Domain,
  tblAssets.AssetName

[Skaiste]

Hello,

can you help me to combine two reports:
First:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Lastseen As [last scan],
Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric))
As [Total size (GB)],
Sum(Cast(Cast(tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric)) As [Free in GB]
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Lastseen
Order By tblAssets.AssetID

Second:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Memory,
tblAssets.Processor,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAntivirus.DisplayName,
tblAntivirus.productUpToDate,
tblOperatingsystem.Caption,
tblOperatingsystem.Organization
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssets.Domain Like '%lekpas%' And tblAssetCustom.State = 1

[Susan_A]

You can try turning your queries into sub queries and linking them that way, like so:

Select Top 1000000 SubQuery1.Department,
  SubQuery1.softwareName,
  SubQuery1.InUseCount,
  SubQuery2.PurchasedLicenses
From (Select Top 1000000 tblADusers.Department,
    tblLicenses.softwareName,
    count(*) As InUseCount
  From tblLicenses
    Left Join tblSoftwareUni On tblSoftwareUni.softwareName =
      tblLicenses.softwareName
    Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID
    Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID
    Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
    Left Join tblADusers On tblAssets.Username = tblADusers.Username And
      tblADusers.Userdomain = tblAssets.Userdomain
    Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Where (tblAssetCustom.State = 1 Or tblAssetCustom.State = 2) And
    (tblADComputers.OU Like '%rockwall%' Or tblADComputers.OU Like '%shared%' Or
      tblADComputers.OU Like '%ems%') And tblADusers.Userdomain = 'greenville'
  Group By tblADusers.Department,
    tblLicenses.softwareName) SubQuery1
  Left Join (Select Cast(tblSublicensesOrders.Comments As NVARCHAR(100)) DEPT,
    tblLicenses.softwareName,
    Sum(tblSublicensesOrders.Nrlicenses) As PurchasedLicenses
  From tblSublicensesOrders
    Inner Join tblLicenses On tblLicenses.LicenseidID =
      tblSublicensesOrders.LicenseidID
  Group By Cast(tblSublicensesOrders.Comments As NVARCHAR(100)),
    tblLicenses.softwareName) SubQuery2 On SubQuery2.DEPT = SubQuery1.Department
    And SubQuery2.softwareName = SubQuery1.softwareName