I would create a class to handle this... However I do have one question, in the original table the dates seem... odd you have a start and end date along with the month, start and enddate would be more useful as your december is actually in 2008.
Given the following Class:
DataTable ContributionInfo = new DataTable();
public FiscalYear()
{
SetupFiscalDataTable();
}
private void SetupFiscalDataTable()
{
ContributionInfo.Clear();
ContributionInfo.Columns.Clear();
ContributionInfo.Columns.Add("Month");
ContributionInfo.Columns.Add("Fiscal");
ContributionInfo.Columns.Add("Name");
ContributionInfo.Columns.Add("Value");
DataRow tmpRow = ContributionInfo.NewRow();
tmpRow["Month"] = 1;
tmpRow["Fiscal"] = 10;
tmpRow["Name"] = "January";
tmpRow["Value"] = 0;
ContributionInfo.Rows.Add(tmpRow);
DataRow tmpRow2 = ContributionInfo.NewRow();
tmpRow2["Month"] = 2;
tmpRow2["Fiscal"] = 11;
tmpRow2["Name"] = "February";
tmpRow["Value"] = 0;
ContributionInfo.Rows.Add(tmpRow2);
DataRow tmpRow3 = ContributionInfo.NewRow();
tmpRow3["Month"] = 3;
tmpRow3["Fiscal"] = 12;
tmpRow3["Name"] = "March";
tmpRow["Value"] = 0;
ContributionInfo.Rows.Add(tmpRow3);
DataRow tmpRow4 = ContributionInfo.NewRow();
tmpRow4["Month"] = 4;
tmpRow4["Fiscal"] = 1;
tmpRow4["Name"] = "April";
tmpRow["Value"] = 0;
ContributionInfo.Rows.Add(tmpRow4);
DataRow tmpRow5 = ContributionInfo.NewRow();
tmpRow5["Month"] = 5;
tmpRow5["Fiscal"] = 2;
tmpRow5["Name"] = "May";
tmpRow["Value"] = 0;
ContributionInfo.Rows.Add(tmpRow5);
DataRow tmpRow6 = ContributionInfo.NewRow();
tmpRow6["Month"] = 6;
tmpRow6["Fiscal"] = 3;
tmpRow6["Name"] = "June";
tmpRow["Value"] = 0;
ContributionInfo.Rows.Add(tmpRow6);
DataRow tmpRow7 = ContributionInfo.NewRow();
tmpRow7["Month"] = 7;
tmpRow7["Fiscal"] = 4;
tmpRow7["Name"] = "July";
tmpRow["Value"] = 0;
ContributionInfo.Rows.Add(tmpRow7);
DataRow tmpRow8 = ContributionInfo.NewRow();
tmpRow8["Month"] = 8;
tmpRow8["Fiscal"] = 5;
tmpRow8["Name"] = "August";
tmpRow["Value"] = 0;
ContributionInfo.Rows.Add(tmpRow8);
DataRow tmpRow9 = ContributionInfo.NewRow();
tmpRow9["Month"] = 9;
tmpRow9["Fiscal"] = 6;
tmpRow9["Name"] = "September";
tmpRow["Value"] = 0;
ContributionInfo.Rows.Add(tmpRow9);
DataRow tmpRow10 = ContributionInfo.NewRow();
tmpRow10["Month"] = 10;
tmpRow10["Fiscal"] = 7;
tmpRow10["Name"] = "October";
tmpRow["Value"] = 0;
ContributionInfo.Rows.Add(tmpRow10);
DataRow tmpRow11 = ContributionInfo.NewRow();
tmpRow11["Month"] = 11;
tmpRow11["Fiscal"] = 8;
tmpRow11["Name"] = "November";
tmpRow["Value"] = 0;
ContributionInfo.Rows.Add(tmpRow11);
DataRow tmpRow12 = ContributionInfo.NewRow();
tmpRow12["Month"] = 12;
tmpRow12["Fiscal"] = 9;
tmpRow12["Name"] = "December";
tmpRow["Value"] = 0;
ContributionInfo.Rows.Add(tmpRow12);
}
private void ReSetupFiscalDataTable()
{
for (int i = 0; i < ContributionInfo.Rows.Count; i++)
{
ContributionInfo.Rows[i]["Value"] = 0;
}
}
public DataTable GetFiscalDate(int sFiscalYear, DataTable sSentInfo)
{
string filterExp;
string sortExp;
for (int i = 0; i < ContributionInfo.Rows.Count; i++)
{
int tmpContribution = 0;
int tmpMonth = Convert.ToInt16(ContributionInfo.Rows[i]["Month"]);
int tmpYear = sFiscalYear;
if (i <= 2) { tmpYear = sFiscalYear + 1; }
filterExp = "'" + tmpMonth + "/2/" + tmpYear + "' > startdate and '" + tmpMonth + "/2/" + tmpYear + "' < enddate";
sortExp = "startdate";
DataRow[] tmpMonthtable;
tmpMonthtable = sSentInfo.Select(filterExp, sortExp);
for (int z = 0; z < tmpMonthtable.Length; z++)
{
tmpContribution += Convert.ToInt16(tmpMonthtable[z][0]);
}
ContributionInfo.Rows[i]["Value"] = tmpContribution;
}
return ContributionInfo;
}
public DataTable GetTrueDate(int sTrueYear, DataTable sSentInfo)
{
string filterExp;
string sortExp;
for (int i = 0; i < ContributionInfo.Rows.Count; i++)
{
int tmpContribution = 0;
int tmpMonth = Convert.ToInt16(ContributionInfo.Rows[i]["Month"]);
int tmpYear = sTrueYear;
filterExp = "'" + tmpMonth + "/2/" + tmpYear + "' > startdate and '" + tmpMonth + "/2/" + tmpYear + "' < enddate";
sortExp = "startdate";
DataRow[] tmpMonthtable;
tmpMonthtable = sSentInfo.Select(filterExp, sortExp);
for (int z = 0; z < tmpMonthtable.Length; z++)
{
tmpContribution += Convert.ToInt16(tmpMonthtable[z][0]);
}
ContributionInfo.Rows[i]["Value"] = tmpContribution;
}
return ContributionInfo;
}
You could use this class to do two different entry points, one would give you the Fiscal year contributions (ie given a fiscal year of 2009 you would get info from April 2009 to March 2010) and the other would give you the true year Jan-Dec. Also, if you have over lap it would add them up.
The Key bit of code here that you are looking for is this:
filterExp = "'" + tmpMonth + "/2/" + tmpYear + "' > startdate and '" + tmpMonth + "/2/" + tmpYear + "' < enddate";
sortExp = "startdate";
DataRow[] tmpMonthtable;
tmpMonthtable = sSentInfo.Select(filterExp, sortExp);
This piece will allow you to select rows from your Data Table that are relevant and sort them if you want. Using this on a "for" statement with a row for each month allows you to pull out the contribution for a specific month and apply it however you want.
As a note, this code was modified from a similar project I recently worked on.