Comparing values between 2 lists and subtracting values for common elements - 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 open issues for a week. For this reason, I have prepared a query and was able to create 2 separate lists successfully - one list maintains weekly count of new issues and second list maintains 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 for 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 from 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 = 5 }

From the above data I get for 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.
If the week values are equal, then calculate the difference between the Count values of those particular week from both lists.
Save the Week value and count value (after calculating the difference) in this new list.
If week values do not match then pick such values (both Week and Count) and store them as is in this new 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 }    // As is value from first list - openIssuesList
[1]: { Week = {12/13/2015 12:00:00 AM}, Count = 1 }  // As is value from second list - closedIssuesList
[2]: { Week = {3/5/2017 12:00:00 AM}, Count = 1 }  // As is value from first list - openIssuesList
[3]: { Week = {5/21/2017 12:00:00 AM}, Count = 4 }  // As is value from first list - openIssuesList
[4]: { Week = {6/4/2017 12:00:00 AM}, Count = 7 }  // As is value from first list - openIssuesList
[5]: { Week = {6/11/2017 12:00:00 AM}, Count = 4 }  // As is value from first list - openIssuesList
[6]: { Week = {6/18/2017 12:00:00 AM}, Count = 2 }  // As is value from second list - closedIssuesList
[7]: { Week = {6/25/2017 12:00:00 AM}, Count = 7 }   // As is value from first list - openIssuesList
[8]: { Week = {7/9/2017 12:00:00 AM}, Count = 0 }   // These is common week from both lists. Hence we calculate the difference between count values. So 3-3 = 0.
[9]: { Week = {7/23/2017 12:00:00 AM}, Count = 8 }  // As is value from second list - closedIssuesList
[10]: { Week = {8/6/2017 12:00:00 AM}, Count = 3 }   // As is value from second list - closedIssuesList
[11]: { Week = {9/17/2017 12:00:00 AM}, Count = 5 }    // As is value from second list - closedIssuesList
[12]: { Week = {10/1/2017 12:00:00 AM}, Count = 6 }    // As is value from second list - 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 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.

Dan Dumitru · Accepted Answer · 2017-11-04T09:06:16.817

1

If you don't absolutely need a LINQ solution, you can create a helper class

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

, modify your selects using this class

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

, and then do it simply:

var totalIssuesList = openIssuesList.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(closedWeekCount);
    }
}

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

edited Nov 04 '17 at 09:06

answered Nov 04 '17 at 08:52

Dan Dumitru

5,348
1
33
43

can you also look at my other similar question but with a different end result requirement? Link : https://stackoverflow.com/questions/47111604/comparing-values-in-historic-manner-between-2-lists-and-increment-the-values-if – Mr.Human Nov 04 '17 at 14:10
Chosing this is as the correct answer as it's bit faster then @Mike Mat's answer – Mr.Human Nov 04 '17 at 14:12

Mike Mat · Answer 2 · 2017-11-04T22:29:24.950

To do this using LINQ, you could consider full outer join of those 2 lists.

Helper class:

    public class IssueCount
    {
        public DateTime Week { get; set; }
        public int Count { get; set; }
    }

then

        // Union left outer join and right outer join to perform full outer join
        // https://stackoverflow.com/a/5491381/5682608
        var leftOuterJoin = from newIssue in newIssues
                            join closedIssue in closedIssues
                            on newIssue.Week equals closedIssue.Week
                            into temp
                            from closedIssue in temp.DefaultIfEmpty(new IssueCount { Week = newIssue.Week, Count = 0 })
                            select new IssueCount
                            {
                                Week = newIssue.Week,
                                Count = newIssue.Count - closedIssue.Count
                            };

        var rightOuterJoin = closedIssues.Where(issue => !newIssues.Select(newIssue => newIssue.Week).Contains(issue.Week));

        // Modified: Following code can generate duplicated entries when
        // 2 IssueCounts of the same Week have different values in Count property
        // 
        //var rightOuterJoin = from closedIssue in closedIssues
        //                     join newIssue in newIssues
        //                     on closedIssue.Week equals newIssue.Week
        //                     into temp
        //                     from newIssue in temp.DefaultIfEmpty(new IssueCount { Week = closedIssue.Week, Count = 0 })
        //                     select new IssueCount
        //                     {
        //                         Week = closedIssue.Week,
        //                         Count = closedIssue.Count - newIssue.Count
        //                     };

        var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);

        foreach (var issue in fullOuterJoin.OrderBy(i => i.Week))
        {
            Console.WriteLine($"{issue.Week.ToString("MM/dd/yyyy")} : {issue.Count}");
        }

This should output

06/14/2015 : 1
12/13/2015 : 1
03/05/2017 : 1
05/21/2017 : 4
06/04/2017 : 7
06/11/2017 : 4
06/18/2017 : 2
06/25/2017 : 7
07/09/2017 : 0
07/23/2017 : 8
08/06/2017 : 3
09/17/2017 : 5
10/01/2017 : 6

can you also look at my other similar question but with a different end result requirement? Link : https://stackoverflow.com/questions/47111604/comparing-values-in-historic-manner-between-2-lists-and-increment-the-values-if — Mr.Human, Nov 04 '17 at 14:10

Comparing values between 2 lists and subtracting values for common elements - C#

2 Answers2

Linked