How to add the opening cash balance with the total opening sales?-CodePudding

I have the following table where the seller id_seller performs the opening with the start date of activities and the opening balance. It will be known that this box is open by having the status at 0:

tbl_cashier:

Taking into account the opening date of the box, I need to list the total sales that the seller has made from its opening date until the seller closes his box:

tbl_invoice:

Considering the above, prepare the following query:

SELECT
ca.id_cashier,
ca.open_date,
ca.open_amount,
ca.close_date,
ca.open_amount   SUM(i.amount_total) AS total_amount,
ca.status
FROM tbl_cashier ca
LEFT JOIN tbl_invoice i ON ca.id_seller=i.id_seller
WHERE ca.id_seller=6
AND i.payer_date BETWEEN ca.open_date AND ca.close_date
GROUP BY ca.id_cashier

Having the following output:

The result is correct since it adds the initial opening balance 100.00 the two sales that were made from the beginning to the closing 10.00 10.10, this gives us an equal of 120.10

Now the problem is that I need both records to be shown, that is, the closed box and the open box of the tbl_cashier table and when adding this WHERE i.payer_date BETWEEN ca.open_date AND ca.close_date condition it only shows me the records of the closed boxes, then, ¿ How can I show both data?

I tried adding this condition but I can't get the open box in the query listing:

AND i.payer_date BETWEEN ca.open_date AND ca.close_date OR i.payer_date BETWEEN ca.open_date AND ca.open_date

CodePudding user response：

For cases like this, where one of your BETWEEN bounds may be NULL, you can't use BETWEEN, and must instead explicitly check whether each bound is NULL. In your particular scenario, it will never be the case that ca.open_date is NULL, so we don't need to check that condition.

Note also that if you want to correctly compute total_amount for sessions where there were no invoices, it is correct to do a left join (as you are already doing), but you will need to coalesce the output of SUM(), as in that case the group's set of i.amount_total values will be {NULL}, meaning SUM(i.amount_total) will be NULL. If you don't do this, then you will see total_amount as NULL in the result set, rather than the opening/closing balance. You must also ensure that such "empty left joins" are retained in the result set by allowing i.id_invoice IS NULL in your WHERE clause.

Putting all these things together gives us the following:

SELECT
    ca.id_cashier,
    ca.open_date,
    ca.open_amount,
    ca.close_date,
    ca.open_amount   COALESCE(SUM(i.amount_total), 0) AS total_amount,
    ca.status
FROM
    tbl_cashier AS ca
    LEFT JOIN tbl_invoice AS i USING (id_seller)
WHERE
    ca.id_seller = 6
    AND (
        i.id_invoice IS NULL -- No invoices were generated whilst this box was open. Make sure we still return this row.
        OR (
            i.payer_date >= ca.open_date -- Only show invoices generated after the box was opened
            AND (
                ca.close_date IS NULL -- The box is still open / hasn't yet been closed
                OR i.payer_date <= ca.close_date -- Only show invoices generated before the box was closed
            )
        )
    )
GROUP BY 1;