
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-26-2015 11:37 PM
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.
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.
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-29-2015 02:34 PM
This might be a bit complicated. You could try something like this:
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.
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.
1 REPLY 1

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-29-2015 02:34 PM
This might be a bit complicated. You could try something like this:
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.
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.
