1 Reply Latest reply on Jan 7, 2019 10:40 AM by Michael Hesser

    Need help to find minimum watermark account across range of dates

    Brandon Mendis

      We have a table for transactions :

       

       

      1. Txn_id                : unique key

      2. txn_datetime      : 12-30-2018 16:43:27

      3. txn_type             : type1, type2, type3

      4. txn_group_type    : g1, g2, g3, g4

      5. txn_sub_group_type : sg1, sg2, sg3  Group type  to Subgroup type is one to many relationship.

      6. firm_id                 : four firms . 1,2,3,4

      7. department_name   : each firms has some departments. Department names are unique across firms.

                             Two different firms can not have Departments with same name.

                             So firm -> department is one to many relationship.

      8. net_dollar_amount : Dollar amount in decimal

      9. pay_or_receive : values can be "Pay" or "Receive". If it is Pay , meaning negative txn. Receive should be positive txn.

       

       

      Put filters on txn_type, txn_group_type, txn_sub_group_type , firm_id , department_name

       

      Also put a filter on txn_datetime as range of dates. Each day's beginning starts with ZERO amount.

       

      On each date in the range, we need to find out the cumulative dollar amount in chronological order for that date, and then figure out the date, with minimal watermark in the range. We need to show all the transactions on that date as a continuous graph of cumulative to

       

      So its  a 2 step problem :
      Step 1 :
      1. For each date in the range, find out the cumulative amount for filtered transactions and keep track of minimal amount value, it touched, each date
      2. Find the date among all the dates above, when account reached its minimum watermark.

       

      Step 2:

       

      1. For that particular date found in Step 1, show all the transaction as a continuous curve Pay and receive as cumulative net amount.