How to add Month/Year Header , and a subsequent line space from a MS SQL query that outputs events in a group by Month?

Hello Experts,

I have a query that outputs events by group of Months and ordered ASC.
It works fine but I'd like to have it display the Month and Year as a header over each relative grouped months and a line break after before the next month.

i.e
January 2012
1/12/12 Alpha Party field 2 field 3
1/14/12 Beta Party field 2 field 3
1/22/12 Gamma Party field 2 field 3
1/29/12 Delta Party field 2 field 3

February 2012
2/2/12 Alpha Party field 2 field 3
2/18/12 Beta Party field 2 field 3
2/23/12 Gamma Party field 2 field 3
2/26/12 Delta Party field 2 field 3

I am a novice coder that prefers ASP Classic and VBScript.
How do I do this using the SQL Statement below?

SELECT MONTH(start) AS 'Month', field1, ID, Event,[Start], [End]
FROM dbo.Events Group By Month(start), field1, ID, Event, [Start],[End]
ORDER BY Month ASC

Open in new window


Thank you in advance Experts.
colonelblueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert SchuttSoftware EngineerCommented:
You would usually do that in your code by setting up 2 variables: 1 to hold the current header value and 1 to remember the previous value. Then use those to print the header only when it changes.
0
colonelblueAuthor Commented:
Would you kindly please be more explicit? I do not understand.
0
Robert SchuttSoftware EngineerCommented:
In your code you process all the records already I assume.

Add something like this, sort of in pseudo code:

strPrevGroup = ""
Loop ReadFromDb
  strCurrentGroup = MonthName(Fields("Start")) & " " & Year(Fields("Start"))
  If strCurrentGroup <> strPrevGroup Then Print strCurrentGroup : strPrevGroup = strCurrentGroup
  ' Regular processing here
End Loop

If you need further help incorporating this in your code, post what you have got now (the relevant part).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

colonelblueAuthor Commented:
Robert, do I feel like a dunce.
I am a novice coder and my code usage is quite basic; I could not understand that. :(


This is the code that I have functional.
It returns all the events grouped in order by month/year but there are no headers ( for the month/Year per group and no line space to separate the months.

I am very grateful for your time.


<%
Dim RS_Events
Dim RS_Events_cmd
Dim RS_Events_numRows

Set RS_Events_cmd = Server.CreateObject ("ADODB.Command")
RS_Events_cmd.ActiveConnection = MM_CAL_STRING
RS_Events_cmd.CommandText = "SELECT MONTH(start) AS 'Month', location, ID, Event,[Start], [End] FROM dbo.Events Group By Month(start), location, ID, Event, [Start],[End] ORDER BY Month ASC" 
RS_Events_cmd.Prepared = true

Set RS_Events = RS_Events_cmd.Execute
RS_Events_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 30
Repeat1__index = 0
RS_Events_numRows = RS_Events_numRows + Repeat1__numRows
%>

......


 <tr>
    <td width="650" height="28" align="left" valign="top"><span class="title"> <%=(RS_Events.Fields.Item("Start").Value)%> to <%=(RS_Events.Fields.Item("End").Value)%></span><br />      <a href="detail.asp?ID=&amp;Attendance=" class="cal"><%=(RS_Events.Fields.Item("Event").Value)%></a></td>
    <td width="155" valign="top" class="cal_title">&nbsp;</td>
  </tr>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  RS_Events.MoveNext()
Wend
%>
<tr>

Open in new window

0
Robert SchuttSoftware EngineerCommented:
ok, no problem, that shows what I was talking about.

I see a "Wend" at the end, there must be a While somewhere, most likely: "While Not RS_Events.EOF", that's the loop I refered to. There may be a slight complication, it looks like the results are paginated, but that's part of the code you left out, so we'll get to that later if necessary.

so somewhere at the start, declare 2 new variables and initialize the first:

Dim strPrevGroup, strCurrentGroup
strPrevGroup = ""

Open in new window


Then, just inside the loop, add the group check, make sure it's inside ASP tags:

  strCurrentGroup = MonthName(RS_Events.Fields.Item("Start").Value) & " " & Year(RS_Events.Fields.Item("Start").Value)
  If strCurrentGroup <> strPrevGroup Then
%>
<tr><td><%= strCurrentGroup %></td></tr>
<%
    strPrevGroup = strCurrentGroup
  End If

Open in new window


This makes sure you always do this part just before you process the next record. Now, you may have to check the function calls (like MonthName), I can't check the code at the moment.

Then have another look at adding the empty line, I suspect you want this before the group header but only if it's not the first.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
colonelblueAuthor Commented:
Hello and thank you.
But just as you pointed out about MonthName.
I'm not sure what I'm to do with that?

I received this error:

Microsoft VBScript runtime error '800a0005'
Invalid procedure call or argument: 'MonthName'


Again, I am so grateful for your assistance and patience.
0
colonelblueAuthor Commented:
Hello Robert.

I searched for MonthName function call and figured out - learned about it. :)
I had to add "month as shown below:
  </tr>
  <% strCurrentGroup = MonthName(Month(RS_Events.Fields.Item("Start").Value)) & " " & Year(RS_Events.Fields.Item("Start").Value)
  If strCurrentGroup <> strPrevGroup Then
%>
<tr><td><%= strCurrentGroup %></td></tr>

Open in new window



I am just not sure how to add a line break after each groupings before the next header.

I am so grateful that you have helped me learn something new and great!! :)
<Happy Dance>
0
Robert SchuttSoftware EngineerCommented:
Ah great! Yeah, that function apparently doesn't take a date as argument but just the month number, well done!

Now then, the empty line. What I meant with my previous remark is that we already have a bit of code where we check if we need to show the groupheader. Within that part you can check if it's "not the first one" by looking at strPrevGroup. If that is filled, you know to display an 'empty' table row. Now maybe that's not important in this case but I would add that check normally if I wanted the output to start at the top of the page:

...
  </tr>
  <% strCurrentGroup = MonthName(Month(RS_Events.Fields.Item("Start").Value)) & " " & Year(RS_Events.Fields.Item("Start").Value)
  If strCurrentGroup <> strPrevGroup Then
    If strPrevGroup <> "" Then
<%
<tr><td>&nbsp;</td></tr>
%>
    End If
%>
<tr><td><%= strCurrentGroup %></td></tr>
...

Open in new window

0
colonelblueAuthor Commented:
I just had to move some close tags around and it worked perfectly!
You are the best!!

THANK YOU SO MUCH!
Wishing you great karma.

<%  If strPrevGroup <> "" Then %>

<tr><td>&nbsp;</td></tr>

  <% End If    %>

Open in new window

0
colonelblueAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for colonelblue's comment #37793851

for the following reason:

Thank you Robert for your expertise, patience and generosity.<br />You've taught a man how to fish. <br />You are the very essence of Experts Exchange.
0
colonelblueAuthor Commented:
I made an error and chose myself for the awarded points,they were meant for Robert.
Please help.
0
colonelblueAuthor Commented:
I made an error and chose myself for the awarded points,they were meant for Robert.
Please help.
0
Robert SchuttSoftware EngineerCommented:
I think that should be possible to correct by a moderator, I'll object and see how that works now ;-)
0
colonelblueAuthor Commented:
Robert thank you! I am so embarrassed.
I have a cold and on medication which is obviously making me loopy.

Kind regards Robert.
0
Robert SchuttSoftware EngineerCommented:
Don't worry about it. Anything else we need to look at, the counter maybe?
0
colonelblueAuthor Commented:
Thank you modus_operandi!
I LOVE YOU GUYS!
0
colonelblueAuthor Commented:
Robert you're a gentleman, a scholar and made some other soul smile from ear to ear!
Thank you Sir!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.