Selecting oldest records from DataTable per product...

Joseph

Hello, I have a bit of an interesting scenario that I need assistance with. I have a DataTable that has five columns. The last column is the date that the record was created. The first column is for the product name. I need to be able to pull the oldest date per product. Is this possible without Linq? I know that I can sort the DataTable and get the oldest overall. And, I could also just set up DataTables per product and pull the information from there. Is there a way to accomplish this with one DataTable? Snippet below:

            dt.Columns.Add("CaseNumber");

            dt.Columns.Add("Subject");

            dt.Columns.Add("Description");

            dt.Columns.Add("Owner");

            dt.Columns.Add("CreatedDate");



                for (int i = 0; i < qr.records.Length; i++)

                {

                    Case newCase = (Case)qr.records[i];

                    DataRow dr = dt.NewRow();

                    dr["CaseNumber"] = newCase.CaseNumber;

                    dr["Subject"] = newCase.Subject;

                    dr["Description"] = newCase.Description;

                    dr["Owner"] = newCase.Owner.Name1;

                    dr["CreatedDate"] = newCase.CreatedDate;

                    dt.Rows.Add(dr);

So, what I need is to find all records by a particular product in the "Owner" column and then select the oldest case by the "CreatedDate" column.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

jinge17y ago

Accepted answer

             DataTable dt = new DataTable();
            dt.Columns.Add("CaseNumber", typeof(string)); 
            dt.Columns.Add("Subject",typeof(string));
            dt.Columns.Add("Description",typeof(string)); 
            dt.Columns.Add("Owner",typeof(string));
            dt.Columns.Add("CreatedDate",typeof(DateTime));

            dt.Rows.Add("001122", "Test", "AABBCC", "Product1", new DateTime(2008, 7, 11));
            dt.Rows.Add("001123", "Test", "AABBCC", "Product2", new DateTime(2008, 8, 11));
            dt.Rows.Add("001124", "Test", "AABBCC", "Product3", new DateTime(2008, 9, 11));
            dt.Rows.Add("001125", "Test", "AABBCC", "Product2", new DateTime(2008, 10, 11));
            dt.Rows.Add("001126", "Test", "AABBCC", "Product1", new DateTime(2008, 3, 11));

            var query = (from r in dt.AsEnumerable()
                         where r.Field("Owner") == "Product1"
                         select new
                         {
                             CaseNumber = r["CaseNumber"],
                             Subject = r["Subject"],
                             Description = r["Description"],
                             Owner = r["Owner"],
                             CreatedDate = r["CreatedDate"]
                         }).ToList();

            var list = query.Where((m)=> m.CreatedDate==query.Max(p => p.CreatedDate));

All that you need is the above code, please check "Do you like the answer" if it works.

Thank you.

Sam Hobbs17y ago
Are you sure you accepted the answer that works? When I tried jinge's code it seemed to product just one record of all records, not one record per item/case/product.
0
Sam Hobbs17y ago
If the DataTable is sorted by the case number or whatever it is you want the earliest date for, then simply iterate through the rows and save the row with the earliest date. For each row, if the case number in the new row is the same as the case number saved previously, then compare the dates and if the new row has an ealier date, then save the new row as the saved row. If the case number in the new row is not the same as the case number saved previously, then first process the previous row; you have the row for the earliest date for the case number. Then simply save the new row as the saved row. The following code below is a sample of doing that. In the sample code, "Cases" is the DataTable that has two columns. If the data is not sorted, then see my answer in http://www.c-sharpcorner.com/Forums/ShowMessages.aspx?ThreadID=69523 [> Sample Code <] if (Cases.Rows.Count == 0) { Console.WriteLine("No data"); return; } DataRow PreviousRow = Cases.Rows[0]; // start with i = 1 (the second row) for (int i = 1; i < Cases.Rows.Count; ++i) { if (Cases.Rows[i].Field("CaseNumber") == PreviousRow.Field("CaseNumber")) { if (Cases.Rows[i].Field("CreatedDate") < PreviousRow.Field("CreatedDate")) PreviousRow = Cases.Rows[i]; } else { // process previous Console.WriteLine(PreviousRow.Field("CaseNumber") + " " + PreviousRow.Field("CreatedDate")); // Store new case PreviousRow = Cases.Rows[i]; } } // process last Console.WriteLine(PreviousRow.Field("CaseNumber") + " " + PreviousRow.Field("CreatedDate"));
0