Looping through SQL database using VBScript and grouping similar times

Question

I need to loop through my SQL Server datatable and display some information on each record. However, I need to group this by the same start times for each "event". I'd like to do this in one loop command and not a separate loop command for each time. I know it's probably something simple that I'm missing. But I can't figure it out. I'd like to group these events by start time. I'd like to have an H4 header for each start time and show all events for that start time and then move onto the next start time with an H4 tag as well. But only one H4 tag for each groups of start times. Here is my code:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open  

strSQL = "SELECT EventTitle, FinalStartTime, FinalEndTime, Organization, description FROM  Meetings WHERE   ApprovalStatus = 'Approved' ORDER BY FinalStartTime"

            Set rstDisplay = Conn.Execute(strSQL)

            i=0

            While Not rstDisplay.EOF  

            i=i+1

            strSecondStartTime = strStartTime

            strStartTime = rstDisplay("FinalStartTime")
            strEndTime = rstDisplay("FinalEndTime")





%>                  

            <h4><%=strStartTime%>&ndash;<%=strEndTime%></h4>

            <table class="prog-table" id="report<%=i%>">
          <tr>
            <td class="firsttd"><%=rstDisplay("EventTitle")%><span style="float:right; opacity:0.5; display:block; padding-left:50px;"><i class="fa fa-plus" aria-hidden="true"></i></span></td>
          </tr>

          <tr>
            <td> 
      <p><strong>Presented by:</strong><BR>
      <blockquote><%=rstDisplay("Organization")%></blockquote></p>


      <p><%=rstDisplay("description")%></p>




      </td>
          </tr>
          </table>      


<%                  
            rstDisplay.MoveNext
            Wend

            rstDisplay.Close
            Conn.Close
%>

The resulting HTML should be in simplified terms:

9:00 AM - 10:00 AM
Event 1
Event 2

11:00 AM - 12:00 PM
Event 3
Event 4

1:00 PM - 2:00 PM
Event 5

3:00 PM - 4:00 PM
Event 6
Event 7
Event 8

Currently my resulting HTML is incorrect and is displaying as such:

9:00 AM - 10:00 AM
Event 1
9:00 AM - 10:00 AM
Event 2
9:00 AM - 10:00 AM
Event 3

etc, etc.

Martha · Accepted Answer · 2016-10-14 22:33:21Z

1

Just keep track of what group you're on, and only display the <h4> if it changes.

G = ""
i = 0
Do Until rs.EOF
    strStartTime = rs("FinalStartTime")
    strEndTime = rs("FinalEndTime")
    If G <> strStartTime & "&ndash;" & strEndTime Then
        G = strStartTime & "&ndash;" & strEndTime
        Response.Write "<h4>" & G & "</h4>"
    End If
    i = i + 1
    %>
    <table class="prog-table">(etc.)</table>
    <%
    rs.Movenext
Loop

(Naturally, this assumes that your recordset is sorted by the header fields - in your case, the start and end times.)

edited Oct 14, 2016 at 22:33

answered Oct 14, 2016 at 22:27

Martha

3,7743 gold badges36 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dijkgraaf · Accepted Answer · 2016-10-16 17:55:50Z

I would try to make the SQL statement itself do the grouping and then iterate the resulting lines to more easily inject into the HTML. Something like this:

SELECT
    CAST(M.FinalStartTime as varchar(50)) + ' - ' + CAST(M.FinalEndTime as varchar(50))
    , 
    REPLACE(
    REPLACE(
    REPLACE(

        (SELECT IM.EventTitle FROM Meetings IM WHERE IM.FinalStartTime = M.FinalStartTime AND IM.FinalEndTime = M.FinalEndTime AND IM.ApprovalStatus = 'Approved'  For XML PATH (''))
            , '</EventTitle><EventTitle>', ',')
            , '<EventTitle>','')
            , '</EventTitle>','')
FROM
    Meetings M
WHERE  
    M.ApprovalStatus = 'Approved'
GROUP BY
    M.FinalStartTime, M.FinalEndTime,  CAST(M.FinalStartTime as varchar(50)) + ' - ' + CAST(M.FinalEndTime as varchar(50))
ORDER BY
    M.FinalStartTime

This SQL is off the top of my head and not optimized for performance, but it may make it easier for you. Just replace your strSQL = with this SQL.

Collectives™ on Stack Overflow

Looping through SQL database using VBScript and grouping similar times

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related