How to run same query on every single row of a table? (MVC)

Question

I'm currently having a table named 'Events' where there are columns 'EquipApprovedDate', 'EquipCalDueDate', & 'ThemeColor'. I want to update the ThemeColor based on the remaining days left before the EquipCalDueDate.

Green = Good

Orange = Considered okay

Red = Critical

The total days will be different for every equipment registered on the calendar. It will be calculated by using this formula (totaldays = EquipCalDueDate - EquipApprovedDate). The remaining days will be calculated using this formula (remainingDays = EquipCalDueDate - DateTime.Now).

If remainingDays is more than 2/3 of totaldays it will be labelled 'green'.

If remainingDays is less than 2/3 of totaldays but more than 1/3 of totaldays it will be labelled 'orange'.

If remainingDays is less than 1/3 of totaldays it will be labelled 'red'.

I want to apply this whole process on table every time the page is loaded, specifically on each and every single row found in the database. Basically to collect the data and return it for every single row. For now it's only running partially where the column ThemeColor is being updated to 'green' for every single row no matter what. What is the correct SQL query?

I have attached my current work together for your view.

Image of 'Events' table data

Image of 'Events' table design

    con.Open();
        string yyy = "SELECT * FROM [Events]";
        using (SqlCommand cmd = new SqlCommand(yyy, con))
        {
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                String startdate = reader["EquipApprovedDate"].ToString();
                DateTime Sdate = DateTime.Parse(startdate);
                String enddate = reader["EquipCalDueDate"].ToString();
                DateTime Edate = DateTime.Parse(enddate);
                String themecolor = reader["ThemeColor"].ToString();

                double totaldays = (Edate - Sdate).TotalDays;
                double remainingDays = (Edate - DateTime.Now).TotalDays;

                if (remainingDays > (totaldays * (2 / 3)))
                {
                    string sqlCoC = "UPDATE Events SET ThemeColor = 'green'";

                    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MVCConnectionString"].ConnectionString))
                    {
                        SqlCommand coccmd = new SqlCommand(sqlCoC, con);
                        con.Open();
                        coccmd.ExecuteNonQuery();
                        con.Close();
                    }

                    //green = means good
                }
                else if ((remainingDays < (totaldays * (2 / 3))) && (remainingDays > (totaldays * (1 / 3))))
                {

                    string sqlCoC = "UPDATE Events SET ThemeColor = 'orange'";

                    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MVCConnectionString"].ConnectionString))
                    {
                        SqlCommand coccmd = new SqlCommand(sqlCoC, con);
                        con.Open();
                        coccmd.ExecuteNonQuery();
                        con.Close();
                    }

                    //orange = considered okay
                }
                else if (remainingDays < (totaldays * (1 / 3)))
                {

                    string sqlCoC = "UPDATE Events SET ThemeColor = 'red'";

                    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MVCConnectionString"].ConnectionString))
                    {
                        SqlCommand coccmd = new SqlCommand(sqlCoC, con);
                        con.Open();
                        coccmd.ExecuteNonQuery();
                        con.Close();
                    }

                    //red = critical
                }
                else { }
            }
            reader.Close();
            con.Close();
        }

What data type are you using for `EquipCalDueDate` and `EquipApprovedDate`? If a character type then what format are the dates in? — Nick, Apr 25 '18 at 01:42
I just updated my question attached with both table data and design for your view. — Maheswarha Rajagopal, Apr 25 '18 at 01:52

score 0 · Accepted Answer · answered Apr 25 '18 at 02:29

You can actually do the entire table update with one query:

declare @remaining int, @total int; update events 
set @remaining=datediff(day, getdate(), equipcalduedate),
@total=datediff(day, equipapproveddate, equipcalduedate),
themecolor=(case
   when @remaining < @total/3 then 'red'
   when @remaining < 2*@total/3 then 'orange'
   else 'green'
end)

For the sample data you provided (plus one other value I added to demonstrate 'orange' theme color, the output data from a select:

select EventId, EquipApprovedDate, EquipCalDueDate, ThemeColor, datediff(day, getdate(), equipcalduedate) as remaining,
datediff(day, equipapproveddate, equipcalduedate) as total
from events

looks like this, which I think is what you want:

EventId  EquipApprovedDate  EquipCalDueDate  ThemeColor  remaining  total
1        2018-04-17         2018-05-31       green       36         44 
2        2018-04-11         2018-04-27       red         2          16 
3        2020-04-20         2020-05-28       green       764        38 
4        2018-04-11         2018-05-15       orange      20         34 
8        2019-04-20         2019-05-31       green       401        41

DUDE, YOU'RE GENIUS! It works like charm. As I'm working on a MVC project, I just stored the query above in Stored Procedures, and then called it when the page loads. THANKS A TON man! — Maheswarha Rajagopal, Apr 25 '18 at 03:43

How to run same query on every single row of a table? (MVC)

1 Answers1