0

How can I show data for tickets which were created in Last Week? I tried searching on google, but unable to make the code work. So, what I want is all chat logs that were created in Lastweek. Lastweek starts from Monday, and ends on Sunday.

  1. Chat Model Class
public class Chat
    {
        [Key]
        public int ChatId { get; set; }

        [Required]
        public string CustName { get; set; }

        
        public string Query { get; set; }

        public string Resolution { get; set; }

        [Required]
        public DateTime ChatStartDateTime { get; set; }

        public DateTime? ChatCreateDateTime { get; set; }

        public DateTime? ChatEndDateTime { get; set; }

        public int Id { get; set; }
        public string Username { get; set; }
        public string FirstName { get; set; }
        public string Email { get; set; }

        [ForeignKey("Id")]
        public virtual User User { get; set; }


    }
  1. Chat Log Controller code is here
var getSunday = DateTime.Now.Date.AddDays(-7);
                var getSat = DateTime.Now.Date.AddDays(7);

                //var dayOfWeek = DayOfWeek.Monday;

                ViewBag.lastWeek = db.Chats.Where(x => x.ChatCreateDateTime >= getSunday && x.ChatCreateDateTime <= getSat).Count();

Any help is much appreciated.

Riyaz Shaikh
  • 83
  • 11

1 Answers1

1

You need to find the start of the week (the most recent Monday) and then query based off of that. Here is a solution using an extension method found here on Stack Overflow:

Extension method used to get the date of the most recent Monday:

public static class DateTimeExtensions
{
    public static DateTime StartOfWeek(this DateTime dt, DayOfWeek startOfWeek)
    {
        int diff = (7 + (dt.DayOfWeek - startOfWeek)) % 7;
        return dt.AddDays(-1 * diff).Date;
    }
}

then your controller code would look something like:

var mostRecentMonday = DateTime.Now.StartOfWeek(DayOfWeek.Monday);//get week start of most recent Monday morning
var weekEnd = mostRecentMonday.AddDays(7).AddSeconds(-1); //will return the end of the day on Sunday
ViewBag.lastWeek = db.Chats.Where(x => x.ChatCreateDateTime >= mostRecentMonday && x.ChatCreateDateTime <= weekEnd).Count();

worth noting that if you're just looking at the most recent week then you don't even need the x.ChatCreateDateTime <= weekEnd part of the query.

Then if you wanted to go get historical data (i.e: this same data but for the weekspan that occurred 1 week ago). You can simply change this:

var mostRecentMonday = DateTime.Now.StartOfWeek(DayOfWeek.Monday);

to this:

//subtract 1 weeks * 7 days per week = 7 days

var mostRecentMonday = DateTime.Now.AddDays(-7).StartOfWeek(DayOfWeek.Monday);
GregH
  • 5,125
  • 8
  • 55
  • 109
  • Appreciate your help. I tried above code, but it is not giving me tickets for LastWeek, it is giving me tickets for this week. How can I show ticket which were worked in between Oct 12 2020 and Oct 18 2020. Thank you again for detailed explanation and knowledge sharing. – Riyaz Shaikh Oct 20 '20 at 17:17
  • @RiyazShaikh i've updated the example at the bottom of my post to show the week you're looking for – GregH Oct 20 '20 at 17:41
  • I am extremely sorry to have missed your response. Yes, I did exactly the same, and it works for me. Thank you for your swift response, and help – Riyaz Shaikh Oct 21 '20 at 10:18
  • How can We show data for Current month? Thank you in advance – Riyaz Shaikh Dec 06 '20 at 14:48