Select count for multiple dates

Question

I have 3 tables: People, Group, and a PeopleGroup join table. PeopleGroup has a start and end date. Using linq-to-sql, I'm trying to write a query that will pull the count of group members on a 1-week interval for any given group over a specified date range. The end result would look something like (for group x in January):

Date        People Count
1/3/2009    445
1/10/2009   420
1/17/2009   426
1/24/2009   435
1/31/2009   432

Is that possible or do I need to write separate queries for each group/week combination?

Answer 1

I recently had to do something similar. After a little research I found that it would just be easier to process the data with c#.

In your situation, you could calculate a canonical date given a date in your data and group on that canonical date.

Answer 2

This is what I would do in T-SQL. See if you can translate to LINQ speak.

SELECT 
    DATEADD (D, -1 * DATEPART (dw, DateTimeColumn), DateTimeColumn), 
    COUNT (*)
FROM DBO.UserTable
GROUP BY 
    DATEADD (D, -1 * DATEPART (dw, DateTimeColumn), DateTimeColumn)

Answer 3

Maybe you can try something like this. I don't know whether it works on LINQ to SQL but it works on LINQ to objects. First day of the week is sunday in this example but it can be changed.

        class Foo
        {
            public DateTime Date { get; set; }
        }

        var foos = new[] 
            {
                new Foo { Date = DateTime.Now },
                new Foo { Date = new DateTime(2009, 7, 3)},
                new Foo { Date = new DateTime(2009, 6, 20)},
                new Foo { Date = new DateTime(2009, 6, 21)},
                new Foo { Date = new DateTime(2009, 7, 2)}
            };

            var query = from foo in foos
                        group foo by foo.Date.AddDays(-(int)foo.Date.DayOfWeek) into g
                        select new
                        {
                            Count = g.Count(),
                            Date = g.Key
                        };

            foreach (var foo in query)
                Console.WriteLine("People count: {0} Date: {1}", foo.Count, foo.Date);