问题根据给定条件填充数据表的逻辑 [英] Problem With the logic to fill data-table according to given condition

问题描述

你好！

我有一个具有以下结构的数据表

Hello!!

I have a data-table which has following structure

Contribution ||  MonthNum  || MonthName ||   startdate  ||  Enddate

900          ||     1      ||  January  ||   1/1/2009  ||  3/31/2009 
1000         ||     4      ||    April  ||  4/1/2009   ||  3/31/2010 
852          ||     12     ||  December ||  12/1/2008  ||  12/31/2008

我需要做的是用此数据表中给定的值填充每个月的另一个数据表.

例如，MonthNum 1的贡献现在为900，而MonthNum 2,3的贡献应该为900，然后从4到11的贡献应该为1000.

请告诉我该怎么做..

What i need to do is fill another datatable For each month with the values given in this datatable.

for example contribution for MonthNum 1 is 900 now for MonthNum 2,3 it should be 900 then from 4 upto 11 it should be 1000.

Please tell me how to do this..

推荐答案

我将创建一个类来处理此问题.但是我确实有一个问题，在原始表格中，日期似乎...奇怪的是，您有一个开始日期和结束日期以及月份，开始日期和结束日期会更有用，因为您的十二月实际上是在2008年.

给定以下类别:

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 to edit
DataTable ContributionInfo = new DataTable();

//Entry Point
public FiscalYear()
{
    SetupFiscalDataTable();
}

//Table Setup operations
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;
    }
}

//Returns Fiscal Year calculation
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; } //Jan - Mar will be in next year

        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;
}
//Returns True Year calculation
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;
}

您可以使用该类来做两个不同的切入点，一个可以给您财政年度的缴款(即给定2009财政年度，您可以获得2009年4月至2010年3月的信息)，另一个可以给您真实的1月-十二月另外，如果您有超大圈，可以将它们加起来.

您在此处寻找的关键代码是:

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);

通过这一部分，您可以从数据表中选择相关的行，并根据需要对它们进行排序.在每个月有一行的"for"语句上使用它，可以提取特定月份的供款，并根据需要应用它.

请注意，此代码是从我最近从事的类似项目中修改而来的.

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.

创建表的月份编号为
.
tablename = tblmonth

create table having months nos.

tablename=tblmonth

monthnos  actualmonno monthnm
-----------------------------
1     4     apr
2     5     may
3     6     june
.     .
.     .
.     .
12    3     march

现在将其与您的桌子一起

Now join it with your table,

select monthnos,(select top 1 Contribution from yourtbl where (select monthnos from tblmonth where actualmonno= MonthNum)<=monthnos inner join tblmonth on MonthNum=monthnos order by Monthnos desc ) as Contribution
from tblmonth
left join yourtbl on MonthNum=monthnos

结果将是

results will be

monthnos   Contribution  
------------------------
1          900           
2          900           
3          900           
4          1000          
5          1000
6          1000
7          1000
8          1000
9          1000
10         1000
11         1000
12         852

祝您编码愉快！
:)

Happy Coding!
:)

这篇关于问题根据给定条件填充数据表的逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！