I've been asked to build a report to show how recently a collection of systems
have used some of the apps in the Office suite. I've been able to get a basic
version of this written, but I'd like to have it be a little easier to read by
using some sub-queries.
Here is what I have so far:
SELECT dbo.v_R_System.Name0, MAX(dbo.v_MonthlyUsageSummary.LastUsage) AS
Expr1, dbo.v_GS_SoftwareUsageData.FileName
FROM dbo.v_MonthlyUsageSummary INNER JOIN
dbo.v_R_System ON dbo.v_MonthlyUsageSummary.ResourceID
= dbo.v_R_System.ResourceID INNER JOIN
dbo.v_GS_SoftwareUsageData ON
dbo.v_MonthlyUsageSummary.FileID = dbo.v_GS_SoftwareUsageData.FileID INNER JOIN
dbo.v_ClientCollectionMembers ON
dbo.v_R_System.ResourceID = dbo.v_ClientCollectionMembers.ResourceID
WHERE (dbo.v_ClientCollectionMembers.CollectionID = '@CollID') AND
((dbo.v_GS_SoftwareUsageData.FileName = 'winword.exe') OR
(dbo.v_GS_SoftwareUsageData.FileName = 'excel.exe') OR
(dbo.v_GS_SoftwareUsageData.FileName = 'outlook.exe'))
GROUP BY dbo.v_GS_SoftwareUsageData.FileName, dbo.v_R_System.Name0
I get output like this:
Name0 Expr1
FileName
Comp1 2014-12-18 22:16:31.000 EXCEL.EXE
Comp1 2015-05-26 22:14:27.000 OUTLOOK.EXE
Comp1 2015-05-28 23:24:44.000 WINWORD.EXE
What I'd like is output like this:
Name0 EXCEL.EXE
OUTLOOK.EXE WINWORD.EXE
Comp1 2014-12-18 22:16:31.000 2015-05-26
22:14:27.000 2015-05-28 23:24:44.000
The various sub-queries that I've tried to build haven't returned any results,
they never stop running.
Matt Atkinson
Client Systems Engineer
________________________________
This message is intended for the sole use of the addressee, and may contain
information that is privileged, confidential and exempt from disclosure under
applicable law. If you are not the addressee you are hereby notified that you
may not use, copy, disclose, or distribute to anyone the message or any
information contained in the message. If you have received this message in
error, please immediately advise the sender by reply email and delete this
message.
