asked on

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.

Robert Schutt

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.

colonelblue

ASKER

Would you kindly please be more explicit? I do not understand.

Robert Schutt

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).

colonelblue

ASKER

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

ASKER CERTIFIED SOLUTION

Robert Schutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

colonelblue

ASKER

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.

colonelblue

ASKER

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>

Robert Schutt

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

colonelblue

ASKER

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

colonelblue

ASKER

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.

colonelblue

ASKER

I made an error and chose myself for the awarded points,they were meant for Robert.
Please help.

colonelblue

ASKER

I made an error and chose myself for the awarded points,they were meant for Robert.
Please help.

Robert Schutt

I think that should be possible to correct by a moderator, I'll object and see how that works now ;-)

colonelblue

ASKER

Robert thank you! I am so embarrassed.
I have a cold and on medication which is obviously making me loopy.

Kind regards Robert.

Robert Schutt

Don't worry about it. Anything else we need to look at, the counter maybe?

colonelblue

ASKER

Thank you modus_operandi!
I LOVE YOU GUYS!

colonelblue

ASKER

Robert you're a gentleman, a scholar and made some other soul smile from ear to ear!
Thank you Sir!