LINQ - Week count for a month - Asked By Shan on 02-Jun-11 03:36 AM

Hi,

 I want to convert this to LINQ,

SELECT avg(currentvalue) AS Average, datediff(week, convert(varchar(6), ReceivedDate, 112) + '01', ReceivedDate) + 1 AS Week

FROM Table1

WHERE (id = 19) AND (CONVERT(datetime, ReceivedDate, 103) >= '01/04/2026') AND (CONVERT(datetime, ReceivedDate, 103) <= '04/30/2011')

GROUP BY datediff(week, convert(varchar(6), ReceivedDate, 112) + '01', ReceivedDate) + 1 order by 2

udayabhaskar bijjam replied to Shan on 06-Jun-11 08:17 AM
try this

var weekcount = from t in table1
        where t.id=19 and (CONVERT(datetime, ReceivedDate, 103) >= '01/04/2026')
        AND (CONVERT(datetime, ReceivedDate, 103) <= '04/30/2011')
        select{Average=avg(t.currentvalue),week=datediff(week, convert(varchar(6), ReceivedDate, 112) + '01', ReceivedDate) + 1}
        groupby week orderby 2
jason rle replied to udayabhaskar bijjam on 13-Jun-11 03:22 AM
Are you sure that your code could work? I dont think avg is supported as your code.
jason rle replied to Shan on 13-Jun-11 06:14 AM
what did you want to get by 'convert(varchar(6), ReceivedDate, 112) + '01''?
I guest you want to datediff(week, '01/04/2026', ReceivedDate), right?
If that, please try:
var q = from t2 in
  (
    from t1 in table1
    where t1.ID == 19 && t1.ReceivedDate >= new DateTime(2011, 4, 1)
       && t1.ReceivedDate <= new DateTime(2011, 4, 30)
    select new
    {
    CurrentValue = t1.CurrentValue,
    Week = (t1.ReceivedDate.Day - 1
       + (int)t1.ReceivedDate.AddDays(1
       - t1.ReceivedDate.Day).DayOfWeek) / 7 + 1
    }
  )
  group t2 by t2.Week into w
  orderby w.Key
  select new
  {
  Average = w.Average(t2 => t2.CurrentValue),
  Week = w.Key
  };