Counting the number of returning item for a TimeFrame

Question

Context:

This code compute the data from table "ItemReturn" into "StatReturn".
It take about 1 700 000 ItemReturn on the first run. For 2minute, computation and database insert.

ItemReturn: (int)Itm_Id, (int)Itm_Item_Serial, (datetime)Itm_CDate, [...]
StatReturn : (int)Stat_id, Itm_Id, NbReturn, NbReturn_at30d, NbReturn_at60d, [...]

For every return, we need to know: How many time "this" item was return in different timeframe (30,60,90.. days).

An item is unique based on his Serial (Itm_Item_Serial).

Function:

This function take in input a list of a ItemReturn give as result the `StatReturn.

private List<StatReturn> ComputeReturnStat(IEnumerable<ItemReturn> todoReturn)
{
 var ttMSE = todoReturn .GroupBy(x => x.Itm_Item_Serial)
 .Select(grp =>
 new InfoReturn(grp.Key
 , grp.Select(x => new MseDate((DateTime)x.Itm_CDate, x.Itm_Id))
 .OrderBy(x => x.InterD)
 .ToArray()
 , grp.Count()
 )
 );
 var result = new List<StatReturn>();
 foreach (var mse in ttMSE)
 {
 var statReturn = new StatReturn();
 statReturn.SR_Compteur = 0;
 statReturn.SR_NbRetour = (byte)mse.NbRetour;
 statReturn.SR_NbRetour30J = 0;
 statReturn.SR_NbRetour60J = 0;
 statReturn.SR_NbRetour90J = 0;
 statReturn.SR_NbRetour120J = 0;
 statReturn.SR_NbRetour180J = 0;
 statReturn.SR_NbRetour365J = 0;
 if (mse.NbRetour == 1)
 {
 statReturn.SR_Compteur = mse.Items.First().MSE_key;
 result.Add(statReturn);
 }
 else
 {
 for (int i = 0; i < mse.NbRetour; i++)
 {
 statReturn = new StatReturn();
 statReturn.SR_NbRetour = (byte)mse.NbRetour;
 statReturn.SR_NbRetour30J = 0;
 statReturn.SR_NbRetour60J = 0;
 statReturn.SR_NbRetour90J = 0;
 statReturn.SR_NbRetour120J = 0;
 statReturn.SR_NbRetour180J = 0;
 statReturn.SR_NbRetour365J = 0;
 statReturn.SR_Compteur = mse.Items[i].MSE_key;
 for (int j = i - 1; j >= 0; j--)
 {
 var delay = (mse.Items[i].InterD - mse.Items[j].InterD).Days;
 if (delay <= 30)
 {
 statReturn.SR_NbRetour++;
 statReturn.SR_NbRetour30J++;
 }
 else if (delay > 30 & delay <= 60)
 {
 statReturn.SR_NbRetour++;
 statReturn.SR_NbRetour60J++;
 }
 else if (delay > 60 & delay <= 90)
 {
 statReturn.SR_NbRetour++;
 statReturn.SR_NbRetour90J++;
 }
 else if (delay > 90 & delay <= 120)
 {
 statReturn.SR_NbRetour++;
 statReturn.SR_NbRetour120J++;
 }
 else if (delay > 120 & delay <= 180)
 {
 statReturn.SR_NbRetour++;
 statReturn.SR_NbRetour180J++;
 }
 else if (delay > 180 & delay <= 365)
 {
 statReturn.SR_NbRetour++;
 statReturn.SR_NbRetour365J++;
 }
 }
 result.Add(statReturn);
 }
 }
 };
 return result;
}

Additional information:

• InfoReturn, is a custom class.

  int nser; // Itm_Item_Serial
  MseDate[] items; // list of Return id (Itm_Id) and Date
  int nbRetour; // Total of return
  

• There is a byte cast in code because database is in small in so Linq-to-SQL type is byte.

• All comment have been delete, and all variable have been translated for the post.

• A lot of column in database are nullable, so we set them to 0 by default.

Answer 1

first var var statReturn is only used in if (mse.NbRetour == 1)

delay > 30 and other > are redundant

change statReturn to default those values to 0

I think you could do this sorted output and not need the GroupBy if you are having performance issues. Or do it in TSQL.

• Drag and Drop: The T-SQL solution is not that easy to comes up with. And for the no groupby part I dont see what is the structure of data you want to loop throught.
• Drag and Drop: Groupby is here for "distinct", I just see no other way around.
• paparazzo: Sorry that only used if (mse.NbRetour == 1) is not clear. I don't know how to be more clear. Sorry I could not be of more assistance.
• paparazzo: No I don't see your point. If you think creating something you might not use is good code then have at it. Again, sorry I could not be of more assistance.
• Drag and Drop: This point is now clear, you're right, I create an Object that will just be left over in some case. Don't be mad at me if I'm slow. We can't blame my English on this one. It was 100% my bad. I think I need to improve my communication skill to avoid this in the future. Thanks for your time and help you provided.