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
704 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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
 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

740 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