ASP.NET MySQL query on a table

Question

Having tried several possibilities I have come to a stop.

As you see on my image I get my header dates from a calendar by coding as following:

<table border="1">
    <tr>
        <td class="auto-style17">&nbsp;</td>
        <%
               string[] days = { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" };
               for (int i = 0; i <= (Calendar1.SelectedDates.Count-1); i++)
            {
                Response.Write("<th>"+ Calendar1.SelectedDates[i].ToShortDateString() +" <br/>"+ days[i] +"</th>");

            }

Then I run this following query:

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand((@"select e.FirstName,
         count(case when v.VacationDates = @day1 THEN 1 END) 'Vacation',
         count(case when v.VacationDates = @day2 THEN 1 END) 'Vacation',
         count(case when v.VacationDates = @day3 THEN 1 END) 'Vacation',
         count(case when v.VacationDates = @day4 THEN 1 END) 'Vacation',
         count(case when v.VacationDates = @day5 THEN 1 END) 'Vacation',
         count(case when v.VacationDates = @day6 THEN 1 END) 'Vacation',
         count(case when v.VacationDates = @day7 THEN 1 END) 'Vacation'
         from Employee e
         left join Vacation v
         on e.EmployeeId = v.EmployeeId
         group by e.FirstName"), con2);

cmd.Parameters.AddWithValue("@day1",
Calendar1.SelectedDates[0].ToShortDateString());
cmd.Parameters.AddWithValue("@day2", Calendar1.SelectedDates[1].ToShortDateString());
cmd.Parameters.AddWithValue("@day3", Calendar1.SelectedDates[2].ToShortDateString());
cmd.Parameters.AddWithValue("@day4", Calendar1.SelectedDates[3].ToShortDateString());
cmd.Parameters.AddWithValue("@day5", Calendar1.SelectedDates[4].ToShortDateString());
cmd.Parameters.AddWithValue("@day6", Calendar1.SelectedDates[5].ToShortDateString());
cmd.Parameters.AddWithValue("@day7", Calendar1.SelectedDates[6].ToShortDateString());

using (rdr = cmd.ExecuteReader())
{
    while (rdr.Read())
    {
        <td><%Response.Write(rdr["FirstName"].ToString() + "<br/>");%></td>
        <td><%Response.Write(rdr[4].ToString());%></td>
        <td><%Response.Write(rdr[4].ToString());%></td>
        <td><%Response.Write(rdr[4].ToString());%></td>
        <td><%Response.Write(rdr[4].ToString());%></td>
        <td><%Response.Write(rdr[4].ToString());%></td>
        <td><%Response.Write(rdr[4].ToString());%></td>
        <td><%Response.Write(rdr[4].ToString());%></td>
    }
</tr>

But the result is as you see on image. How can I do to fix it. I think I am doing wrong on query. Does anyone have a solution of how I am going to fix it?

Thank you in advance.

You want to create a pivot table. This question is asked at least once a day... — Shadow, Oct 29 '16 at 23:27
Possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Shadow, Oct 29 '16 at 23:27
@Shadow Thank you for response, but I have stil problem. Only Richard gets 1 on every day... He should get only from 2016-10-27 until 2016-11-02 and Peter does not get any vacation. I have edited my code and Can you please check it? — Jonas Willander, Oct 30 '16 at 08:31

ASP.NET MySQL query on a table

0 Answers0