Display list of events grouped by day of the week

Question

I am trying to display a list of events per day based on data entries in a sql server database. I want it to display on the page like this:

Monday - Title1 Title2
Tuesday - Title3
Wednesday - Title4 Title5

Right now my data looks like this

Title  | StartDate
Title1 | 2015-04-20 16:46:00.00
Title2 | 2015-04-20 12:46:00.00
Title3 | 2015-04-21 16:46:00.00
Title4 | 2015-04-22 1:46:00.00
Title5 | 2015-04-22 13:12:00.00

I only need the next 7 days. I'm very new to this and this is what I attempted to do but this does not work how I want it to at all.

StringBuilder el = new StringBuilder();
using (SqlConnection conn = new SqlConnection(""))
{
            SqlCommand cmd = new SqlCommand(@"select * from Event where
                                            convert(datetime, StartDate , 101) >= DATEADD(d,DATEDIFF(d,0,getdate()),0) 
                                            order by StartDate", conn);
            conn.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                DateTime start = DateTime.Now;
                DateTime end = DateTime.Now.AddDays(7);
                int days = (end - start).Days;
                Enumerable
                    .Range(0, days)
                    .Select(x => start.AddDays(x))
                    .ToList()
                    .ForEach(d =>
                    {
                        DayOfWeek dayOfWeek = d.DayOfWeek;
                        el.Append("<strong>" + Convert.ToDateTime(rdr["StartDate"].ToString()).DayOfWeek + "</strong><br />");
                        el.Append(rdr["Title"].ToString() + "<br />");
                    });
            }
            rdr.Close();
}
Label1.Text = el.ToString();

Which bit did you get stuck on? This question is too broad as is — mbdavis, Apr 17 '15 at 17:00
What DBMS are you using? This looks like it could be accomplished with a PIVOT query. — crthompson, Apr 17 '15 at 17:04
http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column — Bikal Bhattarai, Apr 17 '15 at 17:20

score 1 · Accepted Answer · answered Apr 17 '15 at 18:15

One approach for handling it in the code is to track the last day processed and print the day whenever it changes. Please see the example below.

StringBuilder el = new StringBuilder();
using (SqlConnection conn = new SqlConnection(connString)) {
  SqlCommand cmd = new SqlCommand(@"
              select * from Event 
              WHERE DATEDIFF(d, getdate(), StartDate) BETWEEN 0 and 6
              order by StartDate", conn);
  conn.Open();
  SqlDataReader rdr = cmd.ExecuteReader();

  if (rdr.HasRows) {
    rdr.Read();
    System.DayOfWeek lastDayProcessed = (DateTime)rdr["StartDate"]).DayOfWeek;
    el.AppendLine("<strong>" + lastDayProcessed + "</strong> -");
    el.Append(" " + rdr["Title"].ToString());

    while (rdr.Read()) {
      if (((DateTime)rdr["StartDate"]).DayOfWeek != lastDayProcessed) {
        // print the Day heading whenever the day changes
        el.AppendLine("<br />");
        lastDayProcessed = ((DateTime)rdr["StartDate"]).DayOfWeek;
        el.AppendLine("<strong>" + lastDayProcessed + "</strong> -");
      }
      el.Append(" " + rdr["Title"].ToString());
    }
  }
  rdr.Close();
}

I adjusted the WHERE clause to pull seven days; today and the next six days.

Display list of events grouped by day of the week

1 Answers1