7 Replies Latest reply on Nov 19, 2018 3:12 PM by Swaminathan Jayaraman

    Help writing query on joined tables

    Aleksandra Shander

      I need to be able to aggregate entries for an incident in a table and display the calculated total in a viz.


      Final product should look like this:


      Incident ID * Project ID * Calculated Status Sum of Specific Statuses

      121939      * 12             * 19.95

      121939      * 16             * 31.69


      I need to be able to aggregate statuses, based on combination of incident id and project id, and then display them together as above.


      This is the logic I want, in English:


      Where incident id = x and project id = y, sum (enddate - startdate) when status in (a, c, d [specific statuses, not all])


      Table A - Incident Table

      Incident Id

      Project Id


      Table B - CRMStatusHistory Table

      CRM Status





      CRMClosed (Boolean)




      I've attached a sample data pull from Table B, which corresponds to the above example. You will note that 121939, project id = 16, excludes "Pending Client Response" status amounts.