Get dates when quantity was missing
Trying to figure out how to get the result of date range when quantity was out of stock with LINQ C #
Suppose I have a table result that looks like this:
EventDate | Qty
2014-02-03 | 6
2014-02-04 | -1
2014-02-05 | -2
2014-02-06 | 2
2014-02-07 | -1
2014-02-08 | -2
2014-02-09 | -3
2014-02-10 | 5
Now I want to get date range when qty was minus 0 in stock like this
FromDate | ToDate
2014-02-04 | 2014-02-05
2014-02-07 | 2014-02-09
Can anyone please help me how to do this?
UPDATE
I know I can do this with query multiplication, but I would like to do it in only one LINQ query if possible.
source to share
For an alternative using only built-in functions,
The strategy here is to select all dates where the count is less than zero, and for each of those dates, do a subquery that builds a list of all dates after the current date with the count also less than zero. Using TakeWhile
, it will stop until the next date with a negative value. It then selects the maximum value that is correct for the end date of the range. The final step is GroupBy
to remove all days after the start of the "out of stock" range that match the same end date to leave you with a specific range of stock date ranges.
As below, it relies on inventory levels to be sorted chronologically as input.
public class StockLevel
{
public DateTime Date { get; set; }
public int Quantity { get; set; }
}
static void Main(string[] args)
{
List<StockLevel> stockLevels = new List<StockLevel>()
{
new StockLevel() { Date = DateTime.Parse("03-Feb-2014"), Quantity = 6 },
new StockLevel() { Date = DateTime.Parse("04-Feb-2014"), Quantity = -1 },
new StockLevel() { Date = DateTime.Parse("05-Feb-2014"), Quantity = -2 },
new StockLevel() { Date = DateTime.Parse("06-Feb-2014"), Quantity = 2 },
new StockLevel() { Date = DateTime.Parse("07-Feb-2014"), Quantity = -1 },
new StockLevel() { Date = DateTime.Parse("08-Feb-2014"), Quantity = -2 },
new StockLevel() { Date = DateTime.Parse("09-Feb-2014"), Quantity = -3 },
new StockLevel() { Date = DateTime.Parse("10-Feb-2014"), Quantity = 5 },
};
var outOfStockDates = stockLevels
.Where(a => a.Quantity < 0)
.Select(a => new
{
S1 = a.Date,
S2 = stockLevels
.Where(c => c.Date >= a.Date)
.TakeWhile(b => b.Quantity < 0)
.Select(b => b.Date).Max()
})
.GroupBy(a => a.S2, a => a.S1, (S2, S1S) => new { FromDate = S1S.Min(), ToDate = S2 });
Console.ReadKey();
}
source to share
You can write your own function to go to Linq expression.
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApplication
{
public class Class1
{
public static void Main(string[] args)
{
IEnumerable<QuantityDate> quantityDates = GetQuantityDates();//I'm sure you already have some way of retrieving these, via EF or Linq to SQL etc.
var results = quantityDates.Where(qd => qd.Qty < 0).CombineResults(); //This is the main Linq expression
foreach (var result in results)
{
Console.WriteLine("From Date: {0} To Date: {1}", result.FromDate, result.ToDate);
}
Console.WriteLine("Press any key to exit.");
Console.ReadKey();
}
//Just to see the data for the moment. You'll probably get this data via EF or Linq to SQL
public static List<QuantityDate> GetQuantityDates()
{
List<QuantityDate> seed = new List<QuantityDate>()
{
new QuantityDate() { EventDate = new DateTime(2014, 2, 3), Qty = 6 },
new QuantityDate() { EventDate = new DateTime(2014, 2, 4), Qty = -1 },
new QuantityDate() { EventDate = new DateTime(2014, 2, 5), Qty = -2 },
new QuantityDate() { EventDate = new DateTime(2014, 2, 6), Qty = 2 },
new QuantityDate() { EventDate = new DateTime(2014, 2, 7), Qty = -1 },
new QuantityDate() { EventDate = new DateTime(2014, 2, 8), Qty = -2 },
new QuantityDate() { EventDate = new DateTime(2014, 2, 9), Qty = -3 },
new QuantityDate() { EventDate = new DateTime(2014, 2, 10), Qty = 5 }
};
return seed;
}
}
public static class Extensions
{
//This is where the magic happens, and we combine the results
public static List<OutOfStockRange> CombineResults(this IEnumerable<QuantityDate> input)
{
List<OutOfStockRange> output=new List<OutOfStockRange>();
OutOfStockRange lastEntered = null;
foreach(var qd in input.OrderBy(qd => qd.EventDate))
{
if(lastEntered != null && lastEntered.ToDate.AddDays(1) == qd.EventDate)
{
lastEntered.ToDate = qd.EventDate;
}
else
{
lastEntered =new OutOfStockRange(){FromDate = qd.EventDate, ToDate = qd.EventDate};
output.Add(lastEntered);
}
}
return output;
}
}
//This class represents the input data
public class QuantityDate
{
public DateTime EventDate { get; set; }
public int Qty { get; set; }
}
//This class represents the output data
public class OutOfStockRange
{
public DateTime FromDate { get; set; }
public DateTime ToDate { get; set; }
}
}
source to share
This is how I would do it.
Start with your data:
var stockQuantities = new []
{
new { Date = new DateTime(2014, 2, 3), Qty = 6 },
new { Date = new DateTime(2014, 2, 4), Qty = -1 },
new { Date = new DateTime(2014, 2, 5), Qty = -2 },
new { Date = new DateTime(2014, 2, 6), Qty = 2 },
new { Date = new DateTime(2014, 2, 7), Qty = -1 },
new { Date = new DateTime(2014, 2, 8), Qty = -2 },
new { Date = new DateTime(2014, 2, 9), Qty = -3 },
new { Date = new DateTime(2014, 2, 10), Qty = 5 },
};
Then a query for out-of-stock records:
var outOfStock = stockQuantities.Where(x => x.Qty < 0);
Now use Aggregate
to plot the results:
var outOfStockRanges =
outOfStock
.Skip(1)
.Aggregate(
outOfStock
.Take(1)
.Select(x => new { From = x.Date, To = x.Date })
.ToList(),
(a, x) =>
{
if (a.Last().To.AddDays(1.0) == x.Date)
a[a.Count - 1] = new { From = a.Last().From, To = x.Date };
else
a.Add(new { From = x.Date, To = x.Date });
return a;
});
As a result, I get:
source to share
I came up with the following:
var src_objects = new []
{
new {date = DateTime.Parse("2014-02-03"), qty = 6},
new {date = DateTime.Parse("2014-02-04"), qty = -1},
new {date = DateTime.Parse("2014-02-05"), qty = -2},
new {date = DateTime.Parse("2014-02-06"), qty = 2},
new {date = DateTime.Parse("2014-02-07"), qty = -1},
new {date = DateTime.Parse("2014-02-08"), qty = -2},
new {date = DateTime.Parse("2014-02-09"), qty = -3},
new {date = DateTime.Parse("2014-02-10"), qty = 5}
};
int i = 0;
var ranges = src_objects
.OrderBy(key => key.date)
.Select(obj =>
{
if (obj.qty > 0)
{
++i;
return new { date = obj.date, group_key = 0 };
}
else
return new { date = obj.date, group_key = i };
})
.Where(obj => obj.group_key != 0)
.GroupBy(obj => obj.group_key)
.Select(g => new { fromdate = g.First().date, todate = g.Last().date });
ranges
.ToList()
.ForEach(range => Console.WriteLine(string.Format("{0} - {1}", range.fromdate, range.todate.Date)));
source to share