Accumulated balance with multiple accounts - SQL

Asked

Viewed 383 times

-1

I’m having trouble creating an account statement that has the option to filter all accounts and/or only a few accounts.

I am using this SQL to appear these record this way

select
  bancos.id,
  bancos.date,
  bancos.type,
  bancos.description,
  bancos.account_plan_id,
  bancos.money * case
    when bancos.type = 2 then -1
    else 1
  end valor,
  (
    select
      sum(
        s.money * case
          when s.type = 2 then -1
          else 1
        end
      )
    from
      bancos as s
    where
      s.date < bancos.date
      or s.date = bancos.date
      and s.id <= bancos.id
  ) as saldo
from
  `bancos`
where
  `date` between '2020-01-01'
  and '2020-02-29'
  and `banco_id` in ('1', '6', '7')
  and `company_id` = 1
order by
  `bancos`.`date` asc


<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" rel="stylesheet"/>
<table class="table table-bordered table-reponsive" >
            <thead>
                    <tr>
                            
                            <th>Data</th>
                            <th>Conta</th>
                            <th>Plano de Contas</th>
                            <th>Descrição</th>                            
                            <th>Valor</th>
                            <th>Saldo</th>
                            
                        </tr>
                
                </thead>
    
                <tbody>
                                                <tr id="lists">                           
                        
                            <td>01/01/2020</td>
                            <td>Banco 1</td>
                            <td>Recebimento em Dinheiro</td>
                            <td>Teste</td>                            
                            <td class="text-green"> 500,00  </td>
                            <td> 1.150,00</td>
                           
                           
                        </tr>   
                                            <tr id="lists">                           
                        
                            <td>10/01/2020</td>
                            <td>Banco 2</td>
                            <td>OBRAS</td>
                            <td>sd</td>                            
                            <td class="text-danger"> -150,00 </td>
                            <td> 1.000,00</td>
                           
                           
                        </tr>   
                                            <tr id="lists">                           
                          
                            <td>28/01/2020</td>
                            <td>Banco 1</td>
                            <td>Internet</td>
                            <td>sdfg</td>                            
                            <td class="text-danger"> -100,00</td>
                            <td> 900,00</td>
                           
                           
                        </tr>   
                                            <tr id="lists">                           
                        
                            <td>28/01/2020</td>
                            <td>Banco 1</td>
                            <td>Recebimento em Dinheiro</td>
                            <td>sd</td>                            
                            <td class="text-green"> 500,00 </td>
                            <td> 1.400,00</td>
                           
                           
                        </tr>   
                                            <tr id="lists">                           
                         
                            <td>29/01/2020</td>
                            <td>Banco 1</td>
                            <td>Recebimento em Dinheiro</td>
                            <td>sd</td>                            
                            <td class="text-green"> 520,00  </td>
                            <td> 1.920,00</td>
                           
                           
                        </tr>   
                                            <tr id="lists">                           
                         
                            <td>30/01/2020</td>
                            <td>Banco 1</td>
                            <td>Recebimento em Dinheiro</td>
                            <td>df</td>                            
                            <td class="text-green"> 500,00  </td>
                            <td> 2.420,00</td>
                           
                           
                        </tr>   
                                            <tr id="lists">                           
                         
                            <td>30/01/2020</td>
                            <td>Banco 1</td>
                            <td>Recebimento em Dinheiro</td>
                            <td>sd</td>                            
                            <td class="text-green"> 500,00  </td>
                            <td> 2.920,00</td>
                           
                           
                        </tr>   
                         <tr id="lists">                          
                          
                            <td>30/01/2020</td>
                            <td>Banco 2</td>
                            <td>Internet</td>
                            <td>df</td>                            
                            <td class="text-danger"> -150,00  <small title="Pago" class="label pull-right bg-green"><i class="fa fa-check"></i></small></td>
                            <td> 2.770,00</td>
                           
                           
                        </tr>   
                        <tr >  
                           
                            <td>31/01/2020</td>
                            <td>Banco 3</td>
                            <td>Recebimento em Dinheiro</td>
                            <td>df</td>                            
                            <td class="text-green"> 150,00 </td>
                            <td> 2.920,00</td>
                           
                           
                        </tr>   
                                   
                </tbody>
          </table>

There it is all right, but when I put to filter only 1 account, the sum of the accumulated balance, does not remain solely of that account. I am not able to build the query correctly to meet the two situations all notes and only one or 2 accounts, but add the balance dynamically.

mysql sql laravel query

  • Probably you will not be able to do this in the bank, the correct is you add on the front, or else you will have to calculate in the bank each type of filter that will have to be carried out and create a new column that with the type of filter and unify this base with Union all.

  • After several attempts I managed to solve the problems, I put the ids of the banco_id in subselect

1 answer

0

The problem was solved as follows, I informed the bank ids within subselect

select
  bancos.id,
  bancos.date,
  bancos.type,
  bancos.description,
  bancos.account_plan_id,
  bancos.money * case
    when bancos.type = 2 then -1
    else 1
  end valor,
  (
    select
      sum(
        s.money * case
          when s.type = 2 then -1
          else 1
        end
      )
    from
      bancos as s
    where
      s.banco_id in ('1','6','7') <<< Linha adicionada
      and s.date < bancos.date
      or s.date = bancos.date
      and s.id <= bancos.id
  ) as saldo
from
  `bancos`
where
  `date` between '2020-01-01'
  and '2020-02-29'
  and `banco_id` in ('1', '6', '7')
  and `company_id` = 1
order by
  `bancos`.`date` asc

With that the problem was solved.

Browser other questions tagged mysql sql laravel query

You are not signed in. Login or sign up in order to post.