• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 748
  • Last Modified:

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.
0
colonelblue
Asked:
colonelblue
  • 11
  • 6
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now