views:

99

answers:

2

I currently have the following code to generate a sales report over the last 30 days. I'd like to know if it would be possible to use linq to generate this report in one step instead of the rather basic loop I have here.

For my requirement, every day needs to return a value to me so if there are no sales for any day then a 0 is returned.

Any of the Sum linq examples out there don't explain how it would be possible to include a where filter so I am confused on how to get the total amount per day, or a 0 if no sales, for the last days I pass through.

Thanks for your help, Rich

    //setup date ranges to use
    DateTime startDate = DateTime.Now.AddDays(-29);
    DateTime endDate = DateTime.Now.AddDays(1);
    TimeSpan startTS = new TimeSpan(0, 0, 0);
    TimeSpan endTS = new TimeSpan(23, 59, 59);

    using (var dc = new DataContext())
    {
        //get database sales from 29 days ago at midnight to the end of today
        var salesForDay = dc.Orders.Where(b => b.OrderDateTime > Convert.ToDateTime(startDate.Date + startTS) && b.OrderDateTime <= Convert.ToDateTime(endDate.Date + endTS));

        //loop through each day and sum up the total orders, if none then set to 0
        while (startDate != endDate)
        {
            decimal totalSales = 0m;
            DateTime startDay = startDate.Date + startTS;
            DateTime endDay = startDate.Date + endTS;
            foreach (var sale in salesForDay.Where(b => b.OrderDateTime > startDay && b.OrderDateTime <= endDay))
            {
                totalSales += (decimal)sale.OrderPrice;
            }

            Response.Write("From Date: " + startDay + " - To Date: " + endDay + ". Sales: " + String.Format("{0:0.00}", totalSales) + "<br>");

            //move to next day
            startDate = startDate.AddDays(1);
        }
    }

EDIT: Johannes answer was a great way to handle my query. Below is an adjustment to the code to get it working for this example in case anyone else has this issue. This will perform an outer join from the allDays table and return 0 values when there is no sales for that day.

var query = from d in allDays
                    join s in salesByDay on d equals s.Day into j
                    from s in j.DefaultIfEmpty()
                    select new { Day = d, totalSales = (s != null) ? s.totalSales : 0m };
A: 

I think that if your enumeration does not contain data for a day, you cannot return values for that day. The best I can think of is to create a list of Order objects with a zero value for each day and create a union with the result of your query. Here is what I came up with. However, I think looping through each group, checking if any day is "skipped", and returning zero for each day that is "skipped" is more straightforward than creating your own enumeration in memory (unless you want an enumeration with the "missing gaps" filled in). Please note that I'm basically assuming that for each group, you want to sum all the values for a single day.

List<Order> zeroList = new List<Order>();
while (startDate <= endDate)
{
  zeroList.Add(new Order { OrderDateTime = startDate, OrderPrice = 0 });
  startDate = startDate.AddDays(1)
}

var comboList = zeroList.Union(dc.Orders.Where(b => b.OrderDateTime > Convert.ToDateTime(startDate.Date + startTS) && b.OrderDateTime <= Convert.ToDateTime(endDate.Date + endTS))

var groupedTotalSales = comboList.GroupBy(b => b.OrderDateTime.Date)
  .Select(b => new { StartDate = Convert.ToDateTime(b.Key + startTS), EndDate = Convert.ToDateTime(b.Key + endTS), Sum = b.Sum(x => x.OrderPrice });

foreach (totalSale in groupedTotalSales)
  Response.Write("From Date: " + totalSale.StartDate + " - To Date: " + totalSale.EndDate + ". Sales: " + String.Format("{0:0.00}", (decimal)totalSale.Sum) + "<br/>");
Jimmy W
+1  A: 

You can group all your data by day and run sum over those groups. To fulfill the requirement of having a sum for each day, even those without order, you can either join a list of all the dates or simply use a loop to make sure all dates are included. Small hint: You don't need to set up the times explicitly if you compare by the DateTime.Date properties.

Here's the solution using a generator function (taken from the MoreLinq project):

public static partial class MoreEnumerable
{

    public static IEnumerable<TResult> GenerateByIndex<TResult>(Func<int, TResult> generator)
    {
        // Looping over 0...int.MaxValue inclusive is a pain. Simplest is to go exclusive,
        // then go again for int.MaxValue.
        for (int i = 0; i < int.MaxValue; i++)
        {
            yield return generator(i);
        }
        yield return generator(int.MaxValue);
    }

}

public class MyClass
{
    private void test()
    {
        DateTime startDate = DateTime.Now.AddDays(-29);
        DateTime endDate = DateTime.Now.AddDays(1);

        using (var dc = new DataContext())
        {
            //get database sales from 29 days ago at midnight to the end of today
            var salesForPeriod = dc.Orders.Where(b => b.OrderDateTime > startDate.Date  && b.OrderDateTime <= endDate.Date);

            var allDays = MoreEnumerable.GenerateByIndex(i => startDate.AddDays(i)).Take(30);

            var salesByDay = from s in salesForPeriod
                        group s by s.OrderDateTime.Date into g
                        select new {Day = g.Key, totalSales = g.Sum(x=>(decimal)x.OrderPrice};

            var query = from d in allDays
                        join s in salesByDay on s.Day equals d
                        select new {Day = s.Day , totalSales = (s != null) ? s.totalSales : 0m;


            foreach (var item in query)
            {
                Response.Write("Date: " +item.Day.ToString() " Sales: " + String.Format("{0:0.00}", item.totalSales) + "<br>");
            }


        }
    }
}
Johannes Rudolph
I came up with that solution too, but the request seems to include days for which no rows are returned. In such a case, the value should be zero for such a day.Using the query result, I could loop through each day and check to see if the result contains a sum for that day. If so, I'll return the sum. Otherwise, I'll return zero. However, this isn't a one-step LINQ query as requested.
Jimmy W
I see your edit. Very nice.
Jimmy W
@Jimmy: Thanks, but I think this one is even nicer.
Johannes Rudolph
Very nice solution. I really like where this is going! Just a small thing, on the 'var query' linq statement, I get errors because of the 'into j' part of the join. If I remove that, the code compiles but no results display. If I leave it on then the 'select' line throws up errors saying s is undefined. Any thoughts? Thanks again!
Richard Reddy
@Richard: fixed.
Johannes Rudolph
Thanks for the update Johannes. I managed to get this working once I put in a few tweaks. I have included the working var query statment in my question above for anyone else having this issue. Thanks again for your help :)
Richard Reddy