Thursday, March 29, 2012

Getting the results for month and year by a single SQL query

Hi,

I've to get the results from a single table for month and year-to-date by a single query. I've two columns monthstartdate and monthenddate. If it is only for month I can assign the values and execute the query. Vice versa for year. But If I want to get results for month and year at once like below:

Reportingdate # of Views per month # of view per year

Please suggest me on writing this query.I am confused. Please give me data sample and desired result.

Thank you.|||Current Result:

PageName Month-To-Date # of views
SqlHelp.aspx 15

Expected Result: Along with the above result it should fetch result for year to date( Jan 1st 2007 to current date)

PageName Month-#-views Year-to-Date # Views
SqlHelp.aspx 5 300

Current query is like as below:
SELECT TOP(10)
PageName,
SUM([Number of Views])[Number of Views],
FROM Pages (NOLOCK)
WHERE MonthStartDt >= @.StartDt
AND MonthEndDt < DATEADD(d, 1, @.EndDt)
GROUP BY
PageName
ORDER BY SUM([Number of Views]) DESC

How do I write query to get the results for month as well as year in a single query, please suggest me.|||I need table structure like what columns are in a table and what parameters you pass to the query to get a result.
Give me something like this:
Table name [Pages]

DATA
PageName , Number of Views, MonthStartDt, MonthEndDt
Page 1 1000 01/01/2006 31/03/2006
Page 1 500 01/05/2006 31/07/2006
Page 2 7000 01/01/2006 31/03/2006

Parameters @.StartDtMonth = ?
Parameters @.EndDtMonth = ?
Parameters @.StartDtYear = ?
Parameters @.EndDtYear = ?

What result do you expect to get
PageName, Count per Month, Count per Year.

No comments:

Post a Comment