How to Show Records from a Required Parameters in C#?

SQL Datatype: SerialNumber/PartNumber - nvarchar(50), Quantity - int

Loaded Records on DataTable:

SerialNumber | PartNumber  | Quantity
      1      |     0001    |   20
      2      |     0001    |   10 
      3      |     0001    |   20  
      5      |     0001    |   20

Requirem开发者_JS百科ent:

1.Get Serials which SUM of their Quantity = 40

2.Get the lowest Serial first (FIFO)

Which Sample result should be like this:

SerialNumber | PartNumber  | Quantity
      1      |     0001    |   20
      3      |     0001    |   20 

How to get this result?

Thanks in Regards

Ok, so you removed the sql tags and introduced FIFO as a requirement. this clarifies matters.

the heart of your answer is here:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace SO_Forms_Demo
{
    class SumFifo
    {

        DataTable _dt;

        public SumFifo(DataTable dt)
        {
            _dt = dt;
        }

        public DataView GetAll()
        {
            return new DataView(_dt, null, null, DataViewRowState.CurrentRows);
        }

        public DataTable GetFIFO(string partNumber, int qty)
        {
            DataTable resultsTable = _dt.Clone();

            //the generic collection type that represents a FIFO relationship is a Queue
            Queue<DataRow> PartRows = new Queue<DataRow>(_dt.Select("partNumber = '" + partNumber + "'", "serialNumber"));

            //iterate through the queue adding rows and decreasing quantity till your requirment is met.
            foreach (DataRow row in PartRows)
            {
                if (qty > 0)
                {
                    resultsTable.ImportRow(row);
                    qty -= int.Parse(row["qty"].ToString());
                }
            }

            return resultsTable;
        }
    }
}

The "GetFIFO" method will iterate through the provided dataset and first create a queue (a FIFO collection of datarows that have the correct partNumber. It will iterate this collection decreasing the object quantity by the selected quantity until 0 is reached.

To test this build a form that looks like:

With a code that looks like:

    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace SO_Forms_Demo
{
    public partial class FormFIFO : Form
    {
        DataTable _table;
        public FormFIFO()
        {
            InitializeComponent();

            _table = new DataTable("fifo");
            _table.Columns.Add(new DataColumn("serialNumber"));
            _table.Columns.Add(new DataColumn("partNumber"));
            _table.Columns.Add(new DataColumn("qty"));
            _table.AcceptChanges();
            _table.Rows.Add(new object[3] { 1, "0001", 20 });
            _table.Rows.Add(new object[3] { 2, "0002", 10 });
            _table.Rows.Add(new object[3] { 3, "0001", 20 });
            _table.Rows.Add(new object[3] { 4, "0002", 10 });
            _table.Rows.Add(new object[3] { 5, "0001", 20 });
            _table.Rows.Add(new object[3] { 6, "0002", 10 });
            _table.AcceptChanges();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            SumFifo sumFifo = new SumFifo(_table);
            dataGridView1.DataSource = sumFifo.GetAll();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            SumFifo sumFifo = new SumFifo(_table);
            dataGridView1.DataSource = sumFifo.GetFIFO(textBox1.Text,int.Parse( textBox2.Text));
        }
    }
}

Now obviously you have a long way to go to make this "useful/robust/etc.." but the GetFIFO method is the essential answer to your question In C#.

This can definitely be done in SQL, but optimizing it to work for large sets will be the challenge. Here's a solution that makes use of Common Table Expressions to calculate the set of possible permutations for the target PartNumber, groups those permutations by the SUM of their Quantities and selects the first permutation whose SUM matches the target.

That permutation is then used to identify the SerialNumbers to select from the data set:

declare @partNum char(4)
SET @partNum = '0001'
declare @quantity int
SET @quantity = 40

declare @data TABLE (
    SerialNumber int identity(1,1),
    PartNumber char(4),
    Quantity int
);

INSERT INTO @data (PartNumber, Quantity) VALUES ('0001', 20);
INSERT INTO @data (PartNumber, Quantity) VALUES ('0001', 10);
INSERT INTO @data (PartNumber, Quantity) VALUES ('0001', 20);
INSERT INTO @data (PartNumber, Quantity) VALUES ('0002', 20);
INSERT INTO @data (PartNumber, Quantity) VALUES ('0001', 20);

WITH
cte_items as (
    select * from @data where PartNumber = @partNum
),
cte_perms as (
    select cast(cast(SerialNumber as binary(4)) as varbinary(max)) as perm, 1 as numentries
    from cte_items
    union all
    select cast(n.SerialNumber as binary(4)) + p.perm, p.numentries + 1
    from cte_perms p
    join cte_items n on n.SerialNumber < cast(substring(perm,1,4) as int)
),
cte_permlist as (
    select row_number() over (order by (select 1)) as permnum, perm
    from cte_perms
)

SELECT d1.SerialNumber, d1.PartNumber, d1.Quantity
FROM @data d1
INNER JOIN (
    SELECT
       cast(substring(p.perm, 4*n.SerialNumber-3, 4) as int) as SerialNumber
    from cte_permlist p
    join @data n on n.SerialNumber = n.SerialNumber
    where cast(substring(p.perm, 4*n.SerialNumber-3, 4) as int) != 0
      and p.permnum = (
            SELECT TOP 1 permutations.permnum
            FROM @data d2
            CROSS APPLY (
                SELECT
                   p.permnum,
                   cast(substring(p.perm, 4*n.SerialNumber-3, 4) as int) as SerialNumber
                from cte_permlist p
                join @data n on n.SerialNumber = n.SerialNumber
                where cast(substring(p.perm, 4*n.SerialNumber-3, 4) as int) != 0
            ) permutations
            WHERE PartNumber = @partNum
              and permutations.SerialNumber = d2.SerialNumber
            GROUP BY permutations.permnum
            HAVING SUM(d2.Quantity) = @quantity
            ORDER BY permnum desc
        )
    ) pSn on pSn.SerialNumber = d1.SerialNumber

The result:

SerialNumber PartNumber Quantity
------------ ---------- -----------
1            0001       20
3            0001       20

Once the query optimizer gets done with this it should be quite efficient unless, for the target part number, there are more than a handful of permutations.

I'd suggest writing a SQL query that returns all rows for a given part number and perform the calculations to determine which Serial #'s to use in a different programming language. SQL is great for set-based operations, but this problem requires finding a particular constrained subset of a subset which SQL won't excel at.

When you've gotten your row collection for the part. I'd suggest employing a depth- or breadth- first search. Depth-first would probably be the best approach if you've no requirement to fulfil the quantity in 'the minimum number of serial #s' - and choosing 'the largest quantity less than the shortfall' at each stage should be a reasonable strategy when progressing the search.