Mysql Query Problem

You need to test for Cash And Cheque first, since either of them being true will break out after their case.

Select
CASE REC.strPaymenttype when "Cash and Cheque" then "DepositDateCheque" as 'DepositDate'
when "Cash" then "DepositDateCash" as 'DepositDate'
when "Cheque" then "DepositDateCheque" as 'DepositDate'

END

I understand the point you're making and I agree with what you said. It doesn't solve my problem tho.

My problem is that "Cash and Cheque" have two amounts (decCash, decCheque) and two possible deposit dates (DepositDateCash, DepositDateCheque).

The dates have 4 possible situations:
Both date values have nulls (Neither has been deposited)
Both date values have dates (The receipt has been deposited)
DepositDateCash has a date but DepositDateCheque does not. (Cash is deposited but cheque has not)
DepositDateCheque has a date but DepositDateCash does not. (Cheque is deposited but cash has not).

I want to select all receipts that have not been deposited and the amounts associated with them. I don't know if there is a valid SQL query that does what I need it to do.

This is MySQL sorry.

By the way, this is NOT valid SQL:

Select
CASE REC.strPaymenttype when "Cash" then "DepositDateCash" as 'DepositDate'
when "Cheque" then "DepositDateCheque" as 'DepositDate'
when "Cash and Cheque" then "???"
END

It would be more like:

SELECT
   CASE REC.strPaymenttype WHEN 'Cash' THEN DepositDateCash ... END AS `DepositDate`

In other words, the alias goes on the outside. That does not really fix your problem, but figure let's start with clean syntax.

mwvisa1:
By the way, this is NOT valid SQL:

Select
CASE REC.strPaymenttype when "Cash" then "DepositDateCash" as 'DepositDate'
when "Cheque" then "DepositDateCheque" as 'DepositDate'
when "Cash and Cheque" then "???"
END

It would be more like:

SELECT
   CASE REC.strPaymenttype WHEN 'Cash' THEN DepositDateCash ... END AS `DepositDate`

In other words, the alias goes on the outside. That does not really fix your problem, but figure let's start with clean syntax.

Thank you for pointing this out to me. I would have overlooked this!





mwvisa1:
Do you have a sample of your actual data?
It may clarify if decCash and decCheque are NULL or 0 when not used, i.e., if a person pays only in 'Cash' is decCheque NULL or 0?

Based on that, you can then do this in your WHERE clause:

WHERE (decCash > 0 AND DepositDateCash IS NULL) ...

Or:

WHERE (decCheque IS NOT NULL AND DepositDateCheque IS NULL) ...

Whichever is correct, write two such conditions and connect with OR and you should get all rows that have either a cash or cheque outsanding or both.


If cash is the form of payment, decCash > 0 and decCheque is 0.
if cheque is the form of payment, decCash is 0 and decCheque > 0.
if both cash and cheque is used, both decCash > 0 and decCheque > 0

That gets me 95% of the way there but not quite 100%.

I need to be able to return the correct amount associated with what hasn't been deposited.

I need to return decCash as amount if DepositDateCash is null and decCash > 0.
I need to return decCheque as amount if DepositDateCheque is null and decCheque > 0.

I'd need to return both sum(decCash + decCheque) if both DepositDateCash is null and decCash > 0 and DepositDateCheque is null and decCheque > 0.

Just add them up in the same fashion.

SELECT ...
     , CASE WHEN DepositDateCash IS NULL THEN decCash ELSE 0 END
       + CASE WHEN DepositDateCheque IS NULL THEN decCheque ELSE 0 END AS `Amount Outstanding`
WHERE (decCash > 0 AND DepositDateCash IS NULL)
OR  (decCheque > 0 AND DepositDateCheque IS NULL)

I'm actually going to attempt to solve this problem in a little different way than what you suggested but I will be rewarding you the points for getting me on the right track. Thank you!

Thanks again!

Sounds good and thanks! I am glad you included the comment about normalization in the solution as I would agree that this is typically handled via ledger, i.e., rows versus columns. You have multiple invoices or payment rows that each have their own payment type, amount, and date. Grabbing all open invoices then becomes a very simple select and amount due a straight-forward SUM(amount) as payment type becomes inconsequential at that point. :)

You may even be able to do this with a view real quick:

SELECT 'Cash' AS PaymentType
     , decCash AS PaymentAmount
     , DepositDateCash AS DepositDate
FROM your_table
WHERE decCash > 0
UNION ALL
SELECT 'Cheque' AS PaymentType
     , decCheque AS PaymentAmount
     , DepositDateCheque AS DepositDate
FROM your_table
WHERE decCheque > 0
;

Select allOpen in new window

Anyway, good luck!

Best regards and happy coding,

Kevin