locked
I need linq query for report using mvc5 c# RRS feed

  • Question

  • User-471420332 posted

    I need below result in linq query using mvc5

    Date            New Registration        Cumulative Registration           New Application            Cumulative Application
    
    09-02-2018         1                             3                              1                                2
    09-01-2018         2                             2                              1                                1

    I am using below two tables Registration and Application to get above counts report base on Dates, from 1 Sept 2018 to till and grid should be date descending order as above output my report. New Registration means on that day how registration came, cumulative registrations means sum of registration counts on that particular day. And Application concept as same as registration.

    1) Registration

    Name                                   Email                           RegDate
    Mazhar                                Khan@gmail.com           2018-09-02 13:08:32.303
    mohan                                 m@gmail.com                2018-09-01 13:08:32.303
    kjdj                                  k@gmail.com                  2018-09-01 13:08:32.303

    2) Application

    Title                Sector                   AppDate
     kk                     jj                  2018-09-02 13:08:32.303 
     mm                     tt                  2018-09-01 13:08:32.303

    You can also check how cumulation count should in image view

    enter image description here

    I search lot but still not getting any idea, thank you in advance

    Sunday, September 9, 2018 5:33 AM

All replies

  • User1520731567 posted

    HI mazhar khan india,

    According to your description,I have some question:

    where is the second item from?

    New Registration means on that day how registration came,

    Which field of the database is this New Registration from?

    Monday, September 10, 2018 6:11 AM
  • User-471420332 posted

    Dear Yuki thank you for your important reply, please check the below image and i have explain more to understand, and added two more dates in below image url

    https://imgur.com/a/VBsoV6V

    Which field of the database is this new registration from.

    See i have told in above question registration table just you have to write counts base on date in new registration and cumulative registration counts you have to write sum of all previous date counts, same concept to applications new application and cumulative application. 

    I think you to pass start date and end date static in linq query  like start date will be 1 sept 2018 and End date will be till today. 

    Just i am explain to get the concept and you write your own way to achieve this thank you in advance.

    Monday, September 10, 2018 7:34 AM
  • User1520731567 posted

    Hi mazhar khan india,

    According to your description,I think if you want to achieve it by linq ,you need to loop nested loops.

    If so,once the data is wrong, you will not be able to debug the code.

    Wednesday, September 12, 2018 3:04 AM
  • User-271186128 posted

    Hi Sir,

    According to the screenshot and your description, I suggest you could refer to the following code:

    using join clause to get the date list, then according to the date to get the new/cummulative columns.

                DateTime dt2 = new DateTime(2018, 09, 02, 13, 08, 32, 303);
                DateTime dt1 = new DateTime(2018, 09, 01, 13, 08, 32, 303);
                List<Registration> reglist = new List<Registration>()
                {
                    new Registration(){ Name="Mazhar",Email="m@gmail.com ",RegDate=dt2 },
                    new Registration(){ Name="mohan",Email="n@gmail.com",RegDate=dt1 },
                    new Registration(){ Name="kjdj",Email="k@gmail.com",RegDate=dt1},
                };
                List<Application> applist = new List<Application>()
                {
                    new Application(){ Title="kk", Sector="jj", AppDate=dt2},
                    new Application(){ Title="mm", Sector="tt", AppDate=dt1},
                };
    
    
                var datalist = (from cc in reglist
                               join aa in applist on cc.RegDate equals aa.AppDate
                               group cc by cc.RegDate into newgroup
                               select new
                               {
                                   date = newgroup.Key.ToShortDateString(),
                                   NewRegistration = reglist.Where(c => c.RegDate == newgroup.Key).Count(),
                                   CumulativeRegistration = reglist.Where(c => c.RegDate <= newgroup.Key).Count(),
                                   NewApplication = applist.Where(c => c.AppDate == newgroup.Key).Count(),
                                   CumulativeApplication = applist.Where(c => c.AppDate <= newgroup.Key).Count(),
                               }).ToList();

    The out put as below:

    You could also use Union to get the date list, code as below:

                //get the date list
    var datelist = reglist.Select(c => c.RegDate).Union(applist.Select(c => c.AppDate)).ToList(); var datelist2 = (from cc in datelist select new { date = cc.ToShortDateString(), NewRegistration = reglist.Where(c => c.RegDate == cc).Count(), CumulativeRegistration = reglist.Where(c => c.RegDate <= cc).Count(), NewApplication = applist.Where(c => c.AppDate == cc).Count(), CumulativeApplication = applist.Where(c => c.AppDate <= cc).Count(), }).ToList();

    Best regards,
    Dillion

    Tuesday, September 18, 2018 10:01 AM