Would Like to Run a Report for Specific Software with Name, Version, and Install Date

[jackrock]

This is largely what I have. But I'm having difficulty getting the install date for that software added. When I do, I get weird results; usually the results multiply by 3-4x, and each asset is listed several times, or I get no results at all. 

I've thought about working in tblSoftware.InstallDate, but I'm not getting anywhere (I think it's looking at all software, not the designated application). 

I'm a SQL neophyte, and we don't have in-house DBAs. I'd rather learn the answer than ask our outsourced DBAs, as they'll just try to fix the issue, and never help me.

TIA

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblAssets.Domain,
  tsysOS.OSname As OS,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets1.Lastseen As Lastseen1
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssets tblAssets1 On tblAssets1.AssetID = tblAssetCustom.AssetID
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID In (Select Top 1000000 tblSoftware.AssetID
    From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Software Client%' And
  tblSoftware.softwareVersion Like '%9.7.4%' And tblAssets1.Lastseen >
  GetDate() - 60 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

[Mister_Nobody · Accepted solution]

There is standard built-in LS query

Select Top (1000000) tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen As [Last successful scan],
  tblAssets.Lasttried As [Last scan attempt],
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  (Select Case tblSoftware.MsStore
        When 0 Then 'Desktop app'
        Else 'Microsoft Store app'
      End) As 'Type',
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftware.Installdate,
  tblSoftware.Lastchanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblState.Statename = 'Active'
Order By tblAssets.Domain,
  tblAssets.AssetName,
  Software