Comparing values in historic manner between 2 lists and increment the values if element is from first list or decerement if it's from second list - C#

Question

I need to prepare a chart wherein I'm required to show 3 lines. One for showing new issues for a week, second for closed issues for a week and third for total accumulative open issues from first week to last week.

For this reason, I have prepared a query and was able to create 2 separate lists successfully - one list maintains the weekly count of new issues and second list maintains the weekly count of closed issues.

Here is the sample data for first list (one which maintains new issues) :

        [0]: { Week = {6/14/2015 12:00:00 AM}, Count = 1 }
        [1]: { Week = {3/5/2017 12:00:00 AM}, Count = 1 }
        [2]: { Week = {5/21/2017 12:00:00 AM}, Count = 4 }
        [3]: { Week = {6/4/2017 12:00:00 AM}, Count = 7 }
        [4]: { Week = {6/11/2017 12:00:00 AM}, Count = 4 }
        [5]: { Week = {6/25/2017 12:00:00 AM}, Count = 7 }
        [6]: { Week = {7/9/2017 12:00:00 AM}, Count = 3 }

From the above data, I get the total count of open issues for a particular week.

Note: For both these lists the Week values contain date which falls on Sunday. As I need the week to start on Monday while displaying data in the chart.

Similarly for sample data for second list (one which maintains closed issues) :

[0]: { Week = {12/13/2015 12:00:00 AM}, Count = 1 }
[1]: { Week = {7/9/2017 12:00:00 AM}, Count = 3 }
[2]: { Week = {6/18/2017 12:00:00 AM}, Count = 2 }
[3]: { Week = {7/23/2017 12:00:00 AM}, Count = 8 }
[4]: { Week = {10/1/2017 12:00:00 AM}, Count = 6 }
[5]: { Week = {8/6/2017 12:00:00 AM}, Count = 3 }
[6]: { Week = {9/17/2017 12:00:00 AM}, Count = 1 }

From the above data, I get total count of closed issues for a particular week.

Here's the code for these lists :

var openIssuesList = getDetails.Where(x => x.ChangedTo == "Open").Select(x => new { Week = x.Date.AddDays(x.Date.DayOfWeek == DayOfWeek.Sunday ? 0 : 7 - (int)x.Date.DayOfWeek).Date, Detail = x }).GroupBy(x => x.Week).Select(x => new { Week = x.Key, Count = x.Count() }).ToList();


var closedIssuesList = getDetails.Where(x => x.ChangedTo == "Closed").Select(x => new { Week = x.Date.AddDays(x.Date.DayOfWeek == DayOfWeek.Sunday ? 0 : 7 - (int)x.Date.DayOfWeek).Date, Detail = x }).GroupBy(x => x.Week).Select(x => new { Week = x.Key, Count = x.Count() }).ToList();

Now the final piece that remains is to create a new list by using the values from these 2 lists which should contain data for total open issues for a week.

Explanation :

I need to compare the week values from the above 2 lists in historic manner(oldest to newest).
Fetch the oldest week value of all from both of these lists. (From the sample data above it should be 6/14/2015 which is in openIssuesList.) Keep fetching weeks (from oldest to newest) in this manner from the above 2 lists.
If week values are from first list i.e openIssuesList then increment the Count value by adding it's Count value with the Count value of previously fetched element (now present in the new third list) if any.
If week values are from second list i.e closedIssuesList then decrement the Count value by subtracting it's Count value with the Count value of previously fetched element (now present in the new third list) if any.
If the week values are equal (like 7/9/2017 from sample data), then first add the Count value of the openIssues list with the previously fetched element (now present in the new third list) if any and then subtract this newly calculated value with the Count value of the closedIssues list .

So from the above provided sample data here's how the new list should like :

[0]: { Week = {6/14/2015 12:00:00 AM}, Count = 1 }    // 0+1 = 0 : Oldest week value of all - (fetched from openIssuesList)
[1]: { Week = {12/13/2015 12:00:00 AM}, Count = 0 }  // 1-1 = 0  (fetched from closedIssuesList)
[2]: { Week = {3/5/2017 12:00:00 AM}, Count = 1 }  // 0+1 = 1 - (fetched from openIssuesList)
[3]: { Week = {5/21/2017 12:00:00 AM}, Count = 5 }  // 1+4 = 5 - (fetched from openIssuesList)
[4]: { Week = {6/4/2017 12:00:00 AM}, Count = 12 }  // 5+7 = 12 - (fetched from openIssuesList)
[5]: { Week = {6/11/2017 12:00:00 AM}, Count = 16}  // 12+4 = 16 - (fetched from openIssuesList)
[6]: { Week = {6/18/2017 12:00:00 AM}, Count = 14 }  // 16-2 = 14  (fetched from closedIssuesList)
[7]: { Week = {6/25/2017 12:00:00 AM}, Count = 21 } //14+7 = 21  (fetched from openIssuesList)
[8]: { Week = {7/9/2017 12:00:00 AM}, Count = 21 }   // These is common week from both lists. So 20 + (openIssuesList Count value) - (closedIssuesList Count value) i.e [21 + 3 - 3 = 21].
[9]: { Week = {7/23/2017 12:00:00 AM}, Count = 13 }  // 21-8 = 13  (fetched from closedIssuesList)
[10]: { Week = {8/6/2017 12:00:00 AM}, Count = 10 }   // 13-3 = 10  (fetched from closedIssuesList)
[11]: { Week = {9/17/2017 12:00:00 AM}, Count = 9 }    // 10-1 = 9  (fetched from closedIssuesList)
[12]: { Week = {10/1/2017 12:00:00 AM}, Count = 3 }    // 9-6 = 3  (fetched from closedIssuesList)

From the above data kindly see the 8th element of this list. The week in this list 7/9/2017 was common from both the openIssuesList (6th element) and closedIssuesList (2nd element)

What would be the code to achieve this list?

Note: I have to remove the Time element value in my code from all the DateTime values in these lists. Hence all the date values appear with 12:00:00 AM in these lists.

You've fixed your calculations and they are now correct except the last two lines: the counts should be 9 (10 - 1) and 3 (9 - 6). — Dan Dumitru, Nov 04 '17 at 14:58

Sergey Berezovskiy · Accepted Answer · 2017-11-04T15:00:00.987

Group all issues by week and then calculate count depending on current total count and sum of open and closed issues for given week:

int totalCount = 0;
var issuesByWeek = from issue in getDetails
                   where issue.ChangedTo == "Open" || issue.ChangedTo == "Closed"
                   group issue by issue.Date.EndOfWeek() into g
                   orderby g.Key
                   select new
                   {
                       Week = g.Key,
                       Count = totalCount += g.Sum(i => i.ChangedTo == "Open" ? 1 : -1)
                   };

You don't need two lists for calculating this stats. If you need these lists for another purpose, then you can simply concat them and use

from issue in openIssuesList.Concat(closedIssuesList)

Test data for three weeks

var issues = new[]
{
    new Issue { Date = DateTime.Today.AddDays(-16), ChangedTo = "Open" },
    new Issue { Date = DateTime.Today.AddDays(-15), ChangedTo = "Unknown" },
    new Issue { Date = DateTime.Today.AddDays(-15), ChangedTo = "Open" },
    new Issue { Date = DateTime.Today.AddDays(-9), ChangedTo = "Closed" },
    new Issue { Date = DateTime.Today.AddDays(-8), ChangedTo = "Open" },
    new Issue { Date = DateTime.Today.AddDays(-6), ChangedTo = "Closed" },
    new Issue { Date = DateTime.Today.AddDays(-5), ChangedTo = "Closed" }
};

Output:

[
  { "Week": "2017-10-22T00:00:00+03:00", "Count": 2 }, // 0 + 2
  { "Week": "2017-10-29T00:00:00+03:00", "Count": 1 }, // 2 + 1 - 2
  { "Week": "2017-11-05T00:00:00+03:00", "Count": 0 }  // 1 - 1
]

Extension method is used for readability:

public static class DateTimeExtensions
{
    public static DateTime EndOfWeek(this DateTime date) => 
       date.AddDays(date.DayOfWeek == DayOfWeek.Sunday ? 0 : 7 - (int)date.DayOfWeek).Date;
}

Note: instead of string consider to use enum for issues statuses

Chosing this as the correct answer as it's more efficient/faster then @Dan Dumitru answer and doesn't require any change to my existing code. — Mr.Human, Nov 04 '17 at 17:06

score 1 · Answer 2 · answered Nov 04 '17 at 14:32

Based on my previous answer, I've expanded the helper class

public class WeekCount
{
    public DateTime Week { get; set; }
    public int Count { get; set; }
    public bool IsOpen { get; set; }
}

, you still need to modify your selects to use it

.Select(x => new WeekCount { Week = x.Key, Count = x.Count() })

, and the code becomes:

var totalIssuesList = openIssuesList.Select(o => new WeekCount { Week = o.Week, Count = o.Count, IsOpen = true }).ToList();

foreach (var closedWeekCount in closedIssuesList)
{
    var totalWeekCount = totalIssuesList.FirstOrDefault(owc => owc.Week == closedWeekCount.Week);
    if (totalWeekCount != null)
    {
        totalWeekCount.Count = totalWeekCount.Count - closedWeekCount.Count;
    }
    else
    {
        totalIssuesList.Add(new WeekCount { Week = closedWeekCount.Week, Count = closedWeekCount.Count, IsOpen = false });
    }
}

totalIssuesList = totalIssuesList.OrderBy(twc => twc.Week).ToList();

var currentCount = 0;

foreach (var totalWeekCount in totalIssuesList)
{
    if (totalWeekCount.IsOpen)
    {
        currentCount += totalWeekCount.Count;
    }
    else
    {
        currentCount -= totalWeekCount.Count;
    }

    totalWeekCount.Count = currentCount;
}

Do note that some of your calculations are wrong, it should be, for example:

[4]: { Week = {6/4/2017 12:00:00 AM}, Count = 12 } // 5+7 = 12

Comparing values in historic manner between 2 lists and increment the values if element is from first list or decerement if it's from second list - C#

2 Answers2

Linked