asked on

How do I count the top 10 most popular downloads within the last month?

I have a download table that records every time an article is downloaded (_title.typeid=2 means the title is an article), and my SQL statement currently gets the top 10 most downloaded articles. My download table also has a column called dateTime to record exactly when each article is downloaded using a dateTime. Instead of getting the top 10 most downloaded articles of all time, I want to get the top 10 most downloaded articles within the last month, but I'm not sure how to do operations on the datetime.

How would I modify this SQL to only get the top 10 downloaded articles of the month?

SELECT TOP (10) _Download.titleidfk, COUNT(_Download.titleidfk) AS Downloads
FROM _Download INNER JOIN
   _Title ON _Download.titleidfk = _Title.titleId
WHERE (_Title.typeId = '2')
GROUP BY _Download.titleidfk
ORDER BY Downloads DESC

Open in new window

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

waltersnowslinarnold

Try this..,

SELECT TOP (10) _Download.titleidfk, COUNT(_Download.titleidfk) AS Downloads
FROM _Download INNER JOIN
_Title ON _Download.titleidfk = _Title.titleId
WHERE _Title.typeId = '2'
GROUP BY _Download.titleidfk
ORDER BY Downloads DESC
BETWEEN DATEPART(mm,DateColumn) - 1 AND DATEPART(mm,DateColumn)

BYU-Studies

ASKER

Works great, thanks.