Calendar of Events table -group output by month

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 2005 Forums

Transact-SQL (2005)

Calendar of Events table -group output by month

Author

Topic

brorobo
Starting Member

3 Posts

Posted - 2012-01-03 : 16:28:44

I apologize in advance if this is not a SQL question, but a rather coding question, but I'm not sure how to do what I'm trying to do.

I have a single table that has location and contact information for various events. I am trying to output them to a web page running ColdFusionMX, but I would like to keep the events grouped by month. I don't have any aggregate functions, nor does the output call for any. Here's what I have:

SELECT event_ID,
	event_title,
	event_city,
	event_state,
	event_venue,
	event_startdate,
	event_startDay,
	event_startMonth,
	event_startYear,
	event_endDate,
	event_endDay,
	event_endMonth,
	event_endYear,
	event_startTime,
	event_startTimeHour,
	event_startTimeMin,
	event_startTimeMeridian,
	event_endTime,
	event_endTimeHour,
	event_endTimeMin,
	event_endTimeMeridian,
	event_comment1,
	event_contact,
	event_email,
	event_phone,
	event_fax,
	event_link1
FROM Events_Calendar_TEST
WHERE event_startTime > DateAdd(d, -1, GetDate())
ORDER BY event_startTime, event_title

I can output all the events in a list in the proper order, but I'd like to separate the different months. Obviously when I try to change the ORDER BY to a GROUP BY, I get the standard GROUP BY error: "event_ID is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Any help would be greatly appreciated, even if it's pointing me in the right direction. Thanks in advance.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-03 : 16:40:09

What's wrong with ORDER BY

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

brorobo
Starting Member

3 Posts

Posted - 2012-01-04 : 13:39:25

Thanks for the reply.

There's nothing wrong with ORDER BY, it's just not displaying the way I would like. I'm trying to lump the events together by month with a header:

JANUARY 2012
Date (Times)
Event 1 (linked)
City, State, venue
mailto:contact phone, fax
info

Date (Times)
Event 2 (linked)
City, State, venue
mailto:contact phone, fax
comment

Date (Times)
Event 3 (linked)
City, State, venue
mailto:contact phone, fax
comment

FEBRUARY 2012
Date (Times)
Event 4 (linked)
City, State, venue
mailto:contact phone, fax
comment

Instead, it's coming out like this:

JANUARY 2012
Date (Times)
Event 1 (linked)
City, State, venue
mailto:contact phone, fax
info

Date (Times)
Event 2 (linked)
City, State, venue
mailto:contact phone, fax
comment

JANUARY 2012
Date (Times)
Event 3 (linked)
City, State, venue
mailto:contact phone, fax
comment

FEBRUARY 2012
Date (Times)
Event 4 (linked)
City, State, venue
mailto:contact phone, fax
comment

Again, I apologize if this is not a SQL question, but a coding question, but I was hoping it could be accomplished either way.

quote:
Originally posted by X002548

What's wrong with ORDER BY

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 13:49:47

Ah, OK, a tad bit clearer..see you DONT want all the data in your columns

Where Does THIS


JANUARY 2012
Date (Times)
Event 1 (linked)
City, State, venue
mailto:contact phone, fax
info

Data come from...what columns in your table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

brorobo
Starting Member

3 Posts

Posted - 2012-01-04 : 14:26:08

I don't want all the columns in the query, no, but I do want a few. I can clear up the SQL, that's not a problem.

The ColdFusion code for the output looks like this:

<code>
<cfoutput query="GetEvents" group="event_startTime">
<h2>#dateformat(event_startTime, 'mmmm')#</h2>
<ul>
<cfoutput>
<li>
#DateFormat(event_startTime, "mmmm d, yyyy")# (#TimeFormat(event_startTime, "hh:mm")#)<br />
<strong>
<a href="#event_link1#" target="NKF">#event_title#</a>
</strong>
<br />
<span class="small">
#event_city#, #event_state# #event_venue#
<a href="mailto:#event_email#">#event_contact#</a> #event_phone#, #event_fax#
<br />
#Replace(Trim(event_comment1),'#chr(13)#','<br />','all')#
</span>
</li>
</cfoutput>
</ul>
</cfoutput>
</code>

Also, here are the datatypes for each column:

event_ID int
event_title nvarchar
event_city nvarchar
event_state nvarchar
event_venue nvarchar
event_startdate nvarchar
event_startDay nvarchar
event_startMonth nvarchar
event_startYear nvarchar
event_endDate nvarchar
event_endDay nvarchar
event_endMonth nvarchar
event_endYear datetime
event_startTime nvarchar
event_startTimeHour nvarchar
event_startTimeMin nvarchar
event_startTimeMeridian nvarchar
event_endTime datetime
event_endTimeHour nvarchar
event_endTimeMin nvarchar
event_endTimeMeridian nvarchar
event_comment1 ntext
event_contact nvarchar
event_email nvarchar
event_phone nvarchar
event_fax nvarchar
event_link1 nvarchar

So to answer your question, the field names are in [brackets] below

JANUARY 2012 [event_startTime mmmm,yyyy]
Date (Times) [event_startTime mmmm, dd yyyy] [event_startTime hh:mm]
Event 1 (linked) [event_link1] [event_title]
City, State, venue [event_city] [event_state] [event_venue]
mailto:contact phone, fax [event_email] [event_contact] [event_phone] [event_fax]
info [event_comment1]

Thanks again for your assistance.

quote:
Originally posted by X002548

Ah, OK, a tad bit clearer..see you DONT want all the data in your columns

Where Does THIS

JANUARY 2012
Date (Times)
Event 1 (linked)
City, State, venue
mailto:contact phone, fax
info

Data come from...what columns in your table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources