none
How to search for date and time and add a value to list

    Question

  • I have this list in C# and how can I check for missing dates between row[0] and row[1] and then add zero value for 08:00:00?

    Or, how can I iterate through this list comparing values of column[2] in every row?

    I have copied this list into new one with dates converted to date ticks (636266016000000000).

    01;-ex;01.04.2017 07:00:00;1;
    01;-ex;01.04.2017 09:00:00;13;
    01;-ex;01.04.2017 10:00:00;73;
    01;+in;01.04.2017 07:00:00;22;
    01;+in;01.04.2017 08:00:00;12;
    01;+in;01.04.2017 09:00:00;12;
    01;+in;01.04.2017 10:00:00;33;

    It should look like this:


    01;1;22;01.04.2017 07:00:00 01;0;12;01.04.2017 08:00:00 01;13;12;01.04.2017 09:00:00 01;73;33;01.04.2017 10:00:00



    Friday, April 7, 2017 11:45 AM

Answers

  • Hi,

    So we don't know what ex or in represent, but something like this should do the trick...

    In summary iterate over the list, compare the current date to the last, if there's a difference of more than an hour add it to a new list of 'missing' elements.

    At the end of the list check to see  if there's any missing elements, if there are add and sort the list.

    Caveats:

    1. I don't know the semantics of your collection so the comparison and sorting needs to be refined
    2. If there's more than one element missing, this approach will happily ignore that!
    using System;
    using System.Collections.Generic;
    
    namespace MatchNumbers
    {
    	class MainClass
    	{
    		public static void Main(string[] args)
    		{
    			var samples = new List<string>(new[]{
    				"01;-ex;01.04.2017 07:00:00;1;",
    				"01;-ex;01.04.2017 09:00:00;13;",
    				"01;-ex;01.04.2017 10:00:00;73;",
    				"01;+in;01.04.2017 07:00:00;22;",
    				"01;+in;01.04.2017 08:00:00;12;",
    				"01;+in;01.04.2017 09:00:00;12;",
    				"01;+in;01.04.2017 10:00:00;33;"
    			});
    			var missing = new List<string>();
    			DateTime? lastDate = null;
    
    			for (var ctr = 0; ctr < samples.Count; ctr++)
    			{
    				var split = samples[ctr].Split(';');
    
    				if (split.Length > 3)
    				{
    					// Index 2 holds the date/time
    					var currentDate = DateTime.Parse(split[2]);
    					if (ctr > 0 && lastDate != null)
    					{
    						// Just for clarity;
    						var diff = (currentDate - lastDate.Value).TotalHours;
    						// More than an hour in the difference
    						if (diff > 1)
    							missing.Add($"01;-ex;{lastDate.Value.AddHours(1)};xx;");
    					}
    					lastDate = currentDate;
    				}
    
    			}
    
    			// Only add in after we've gone through everything
    			if (missing.Count > 0)
    			{
    				samples.AddRange(missing);
    				samples.Sort((x, y) =>
    				{
    					var xElement = x.Split(';');
    					var yElement = y.Split(';');
    					var xDate = DateTime.Parse(xElement[2]);
    					var yDate = DateTime.Parse(yElement[2]);
    					var xVal = xElement[1].ToLower() == "-ex" ? 0 : 1;
    					var yVal = yElement[1].ToLower() == "-ex" ? 0 : 1;
    
    					if (xVal != yVal)
    						return xVal.CompareTo(yVal);
    
    					return xDate.CompareTo(yDate);
    				});
    			}
    
    			foreach (var x in samples)
    				Console.WriteLine(x);
    			Console.ReadLine();
    		}
    	}
    }
    

    Regards,

    D

    ----

    If this answer helped please mark it as the answer or upvote it so that others can find it more easily

    • Proposed as answer by bowman_d Friday, April 7, 2017 1:48 PM
    • Marked as answer by bobis123 Monday, April 10, 2017 6:22 AM
    Friday, April 7, 2017 1:48 PM

All replies


  • What is row[0] and what is row[1]


    Success
    Cor


    Friday, April 7, 2017 12:44 PM
  • Hi,

    So we don't know what ex or in represent, but something like this should do the trick...

    In summary iterate over the list, compare the current date to the last, if there's a difference of more than an hour add it to a new list of 'missing' elements.

    At the end of the list check to see  if there's any missing elements, if there are add and sort the list.

    Caveats:

    1. I don't know the semantics of your collection so the comparison and sorting needs to be refined
    2. If there's more than one element missing, this approach will happily ignore that!
    using System;
    using System.Collections.Generic;
    
    namespace MatchNumbers
    {
    	class MainClass
    	{
    		public static void Main(string[] args)
    		{
    			var samples = new List<string>(new[]{
    				"01;-ex;01.04.2017 07:00:00;1;",
    				"01;-ex;01.04.2017 09:00:00;13;",
    				"01;-ex;01.04.2017 10:00:00;73;",
    				"01;+in;01.04.2017 07:00:00;22;",
    				"01;+in;01.04.2017 08:00:00;12;",
    				"01;+in;01.04.2017 09:00:00;12;",
    				"01;+in;01.04.2017 10:00:00;33;"
    			});
    			var missing = new List<string>();
    			DateTime? lastDate = null;
    
    			for (var ctr = 0; ctr < samples.Count; ctr++)
    			{
    				var split = samples[ctr].Split(';');
    
    				if (split.Length > 3)
    				{
    					// Index 2 holds the date/time
    					var currentDate = DateTime.Parse(split[2]);
    					if (ctr > 0 && lastDate != null)
    					{
    						// Just for clarity;
    						var diff = (currentDate - lastDate.Value).TotalHours;
    						// More than an hour in the difference
    						if (diff > 1)
    							missing.Add($"01;-ex;{lastDate.Value.AddHours(1)};xx;");
    					}
    					lastDate = currentDate;
    				}
    
    			}
    
    			// Only add in after we've gone through everything
    			if (missing.Count > 0)
    			{
    				samples.AddRange(missing);
    				samples.Sort((x, y) =>
    				{
    					var xElement = x.Split(';');
    					var yElement = y.Split(';');
    					var xDate = DateTime.Parse(xElement[2]);
    					var yDate = DateTime.Parse(yElement[2]);
    					var xVal = xElement[1].ToLower() == "-ex" ? 0 : 1;
    					var yVal = yElement[1].ToLower() == "-ex" ? 0 : 1;
    
    					if (xVal != yVal)
    						return xVal.CompareTo(yVal);
    
    					return xDate.CompareTo(yDate);
    				});
    			}
    
    			foreach (var x in samples)
    				Console.WriteLine(x);
    			Console.ReadLine();
    		}
    	}
    }
    

    Regards,

    D

    ----

    If this answer helped please mark it as the answer or upvote it so that others can find it more easily

    • Proposed as answer by bowman_d Friday, April 7, 2017 1:48 PM
    • Marked as answer by bobis123 Monday, April 10, 2017 6:22 AM
    Friday, April 7, 2017 1:48 PM