14 Replies Latest reply on Jun 12, 2014 10:01 PM by Nick Lupis

    Multi Customer and Account Balance Transactions with no detail to calculate aggregated balances

    Nick Lupis

      Hi

       

      I have what I thought would be a straight forward query however when looking at it as there is no detail in the table the query won't work. Note: Initially I need a Table.

       

      Example:

      TypeCustomerAccountBalance
      PersonalNick1100
      PersonalEmily1100
      PersonalPeter2200
      PersonalPaul3300
      StaffJoan3

      300

       

      Grouping at Type Level the following is achieved: (I need this as I want filter out staff customers)

       

      Type
      Personal700.0
      Staff300.0
      1000

       

      However as we know our account balances don't double with 2 people and the answer should be weighted/shared across the customer types but it needs to be dynamic based on the filter as if aggregate to the type, the values you see are:

       

      TypeBalance
      Personal450
      Staff150
      600

       

      (200/2)+(200/1)+(300/2) =  600

       

      Then filtering out staff we should get just the overall account balances ($600)

       

      TypeBalance
      Personal600
      600

       

      i.e (200/2)+(200/1)+(300/1) = 600

       

      But since we don't have the customer & account information in the query the table can't figure it out.

       

      Am I missing something really obvious??? Please help this is doing my head in....

       

      Thanks in advance

       

      Nick.

       

      Message was edited by: Nick Lupis Updated to clarify a little further.