C # Linq Weighted average by date

Question

I found several posts detailing how to perform a weighted average based on a foreign key, but I have yet to find a solution that addresses my situation. Here he is:

I have two tables, table A and table B of many-to-many tables linking them; nothing complicated:

TableA { A_ID, Other stuff } TableB { B_ID, Date Other stuff } LinkAtoB { A_ID, B_ID } 

Now here comes the math. I am more or less trying to give a result in TableA, based on the number of recent associations in table B.

So, if TableA has 4 associations in a table with the following dates:

 {10/23/2010, //3 days ago 10/19/2010, //5 days ago 10/18/2010, //6 days ago 9/13/2010} //40ish days ago 

So here is how I would like to rank them:

I would like to provide a threshold for return in days, I will use 7 days as an example:

Thus, using the above data, I would assign the following values:

 {10/23/2010, //7-3 = 4 10/19/2010, //7-5 = 2 10/18/2010, //7-6 = 1 9/13/2010} //40ish days ago 

Thus, the weighted average value for this particular record in TableA is 7/3 = 2,33333.

Here is more or less what I still have:

 var k = from a in TableA group a by a.Select(x=>x.LinkAtoB.TableB) .Where(x=>x.Date.CompareTo(DateTime.Now.AddDays(-7)) >= 0) into g select g.Sum(x => DateTime.Now.Subtract(x.Date).Days) / g.Sum(x => x.Length); 

I think I'm close, but I know that part of the group is wrong. I think other stuff should work. How to fix my code to execute what I want?

Answer 1

Here you go! :)

 var k = (from b in TableB join bb in LinkAtoB on b.B_ID equals bb.B_ID into b_join from ab in b_join.DefaultIfEmpty() where b.B_DATE.CompareTo(DateTime.Now.AddDays(-7)) > 0 select new {ab.A_ID, DaysAgo = (DateTime.Now - b.B_DATE).Days} into xx group xx by xx.A_ID into yy select new {yy.Key, Weighted = yy.Sum(x=> 7 - x.DaysAgo) / yy.Count()} into zz join a in TableA on zz.Key equals a.A_ID select new {a.A_ID, a.A_Other_Stuff, zz.Weighted}).ToList();