→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !
‎09-14-2015 10:14 PM
Solved! Go to Solution.
‎09-15-2015 11:47 AM
Select Top 1000000 tblSoftwareUni.softwareName,
Case When Coalesce(tNotInstalled.AssetID, 0) = 0 Then 'not installed'
Else 'installed' End As [installed on new computer]
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID
Left Join (Select tblAssets.AssetID,
tblSoftwareUni.SoftID
From tblSoftwareUni,
tblAssets
Inner Join tblComputersystem
On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetName = 'NewPC ' And
Not Exists(Select tblSoftware.softID,
tblSoftware.AssetID From tblSoftware
Where tblSoftware.softID = tblSoftwareUni.SoftID And tblSoftware.AssetID =
tblAssets.AssetID)) tNotInstalled On tNotInstalled.SoftID =
tblSoftware.softID
Where tblAssets.AssetName = 'OldPC '
Order By tblSoftwareUni.softwareName,
tblAssets.AssetName
‎09-28-2015 04:05 PM
‎09-28-2015 01:11 PM
‎09-24-2015 09:49 PM
‎09-24-2015 06:32 PM
‎09-21-2015 04:23 PM
Select Top 1000000 tblSoftwareUni.softwareName,
Case When Coalesce(tNotInstalled.AssetID, 0) = 0 Then 'not installed'
Else 'installed' End As [installed on new computer]
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID
Left Join (Select tblAssets.AssetID,
tblSoftwareUni.SoftID
From tblSoftwareUni,
tblAssets
Inner Join tblComputersystem
On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetName =@newPC And
Not Exists(Select tblSoftware.softID,
tblSoftware.AssetID From tblSoftware
Where tblSoftware.softID = tblSoftwareUni.SoftID And tblSoftware.AssetID =
tblAssets.AssetID)) tNotInstalled On tNotInstalled.SoftID =
tblSoftware.softID
Where tblAssets.AssetName =@oldPC
Order By tblSoftwareUni.softwareName,
tblAssets.AssetName
&@oldPC=HostnameA&@newPC=HostnameB
‎09-16-2015 04:12 PM
‎09-15-2015 11:47 AM
Select Top 1000000 tblSoftwareUni.softwareName,
Case When Coalesce(tNotInstalled.AssetID, 0) = 0 Then 'not installed'
Else 'installed' End As [installed on new computer]
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID
Left Join (Select tblAssets.AssetID,
tblSoftwareUni.SoftID
From tblSoftwareUni,
tblAssets
Inner Join tblComputersystem
On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetName = 'NewPC ' And
Not Exists(Select tblSoftware.softID,
tblSoftware.AssetID From tblSoftware
Where tblSoftware.softID = tblSoftwareUni.SoftID And tblSoftware.AssetID =
tblAssets.AssetID)) tNotInstalled On tNotInstalled.SoftID =
tblSoftware.softID
Where tblAssets.AssetName = 'OldPC '
Order By tblSoftwareUni.softwareName,
tblAssets.AssetName
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now