Posted At : 20 February 2009 17:27
| Posted By : James Privett
Related Categories:
Development
-
-
Keep up with the latest news, ideas and opinions.
-
-
Balcony Suite,
69 Grand Parade,
Brighton & Hove,
BN2 9TS
Tel: 0845 2571870
Blogs
I was recently asked to run a report showing the total amount of subscribers one of our sites has had over the last ten weeks. The results were to be broken down into a per week basis. This would be simple enough to do in Coldfusion but I wanted to take all the work away from the server and let the database do all the hard work.
Here is what I can up with
<!--- get subscribers info for the last ten weeks --->
<cfquery name="qLastTenWeeks" datasource="#request.datasource#">
SELECT top 10 DATEPART(YEAR,date_created) AS DateYear, DATEPART(wk,date_created) AS NoWeek, count(userID) AS Subscribers
FROM tblUser
GROUP BY DATEPART(YEAR,date_created), DATEPART(wk,date_created)
ORDER BY DATEPART(YEAR,date_created) desc, DATEPART(wk,date_created) desc
</cfquery>
<cfquery name="qLastTenWeeks" datasource="#request.datasource#">
SELECT top 10 DATEPART(YEAR,date_created) AS DateYear, DATEPART(wk,date_created) AS NoWeek, count(userID) AS Subscribers
FROM tblUser
GROUP BY DATEPART(YEAR,date_created), DATEPART(wk,date_created)
ORDER BY DATEPART(YEAR,date_created) desc, DATEPART(wk,date_created) desc
</cfquery>
The query returns the year of date_created and the week of date_created. Grouping the results by Year and Week ensures we get the correct amount of subscribers for the required timeframe.
Below are the results of the query.
There are no comments for this entry.
[Add Comment]