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

      StartDate

      EndDate

      ProjectID

      IncidentID

      CRMClosed (Boolean)

      CRMClosedDate

      SequenceNo

       

      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.