1

I have created the balance sheet using PHP MYSQL, for admin. Now I want to filter only Debtors and only Creditors from the table.

Here are Debtors and Creditors means:

Debtors: A debtor is a person or enterprise that owes money to another party

Creditors: A creditor is a person, bank, or another enterprise that has lent money or extended credit to another party

Below is my table structure:

enter image description here

I am using below queries to get both different:

To get Debtors only:

SELECT *, 
           Sum(Coalesce(CASE 
                      WHEN action_type = 'dr' THEN amount 
                    end, 0)) AS total_debits, 
       Sum(Coalesce(CASE 
                      WHEN action_type = 'cr' THEN amount 
                    end, 0)) AS total_credits, 
       Sum(Coalesce(CASE 
                      WHEN action_type = 'cr' THEN amount 
                    end, 0)) - Sum(Coalesce(CASE 
                                              WHEN action_type = 'dr' THEN 
                                              amount 
                                            end, 0)) AS total_debtors, 
       IF(Sum(Coalesce(CASE 
                         WHEN action_type = 'cr' THEN amount 
                       end, 0)) - Sum(Coalesce(CASE 
                                                 WHEN action_type = 'dr' THEN 
                                                 amount 
                                               end, 0)) > 0, 'dr', 'cr') AS balance 
FROM tbl_balancesheet 
GROUP BY area_id 
HAVING balance = 'cr' AND total_debtors <> 0 ORDER BY total_debtors DESC;

To get Creditors only:

SELECT *,
   Sum(Coalesce(CASE 
                  WHEN action_type = 'dr' THEN amount 
                end, 0)) AS total_debits, 
   Sum(Coalesce(CASE 
                  WHEN action_type = 'cr' THEN amount 
                end, 0)) AS total_credits, 
   Sum(Coalesce(CASE 
                  WHEN action_type = 'cr' THEN amount 
                end, 0)) - Sum(Coalesce(CASE 
                                          WHEN action_type = 'dr' THEN 
                                          amount 
                                        end, 0)) AS total_debtors, 
   IF(Sum(Coalesce(CASE 
                     WHEN action_type = 'cr' THEN amount 
                   end, 0)) - Sum(Coalesce(CASE 
                                             WHEN action_type = 'dr' THEN 
                                             amount 
                                           end, 0)) > 0, 'dr', 'cr') AS balance 
    FROM tbl_balancesheet 
    GROUP BY area_id
HAVING balance = 'dr' AND total_debtors <> 0 ORDER BY total_debtors DESC;

Now, I have issue with the calculation and not getting proper Debtors and Creditors

Also, I have uploaded the dump on SQL Fiddle. Below is the URL:

URL: http://www.sqlfiddle.com/#!9/badc3f/5

Plese help me!!! Thanks

0

Test

SELECT -- client_id, 
       area_id, 
       SUM(CASE WHEN action_type='dr' 
                THEN amount END) total_debits,
       SUM(CASE WHEN action_type='cr' 
                THEN amount END) total_credits,
       SUM(CASE WHEN action_type='cr' 
                THEN amount END) - SUM(CASE WHEN action_type='dr' 
                                            THEN amount END) total_debtors,
       CASE WHEN SUM(CASE WHEN action_type='cr' 
                          THEN amount END) - SUM(CASE WHEN action_type='dr' 
                                                      THEN amount END) > 0 
            THEN 'dr' 
            ELSE 'cr' END balance
FROM tbl_balancesheet
GROUP BY -- client_id, 
         area_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.