Linq Count number of consults per week, from 28 weeks in the past till current week

Question

I have a sql table named Consult it represents consults(treatments) of physical therapists

ConsultID ConsultDate Therapist Location

I want to present this data summarized per week, starting 28 weeks ago till current week

Like:

Location     Week-1          Week-2          Week-3 .....      Week-28
Amsterdam    41              38              34                55

Utrecht      65              56              46                46

How can I do this in Linq? I have the following:

public ActionResult Therapist(int? id)
    {
        if (id == null)
        {
            return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
        }

        DateTime startDate = DateTime.Now.StartOfWeek(DayOfWeek.Monday).AddDays(-168);
        DateTime endDate = DateTime.Now.StartOfWeek(DayOfWeek.Sunday);

        var TherapistConsult = from row in db.Consults
                               where ((row.Therapist == id) && (row.ConsultDate > startDate) && (row.ConsultDate < endDate))
                                 group row by row.Location into g
                                 where g.FirstOrDefault() != null
                                 select new
                                 {
                                     Location = g.Key,
                                     // Need a loop here for 28 weeks in the past till this week
                                     // WeekN = g.Count(x => x.Week == N),

                                 };

        return View(TherapistConsult.ToList());

    }

yes, i have the following: var tr = from row in Consults where ((row.Therapist == 44) && (row.ConsultDate > Convert.ToDateTime("19-5-2014 00:00:00") && (row.ConsultDate < Convert.ToDateTime("2-11-2014 00:00:00")))) group row by row.Location into g where g.FirstOrDefault() != null select new { Location = g.Key, Week-1 = g.Count (x => x.Week == 1 ) — René Van Woezik, Nov 04 '14 at 09:42
Does your week start on a monday or on a sunday or is "week-1" just "today - 7 days"? — Corak, Nov 04 '14 at 09:42
Week starts at Monday, for startDate and enddate i have: DateTime endDate = DateTime.Now.StartOfWeek(DayOfWeek.Sunday); DateTime startDate = DateTime.Now.StartOfWeek(DayOfWeek.Monday).AddDays(-168); — René Van Woezik, Nov 04 '14 at 09:48
`select new { Week - 1 = ...}` isn't going to work, `Week - 1` isn't a valid property name. Try something like `WeekNumber`. Please show the actual code you use, what you expect it to do and what it actually does. — CodeCaster, Nov 04 '14 at 09:50
to get the week number you can use the `datepart(wk, your_datetime_field)` — Deepak Sharma, Nov 04 '14 at 10:12
if you want I can tell you the sql query but can not provide you the Linq — Deepak Sharma, Nov 04 '14 at 10:12

score 0 · Accepted Answer · edited May 23 '17 at 12:20

0

What you're looking for is "Pivoting" your data, which is much easier to do from SQL then from linq,
Look this up in CodeProject
http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query
And if you have to do this in linq for some reason, look this up:
Pivot data using LINQ

edited May 23 '17 at 12:20

Community

1
1

answered Nov 04 '14 at 09:52

Nitzan

1,669
3
19
33

Linq Count number of consults per week, from 28 weeks in the past till current week

1 Answers1