Group By Month/Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group By Month/Date

Author

Topic

josethegeek
Starting Member

45 Posts

Posted - 2004-06-30 : 17:23:03

Hello,
Maybe someone can help me with this one...

Currently I am writing a report that will generate the total count of visitors, by month, to specific sections on one of our sites.

I have a visitors table

tbl_Visitors
Visitor_ID int
Page_ID int
Visitor_Date datetime

I want to extract the total number of visitors for a range of months/year. I tried doing a group by converting the Visitor_Date to varchar, but it doesn't work. Currently the stored procedure takes in a startdate and an end date. I would like to return the results with the month and year, and the total visitors.

Thank You in advance,
Jose

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-06-30 : 17:42:08

[code]select page_id,mo,yr,count(page_id) hits
from
(
select visitor_id,page_id,datepart(mm,visitor_date) mo,datepart(yy,visitor_date) yr
from tbl_visitors
)d
group by page_id,mo,yr
[/code]

josethegeek
Starting Member

45 Posts

Posted - 2004-06-30 : 18:38:05

quote:

Originally posted by ehorn

select page_id,mo,yr,count(page_id) hits
from
(
	select visitor_id,page_id,datepart(mm,visitor_date) mo,datepart(yy,visitor_date) yr
	from tbl_visitors
)d
group by page_id,mo,yr

ehorn,
Thank You. The stored procedure is actually taking in a start date(ex. 4/1/2004) and an end date(ex. 8/31/2004). How would I be able take that into account?

Thanks again,
Jose

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-30 : 20:20:36

[code]

DECLARE
@start_date DATETIME,
@end_date DATETIME

DECLARE @tbl_visitors TABLE(
visitor_id INT,
page_id INT,
visitor_date DATETIME)

INSERT @tbl_visitors(visitor_id, page_id, visitor_date)
SELECT 1,1,'04/01/2004'
UNION ALL
SELECT 1,2,'08/31/2004'
UNION ALL
SELECT 1,3,'04/01/2004'
UNION ALL
SELECT 2,2,'08/31/2004'
UNION ALL
SELECT 2,3,'09/25/2004'
UNION ALL
SELECT 2,4,'09/15/2003'
UNION ALL
SELECT 3,4,'04/01/2004'
UNION ALL
SELECT 3,3,'04/01/2004'
UNION ALL
SELECT 4,5,'08/15/2004'

SELECT
@start_date = '04/01/2004',
@end_date = '08/31/2004'

SELECT
page_id,
mo,
yr,
COUNT(page_id) hits
FROM (
SELECT
visitor_id,
page_id,
DATEPART(mm,visitor_date) mo,
DATEPART(yy,visitor_date) yr
FROM
@tbl_visitors
WHERE
visitor_date BETWEEN @start_date AND @end_date)d
GROUP BY
page_id,
mo,
yr

[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

josethegeek
Starting Member

45 Posts

Posted - 2004-07-01 : 12:20:01

quote:

Originally posted by derrickleggett



DECLARE 
	@start_date DATETIME,
	@end_date DATETIME

DECLARE @tbl_visitors TABLE(
	visitor_id INT,
	page_id INT,
	visitor_date DATETIME)

INSERT @tbl_visitors(visitor_id, page_id, visitor_date)
	SELECT 1,1,'04/01/2004'
	UNION ALL
	SELECT 1,2,'08/31/2004'
	UNION ALL
	SELECT 1,3,'04/01/2004'
	UNION ALL
	SELECT 2,2,'08/31/2004'
	UNION ALL
	SELECT 2,3,'09/25/2004'
	UNION ALL
	SELECT 2,4,'09/15/2003'
	UNION ALL
	SELECT 3,4,'04/01/2004'
	UNION ALL
	SELECT 3,3,'04/01/2004'
	UNION ALL
	SELECT 4,5,'08/15/2004'

SELECT 
	@start_date = '04/01/2004',
	@end_date = '08/31/2004'

SELECT 
	page_id,
	mo,
	yr,
	COUNT(page_id) hits
FROM (
	SELECT 
		visitor_id,
		page_id,
		DATEPART(mm,visitor_date) mo,
		DATEPART(yy,visitor_date) yr
	FROM
		@tbl_visitors
	WHERE
		visitor_date BETWEEN @start_date AND @end_date)d
GROUP BY 
	page_id,
	mo,
	yr

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

derrickleggett,
Thank You. Let me try and see if it gives me back the results that I am looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources