Solved

SQL Query - List most used report

Posted on 2014-07-29
6
374 Views
Last Modified: 2014-07-29
Hi all,

I have a table structure like this:

ReportLogID, UserID, ReportID, ReportName, Viewed

The table stores which users have run which reports in our system.

I would like to run a SQL query that lists the reports (grouped by ReportID) and shows me and the number of times it has been run.

I would also like to choose the start and end date that I want to run the query for (this is the Viewed field).

I can do this in Crystal Reports (where I am comfortable!), but I am not sure what to do in SQL.

Any hep would be great.

Thanks,

Tom
0
Comment
Question by:tom_optimum
  • 3
  • 2
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 40226380
the starter is easy:
 select ReportID, count(*), min(Viewed), max(Viewed)
from yourtable
group by ReportID

Open in new window

you may want more data/columns, in which case you want to read up this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40226385
Hi,

The below query will let you know that which report run how many times.

SELECT
	t2.Name AS ReportName, ReportID,InstanceName,COUNT(1) counts
	FROM dbo.ExecutionLog t1
    JOIN dbo.Catalog t2
    ON t1.ReportID = t2.ItemID
	GROUP BY t2.Name , ReportID,InstanceName

Open in new window

0
 

Author Comment

by:tom_optimum
ID: 40226388
Great - thanks.

So, I am using this:

SELECT ReportID, ReportName, count(*), min(Viewed), max(Viewed)
FROM tblReportViewLog
GROUP BY ReportID, ReportName

Open in new window

What would I need to do so I can type in the start and end date instead of min and max?

Thanks,

Tom
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 250 total points
ID: 40226392
Hello,

try this for your local table

SELECT ReportName,Count(ReportID) Counts FROM YourTable
	WHERE Viewed BETWEEN DATE1 AND DATE2
	group by ReportName

Open in new window

0
 

Author Comment

by:tom_optimum
ID: 40226422
Great - thanks.

Here is what I used int he end
-- Reports run over all time
SELECT ReportID, ReportName, count(*), min(Viewed), max(Viewed)
FROM tblReportViewLog
GROUP BY ReportID, ReportName

-- Reports run between two dates
SELECT ReportID, ReportName, Count(ReportID) Counts FROM tblReportViewLog
WHERE Viewed BETWEEN '2013-09-01' AND '2014-07-29'
GROUP BY ReportID, ReportName

Open in new window

Thanks for you help.

Tom
0
 

Author Closing Comment

by:tom_optimum
ID: 40226426
Great help guys.

Thanks,

Tom
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question