public ContentResult RevByAdvertiser() { int[] timearray = new int[96]; int[,] finaltimearray = new int[96, 59]; string[] daySlice = new string[59]; int counter = daySlice.Length; for (int i = 0; i < daySlice.Length; i++) { var day = DateTime.UtcNow.AddDays(-(counter - i)); daySlice[i] = Convert.ToDateTime(day).ToString("yyyy-MM-dd"); } DateTime startDate = DateTime.UtcNow.Date.AddDays(-61); DateTime endDate = DateTime.UtcNow.Date.AddDays(0); string Query = @"SELECT date as 'date', revenuestream as revenuestream, SUM(revenue) AS revenue FROM [Reports].[dbo].[DailyRevenue] WITH (NOLOCK) WHERE 1=1 AND date >= dateAdd(DAY, -60, GETDATE()) AND date <= GETDATE() AND category = 'push' group by revenuestream, date order by revenuestream, date" ; // Since table format is (date, revenuestream, revenue), we'll model our Json Array likewise // Kinda like a dictionary JArray Date = new JArray(); JArray Data = new JArray(); JArray SingleDay = new JArray(); JArray Revenue = new JArray(); JArray DayTime = new JArray(); JObject Record = new JObject(); JObject Day = new JObject(); using (var sqlConn = new SqlConnection(DataSource)) { sqlConn.Open(); SqlCommand command = new SqlCommand(string.Format(Query, startDate.ToString(), endDate.ToShortDateString()), sqlConn); string revenuestream = "tt"; using (var reader = command.ExecuteReader()) { if (reader != null && reader.HasRows) { while (reader.Read()) { string temp = Convert.ToDateTime(reader["date"]).ToString("yyyy-MM-dd"); if (revenuestream != "tt" && revenuestream != Convert.ToString(reader["revenuestream"])) { while (DayTime.Count < daySlice.Length) { DayTime.Add(daySlice[DayTime.Count]); Revenue.Add(0); } Record = new JObject(); Record.Add("Advertiser", revenuestream); Record.Add("Revenue", Revenue); Record.Add("Date", DayTime); Data.Add(Record); DayTime = new JArray(); Revenue = new JArray(); } revenuestream = Convert.ToString(reader["revenuestream"]); if (daySlice.Length != DayTime.Count) { while (daySlice[DayTime.Count] != temp) { DayTime.Add(daySlice[DayTime.Count]); Revenue.Add(0); } } DayTime.Add(Convert.ToDateTime(reader["date"]).ToString("yyyy-MM-dd")); Revenue.Add(Convert.ToInt32(reader["revenue"] is DBNull ? 0 : reader["revenue"])); } } } Record = new JObject(); while (DayTime.Count < daySlice.Length) { DayTime.Add(daySlice[DayTime.Count]); Revenue.Add(0); } Record.Add("Advertiser", revenuestream); Record.Add("Revenue", Revenue); Record.Add("Date", DayTime); Data.Add(Record); sqlConn.Close(); } return(new ContentResult { Content = Data.ToString(), ContentType = "application/json" }); }