cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Casey4147
Engaged Sweeper
I've been tasked with creating a report that will be similar to the PC Licensing report, for Mac software. Even better, we'd like results from both platforms tallied if possible.

On the PC side, Adobe Creative Suite is shown as Adobe Creative Suite. On the Mac, it's shown as components - PhotoShop, InDesign, Illustrator, etc.

Is there a way to filter things so that if a Mac has PhotoShop, Illustrator, InDesign, Bridge, and Acrobat, it has Creative Suite Design Standard; if it also has Premiere, it has Production Premium; if it has Dreamweaver then it has Design Web...? I'm learning SQL as I go, and this bit is beyond me.

Thanks in advance for any assistance.
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
This might be a bit complicated. You could try something like this:

Select tblAssets.AssetID,
tblAssets.AssetName,
Case When tblAssets.AssetID In (Select Top 1000000 tSoftware1.AssetID
From (Select tblMacApplications.AssetID
From tblSoftwareUni
Inner Join tblMacApplications On tblSoftwareUni.SoftID =
tblMacApplications.softid
Where tblSoftwareUni.softwareName Like '%Photoshop%') tSoftware1
Inner Join (Select tblMacApplications.AssetID
From tblSoftwareUni
Inner Join tblMacApplications On tblSoftwareUni.SoftID =
tblMacApplications.softid
Where tblSoftwareUni.softwareName Like '%Illustrator%') tSoftware2
On tSoftware1.AssetID = tSoftware2.AssetID Inner Join
(Select tblMacApplications.AssetID
From tblSoftwareUni
Inner Join tblMacApplications On tblSoftwareUni.SoftID =
tblMacApplications.softid
Where tblSoftwareUni.softwareName Like '%InDesign%') tSoftware3
On tSoftware1.AssetID = tSoftware3.AssetID Inner Join
(Select tblMacApplications.AssetID
From tblSoftwareUni
Inner Join tblMacApplications On tblSoftwareUni.SoftID =
tblMacApplications.softid
Where tblSoftwareUni.softwareName Like '%Bridge%') tSoftware4
On tSoftware1.AssetID = tSoftware4.AssetID Inner Join
(Select tblMacApplications.AssetID
From tblSoftwareUni
Inner Join tblMacApplications On tblSoftwareUni.SoftID =
tblMacApplications.softid
Where tblSoftwareUni.softwareName Like '%Acrobat%') tSoftware5
On tSoftware1.AssetID = tSoftware5.AssetID) Then
'Creative Suite Design Standard' End As [Installed package]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1


This will become a long script for this kind of query if you want to do it for all possible combinations. It might be much easier to do it in Excel.

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
This might be a bit complicated. You could try something like this:

Select tblAssets.AssetID,
tblAssets.AssetName,
Case When tblAssets.AssetID In (Select Top 1000000 tSoftware1.AssetID
From (Select tblMacApplications.AssetID
From tblSoftwareUni
Inner Join tblMacApplications On tblSoftwareUni.SoftID =
tblMacApplications.softid
Where tblSoftwareUni.softwareName Like '%Photoshop%') tSoftware1
Inner Join (Select tblMacApplications.AssetID
From tblSoftwareUni
Inner Join tblMacApplications On tblSoftwareUni.SoftID =
tblMacApplications.softid
Where tblSoftwareUni.softwareName Like '%Illustrator%') tSoftware2
On tSoftware1.AssetID = tSoftware2.AssetID Inner Join
(Select tblMacApplications.AssetID
From tblSoftwareUni
Inner Join tblMacApplications On tblSoftwareUni.SoftID =
tblMacApplications.softid
Where tblSoftwareUni.softwareName Like '%InDesign%') tSoftware3
On tSoftware1.AssetID = tSoftware3.AssetID Inner Join
(Select tblMacApplications.AssetID
From tblSoftwareUni
Inner Join tblMacApplications On tblSoftwareUni.SoftID =
tblMacApplications.softid
Where tblSoftwareUni.softwareName Like '%Bridge%') tSoftware4
On tSoftware1.AssetID = tSoftware4.AssetID Inner Join
(Select tblMacApplications.AssetID
From tblSoftwareUni
Inner Join tblMacApplications On tblSoftwareUni.SoftID =
tblMacApplications.softid
Where tblSoftwareUni.softwareName Like '%Acrobat%') tSoftware5
On tSoftware1.AssetID = tSoftware5.AssetID) Then
'Creative Suite Design Standard' End As [Installed package]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1


This will become a long script for this kind of query if you want to do it for all possible combinations. It might be much easier to do it in Excel.