Allan
asked on
Get Earliest and Latest Dates
Hi Experts!
Thanks for reading this.
Have this Model:
per (Model1) is populated with the following contents:
The following code (from FernandoSoto) should have selected the (combine of the) earliest (Start Date) and latest (End Date) for each unique Product.
The results should look like this:
TIA!
Product.xlsx
Thanks for reading this.
Have this Model:
public class Model1
{
public string PRODUCT { get; set; }
public string STARTDATE { get; set; }
private string _Enddate;
public string ENDDATE
{
get { return _Enddate; }
set
{
if(String.IsNullOrEmpty(value))
_Enddate = "20781231";
else
_Enddate = value;
}
}
}
per (Model1) is populated with the following contents:
The following code (from FernandoSoto) should have selected the (combine of the) earliest (Start Date) and latest (End Date) for each unique Product.
var prods = (from p in per
group p by p.PRODUCT into pGroup
from pg in pGroup
group pg by pg.PRODUCT into pgGroup
select pgGroup.OrderBy( d => d.STARTDATE)
.ThenByDescending(d => d.ENDDATE)
.FirstOrDefault())
.ToList();
So, for product = 0002-0800 it should have the Start Date = 19970201 and the End Date = 20781231. However, it has Start Date = 19890201 End Date = 20340201The results should look like this:
PRODUCT STARTDATE ENDDATE
0002-0800 19970201 20781231
0002-1407 19510301 20781231
0002-1975 20101201 20781231
0002-3004 20010316 20781231
0002-3227 20030110 20051123
TIA!
Product.xlsx
ASKER
Hi matthewspatrick.
For a unique product say 0002-0800, you would look through all the Start Dates and pick the earliest and do the same for End Date for that product.
In the example we have eight (8) products with 0002-0800, so the earliest Start Date should be 19890201 (that's correct), but the End Date should be 20781231 instead of 20340201.
Hope this makes sense.
And thanks again!
For a unique product say 0002-0800, you would look through all the Start Dates and pick the earliest and do the same for End Date for that product.
In the example we have eight (8) products with 0002-0800, so the earliest Start Date should be 19890201 (that's correct), but the End Date should be 20781231 instead of 20340201.
Hope this makes sense.
And thanks again!
Check again. In your sample data, the earliest start for product code 0002-0800 is very clearly 19890201, and your latest end is also very clearly 20340201.
ASKER
Hi matthewspatrick,
you're right if we ignore the blank values. However, in Model1 where the End Date is blank it will default to 20781231. So, it should have selected 20781231 instead of 20340201.
Thanks again for your help!
you're right if we ignore the blank values. However, in Model1 where the End Date is blank it will default to 20781231. So, it should have selected 20781231 instead of 20340201.
Thanks again for your help!
in sql for these results:
PRODUCT MIN_START MAX_END
0002-0800 19890201 20781231
0002-1407 19510301 20781231
0002-1975 20101201 20781231
0002-3004 20010316 20781231
0002-3227 20030110 20051123
produced by this query:
SELECT
product
, min(startdate) min_start
, max(CASE WHEN enddate IS NULL THEN 20781231 ELSE enddate END) max_end
FROM products
GROUP BY product
ASKER
Thanks PortletPaul,
I'll see if someone can help express that into a LINQ query; in the meantime I'll continue trying... thanks again..
I'll see if someone can help express that into a LINQ query; in the meantime I'll continue trying... thanks again..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, thank you FernandoSoto...
I shall try it out now!
I shall try it out now!
ASKER
Hi FernandoSoto.
Oh man; got this error when it was trying to execute prods:
{"LinqToExcel does not provide support for the Group() method"}
Oh man; got this error when it was trying to execute prods:
{"LinqToExcel does not provide support for the Group() method"}
System.NotSupportedException was caught
HResult=-2146233067
Message=LinqToExcel does not provide support for the Group() method
Source=LinqToExcel
StackTrace:
at LinqToExcel.Query.SqlGeneratorQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
at LinqToExcel.Query.ExcelQueryExecutor.GetSqlStatement(QueryModel queryModel)
at LinqToExcel.Query.ExcelQueryExecutor.ExecuteCollection[T](QueryModel queryModel)
at Remotion.Data.Linq.Clauses.StreamedData.StreamedSequenceInfo.ExecuteCollectionQueryModel[T](QueryModel queryModel, IQueryExecutor executor)
at Remotion.Data.Linq.Clauses.StreamedData.StreamedSequenceInfo.ExecuteQueryModel(QueryModel queryModel, IQueryExecutor executor)
at Remotion.Data.Linq.QueryModel.Execute(IQueryExecutor executor)
at Remotion.Data.Linq.QueryProviderBase.Execute[TResult](Expression expression)
at Remotion.Data.Linq.QueryableBase`1.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at SomeNameSpace.Process() in C:\blah\Import.cs:line 37
InnerException:
Did it work on your end?
You should still have two queries one for LinqToExcel and the other for Linq to Objects, so the code should look like this.
var per = (from x in excel.Worksheet<Model1>("Product")
select x).ToList();
var prods = (from p in per
group p by p.PRODUCT into pGroup
select new Model1()
{
PRODUCT = pGroup.Key,
STARTDATE = pGroup.OrderBy(sd => sd.STARTDATE).Select(sd => sd.STARTDATE).FirstOrDefault(),
ENDDATE = pGroup.OrderByDescending(ed => ed.ENDDATE).Select(ed => ed.ENDDATE).FirstOrDefault()
}).ToList();
ASKER
my bad FernandoSoto; you're right ... earlier I was at home and now I'm at work, and between the puter the 'per' at work didn't have the 'ToList();' ... it compiled ... I'm validating now ...
ASKER
Eureka! That did it!!
Thanks so much FernandoSoto; if you were in town I would've bought u lunch.
you were patient in helping me along. Thanks so much.
And thanks PortletPaul ... the absolute last resort was to dump it into a SQL table and do what I needed from there..
Thanks so much FernandoSoto; if you were in town I would've bought u lunch.
you were patient in helping me along. Thanks so much.
And thanks PortletPaul ... the absolute last resort was to dump it into a SQL table and do what I needed from there..
ASKER
super answer!
happy to have assisted - not much I understood in that code though
(I'm getting too old for yet another syntax)
(I'm getting too old for yet another syntax)
Not a problem allanau20, glad I was able to help. Have a great day.
Please explain how one would get those results. Based on your sample data, Start Date = 19890201 and End Date = 20340201 appear to both be correct.