title: How to optimize query where we are using transaction slab?
body: <p>I need to generate an aging report as per transaction slab i.e. on the interval of 2, 5, 10, and so on. Users will select a report start date and end date and then we need to generate a report for that particular date range. The report should be in such a way that transactions should be divided in slabs; like 0-2, 3-4, 5-6, 6-8, and greater than 8 days. I wrote a subquery for each slab and it gave me the required data. But since the data increases on daily basis it is taking too much time. I tried indexing as well, but it is not improving performance much.</p>
  <pre><code>select id_ledger as ledger_id,
         customer_id,
         title     as ledger,
         (
             select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
             from journal_voucher_details
             where cr_amount = 0
               and (('2021-10-23' - transaction_date) &lt;= (2))
               and ledger_id = l.customer_id
               and transaction_date &gt;= '2021-10-01'
               and transaction_date &lt;= '2021-10-24'
             group by ledger_id
         )         as slab1,
         (
             select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
             from journal_voucher_details
             where cr_amount = 0
               and (('2021-10-23' - transaction_date) &lt;= (2 * 2))
               and (('2021-10-23' - transaction_date) &gt; (2 * 1))
               and ledger_id = l.customer_id
               and transaction_date &gt;= '2021-10-01'
               and transaction_date &lt;= '2021-10-24'
             group by ledger_id
         )         as slab2,
         (
             select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
             from journal_voucher_details
             where cr_amount = 0
               and (('2021-10-23' - transaction_date) &lt;= (2 * 3))
               and (('2021-10-23' - transaction_date) &gt; (2 * 2))
               and ledger_id = l.customer_id
               and transaction_date &gt;= '2021-10-01'
               and transaction_date &lt;= '2021-10-24'
             group by ledger_id
         )         as slab3,
         (
             select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
             from journal_voucher_details
             where cr_amount = 0
               and (('2021-10-23' - transaction_date) &lt;= (2 * 4))
               and (('2021-10-23' - transaction_date) &gt; (2 * 3))
               and ledger_id = l.customer_id
               and transaction_date &gt;= '2021-10-01'
               and transaction_date &lt;= '2021-10-24'
             group by ledger_id
         )         as slab4,
         (
             select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
             from journal_voucher_details
             where cr_amount = 0
               and (('2021-10-23' - transaction_date) &gt; (2 * 4))
               and ledger_id = l.customer_id
               and transaction_date &gt;= '2021-10-01'
               and transaction_date &lt;= '2021-10-24'
             group by ledger_id
         )         as slab5,
         (
             select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
             from journal_voucher_details
             where cr_amount = 0
               and 1 = 1
               and ledger_id = l.customer_id
               and transaction_date &gt;= '2021-10-01'
               and transaction_date &lt;= '2021-10-24'
             group by ledger_id
         )         as balance
  from ledgers l;
  </code></pre>
  <p>NB:
  2021-10-1 ==&gt; start date</p>
  <p>2021-10-24 ==&gt; end date</p>
  <p>2021-10-23 ==&gt; report date</p>
  <p>and 2 is selected as slab number.</p>
  <p>How can I improve the performance of this query?</p>
  <p>Thanks in advance.</p>