Clever Geek Handbook

Select counter for multiple dates

I have 3 tables: People Group, PeopleGroup and PeopleGroup. PeopleGroup has a start and end date. Using linq-to-sql, I am trying to write a query that will pull the number of group members by a 1 week interval for any given group for a specified date range. The end result will look something like this (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 this possible, or do I need to write separate queries for each group / week?

+2


source to share


3 answers


I recently had to do something similar. After doing a little research, I found that it is easier to process data with C #.



In your situation, you can calculate a canonical date from the date of the date in your data and groups to that canonical date.

+1


source


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



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


source


Perhaps you can try something like this. I don't know if it works in LINQ to SQL, but it works with LINQ for objects. The 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);
0


source






More articles:


All Articles