Solved

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

Posted on 2012-03-31
18
673 Views
Last Modified: 2012-04-04
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
Comment
Question by:colonelblue
  • 11
  • 6
18 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 37792433
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
 

Author Comment

by:colonelblue
ID: 37792538
Would you kindly please be more explicit? I do not understand.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 37792552
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
 

Author Comment

by:colonelblue
ID: 37793851
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
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 37793914
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
 

Author Comment

by:colonelblue
ID: 37794344
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
 

Author Comment

by:colonelblue
ID: 37794397
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
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 37794853
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:colonelblue
ID: 37796579
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
 

Author Comment

by:colonelblue
ID: 37797216
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
 

Author Comment

by:colonelblue
ID: 37796599
I made an error and chose myself for the awarded points,they were meant for Robert.
Please help.
0
 

Author Comment

by:colonelblue
ID: 37796606
I made an error and chose myself for the awarded points,they were meant for Robert.
Please help.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 37797217
I think that should be possible to correct by a moderator, I'll object and see how that works now ;-)
0
 

Author Comment

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

Kind regards Robert.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 37798058
Don't worry about it. Anything else we need to look at, the counter maybe?
0
 

Author Comment

by:colonelblue
ID: 37799429
Thank you modus_operandi!
I LOVE YOU GUYS!
0
 

Author Closing Comment

by:colonelblue
ID: 37799432
Robert you're a gentleman, a scholar and made some other soul smile from ear to ear!
Thank you Sir!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now