none
Grouping data Hierarchically RRS feed

  • Question

  • I have four tables:

    1. Case (CaseId, CaseNo, CourtId)

    2. Court (CourtId, Name, HCId)

    3. HeadCourt (HCId, Name)

    4. Status (Id, CaseId, Disposed)

    I want to prepare a summary showing total number of cases under different HeadCourt and the cases disposed off (please note that column Disposed in Status is boolean) for example:

    Head Court                       Cases                                Cases Disposed Off                     Balance

    Supreme Court                  100                                                       40                                      60

    High Court                         200                                                      120                                     80

    Please help.

    Monday, September 12, 2016 3:25 PM

All replies

  • Hi,

    According to your description, it seems that we could use Groupby method to achieve it. Please check the following code.

    var query = from hc in db.HeadCourt
                                join c in db.Court on hc.HCId equals c.HCId
                                join ca in db.Case on c.CourtId equals ca.CourtId
                                join s in db.Status on ca.CaseId equals s.CaseId
                                select new
                                {
                                    HeadCourt = hc.Name,
                                    Cases = ca.CaseId,
                                    CasesDisposed = s.Disposed
                                };
    
                    var result = query.GroupBy(t => t.HeadCourt).Select(t => new
                    {
                        HeadCourt = t.Key,
                        Cases = t.Count(),
                        CasesDisposedOff = t.Where(x => x.CasesDisposed == false).Count(),
                        Balance = t.Where(x => x.CasesDisposed == true).Count()
                    });

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 13, 2016 2:02 AM
    Moderator
  • Hello Cole Wu,

    Thanks a lot for your quick response. I used the query in my MVC application as under:

    ViewModel:

    public class HeadCourtSummary
        {
            public string HeadCourt { get; set; }
            public int Cases { get; set; }
            public int CasesDisposedOff { get; set; }
            public int Balance { get; set; }
    
        }

    Controller:

    public ActionResult Summary()
            {
    
                var query = from hc in db.tblHeadCourts
                            join c in db.tblCourts on hc.fldUCId equals c.fldUCId
                            join ca in db.tblCases on c.fldCourtId equals ca.fldCourtId
                            join s in db.tblStatus on ca.fldCaseId equals s.fldCaseId
                            select new
                            {
                                HeadCourt = hc.fldName,
                                Cases = ca.fldCaseId,
                                CasesDisposed = s.fldDispose
                            };
    
                var summary = query.GroupBy(t => t.HeadCourt).Select(t => new HeadCourtSummary
                {
                    HeadCourt = t.Key,
                    Cases = t.Count(),
                    CasesDisposedOff = t.Where(x => x.CasesDisposed != "P").Count(),
                    Balance = t.Where(x => x.CasesDisposed == "P").Count()
                });
    
                return View(summary);

    View:

    @model IEnumerable<CCIS.ViewModel.HeadCourtSummary>
    
    @{
        ViewBag.Title = "Summary";
    }
    
    <h2>Summary of Cases under differnt Courts</h2>
    
    <table class="table">
        <tr>
            <th>
                Head Court
            </th>
    
            <th>
                Registered Case
            </th>
    
            <th>
                Disposed Off
            </th>
    
            <th>
                Pending
            </th>
            <th></th>
        </tr>
    
    @foreach (var item in Model) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.HeadCourt)
            </td>
            
            <td>
                @Html.DisplayFor(modelItem => item.Cases)
            </td>
    
            <td>
                @Html.DisplayFor(modelItem => item.CasesDisposedOff)
            </td>
    
            <td>
                @Html.DisplayFor(modelItem => item.Balance)
            </td>
        </tr>
    }
    
    </table>

    Please note that in my original question I had mentioned that the field Disposed is boolean but it is text.

    Also, fldName in the table HeadCourt is text.

    When I run the application I get the following error:

    The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.


    Please help to solve it.

    Warm Regards,

    Arun




    • Edited by ArunKhatri Tuesday, September 13, 2016 6:34 AM
    Tuesday, September 13, 2016 6:24 AM
  • Hi ArunKhatri,

    >>Please help to solve it.

    If Disposed is text, please use contains method. like this:

     var summary = query.GroupBy(t => t.HeadCourt).Select(t => new HeadCourtSummary
                {
                    HeadCourt = t.Key,
                    Cases = t.Count(),
                    CasesDisposedOff = t.Where(x => !x.CasesDisposed.Contains("P")).Count(),
                    Balance = t.Where(x => x.CasesDisposed.Contains("P")).Count()
                });

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 13, 2016 8:06 AM
    Moderator
  • Dear Cole Wu,

    I tried using contains method but it did not make any difference. I still receive the same error.

    Please see if I have declared variables in the ViewModel correctly. Then, should I initiate the View using  

    @model IEnumerable<CCIS.ViewModel.HeadCourtSummary> or there is a need to adopt some other way? So please ensure if everything is okay.

    Waiting for your earliest reply.

    Warm regards,

    Arun

    EDIT: I further noticed that the query is not working because we are trying to GroupBy on fldName which is a text field. When I removed it and assigned HeadCourt = hc.fldUCId the application ran without any error and displayed records without the name of the HeadCourt. Further I noticed that it returns incorrect count for Cases. In all there are 368 cases in the table tblCases but when we sum up (manually from the display list) the cases grouped into HeadCourt turns up to be 401 cases. 

    What I get after changing Groupby on fldName to fldUCId is shown below:

    Please try to fine tune the query so that I get HeadCourt names and the correct total.

    • Edited by ArunKhatri Tuesday, September 13, 2016 11:53 AM
    Tuesday, September 13, 2016 10:59 AM
  • Hi ArunKhatri,

    Could your please provide a simple demo which could reproduce the issue via OneDrive. we'll reproduce your issue on our side and try to find a solution to resolve it.

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 16, 2016 8:15 AM
    Moderator
  • Dear Cole Wu,

    As suggested here is the link of the demo:

     https://1drv.ms/f/s!Ar2m2-zgErt-p3GDX3Qc4bG7xtmC

    Sir, I would like to share with you that while I was replicating my original project to be reproduced as a demo, one thing I could not understood why the query didn't execute in the manner it executes in the original project.

    In the demo, please notice that the query returns count for the cases equal to the count of disposed off cases where count of disposed off cases is correct but the number of cases incorrect. In the original project the same query returns more number of cases than the cases are in the database.

    Further, I have commented the line in the query which stores names of the Head Court into a variable for in the demo it throws exception but in the original application it does not throw any exception but displays the Head Court name for as many time as the count of the cases.

    And the last thing to mention that I have used Localdb to connect to the database and the database is in the App_Data folder.

    I hope, you would find the information provided above helpful in solving the problem. 

    Warm Regards,

    Arun

    Saturday, September 17, 2016 4:14 AM
  • Hi ArunKhatri,

    I have downloaded your solution and reproduce your issue on my side, it seems that you don't assign HeadCourtName to entity named vmSummary, please modify your code like this:

    #vmSummary

     public class vmSummary
        {
            public int HeadCourt { get; set; }
            public string HeadCourtName { get; set; }
            //public List<string> HeadCourtName { get; set; }
            public int Cases { get; set; }
            public int CasesDisposedOff { get; set; }
            //public int Balance { get; set; }
    
        }


    #Controller

     public ActionResult Summary()
            {
    
                
                var query = (from hc in db.HeadCourts
                            join c in db.Courts on hc.fldUCId equals c.fldUCId
                            join ca in db.Cases on c.fldCourtId equals ca.fldCourtId
                            join s in db.Status on ca.fldCaseId equals s.fldCaseId
                            select new
                            {
                                HeadCourt= hc.fldUCId,
                                HeadCourtName = hc.fldName,
                                Cases = ca.fldCaseId,
                                CasesDisposed = s.fldDispose
                            }).ToList(); // please add ToList method.
    
                var summary = query.GroupBy(t => new {t.HeadCourt, t.HeadCourtName }).Select(t => new vmSummary
                {
                    HeadCourt = t.Key.HeadCourt,
                    HeadCourtName = t.Key.HeadCourtName,
                    Cases = t.Count(),
                    CasesDisposedOff = t.Where(x => !x.CasesDisposed.Contains("P")).Count()
                    //HeadCourtName = t.Where(x => t.Select(p => p.HeadCourt).Contains(x.HeadCourt)).Select(x => x.HeadCourtName).ToList()
                    //Balance = t.Where(x => x.CasesDisposed.Contains("P")).Count()
                }).ToList();
    
                return View(summary);
    
            }

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 19, 2016 7:21 AM
    Moderator
  • Dear Cole Wu,

    After modification, I got the display as under:

    Unfortunately, the total number of cases under different Head Courts is not coming up correctly while number of disposed off cases is correct.

    When I removed the table Status from the join then the correct total number of cases comes up. Here is the screenshot:

    Therefore, it seems the query still needs a bit of fine-tuning so as to get the correct total number of cases under different Head Court along with the disposed-off cases.

    Thanks in advance for putting in your best efforts.

    Warm Regards,

    Arun

    Monday, September 19, 2016 9:34 AM
  • Hi ArunKhatri,

    >>When I removed the table Status from the join then the correct total number of cases comes up. Here is the screenshot:

    Could you please provide related code about the result. and what kind of result you want to display?

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 27, 2016 8:40 AM
    Moderator