Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Get Earliest and Latest Dates

Posted on 2013-05-21
15
Medium Priority
?
388 Views
Last Modified: 2013-05-21
Hi Experts!

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

Open in new window


per (Model1) is populated with the following contents:
Product Listing
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();

Open in new window

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 = 20340201

The 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

Open in new window


TIA!
Product.xlsx
0
Comment
Question by:allanau20
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +1
15 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39185970
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 = 20340201

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.
0
 
LVL 5

Author Comment

by:allanau20
ID: 39186023
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!
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39186040
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.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 5

Author Comment

by:allanau20
ID: 39186057
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!
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39186080
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

Open in new window

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

Open in new window

0
 
LVL 5

Author Comment

by:allanau20
ID: 39186264
Thanks PortletPaul,

I'll see if someone can help express that into a LINQ query; in the meantime I'll continue trying... thanks again..
0
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 39186337
Hi allanau20;

Aha I did misunderstand the requirements. I understood that we were to sort first by STARTDATE and then by ENDDATE but what you were asking was which in each product group find the earliest STARTDATE and use that value and then do the same for ENDDATE and use that value even if the two dates come from different records within the same group. Then use this query.

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

Open in new window

0
 
LVL 5

Author Comment

by:allanau20
ID: 39186338
Yes, thank you FernandoSoto...

I shall try it out now!
0
 
LVL 5

Author Comment

by:allanau20
ID: 39186344
Hi FernandoSoto.

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: 

Open in new window

Did it work on your end?
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 39186346
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();

Open in new window

0
 
LVL 5

Author Comment

by:allanau20
ID: 39186350
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 ...
0
 
LVL 5

Author Comment

by:allanau20
ID: 39186355
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..
0
 
LVL 5

Author Closing Comment

by:allanau20
ID: 39186356
super answer!
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39186363
happy to have assisted - not much I understood in that code though
(I'm getting too old for yet another syntax)
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 39186364
Not a problem allanau20, glad I was able to help. Have a great day.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question