Mysql Query Problem

I need to select Receipts that have not been deposited.
A person may pay by Cash, Cheque or by Cash and Cheque.
The deposit date fields (on the same row) are 'DepositDateCash' and 'DepositDateCheque'.
The amount fields are 'decCash' or 'decCheque'.

A person who pays in both cash and cheque may have two different deposit dates. For example, their cash deposit might happen on one day and their cheque deposit may not have happened yet.

In the above scenario I want to display the 'decCheque' amount was not deposited (amongst other fields).

This is part of the select query I'm trying to figure out.

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

Open in new window

....
where depositdate is null
JohnnyBCJAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RyanProject Engineer, ElectricalCommented:
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
0
JohnnyBCJAuthor Commented:
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.
0
RyanProject Engineer, ElectricalCommented:
I would have normalized the table better, ideally. (DepositType, DepositDate, DepositAmount)

So you want all rows where at least 1 date is missing?  This situation doesn't make sense to me, since they may only be paying with one or the other, thus one of the 2 will always be null?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

JohnnyBCJAuthor Commented:
This is MySQL sorry.
0
Kevin CrossChief Technology OfficerCommented:
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.
0
Kevin CrossChief Technology OfficerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnnyBCJAuthor Commented:
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
0
Kevin CrossChief Technology OfficerCommented:
Okay, then the first WHERE example is what you want.

SELECT ...
WHERE (decCash > 0 AND DepositDateCash IS NULL)
OR  (decCheque > 0 AND DepositDateCheque IS NULL)

:)
0
JohnnyBCJAuthor Commented:
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.
0
Kevin CrossChief Technology OfficerCommented:
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)
0
JohnnyBCJAuthor Commented:
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!
0
JohnnyBCJAuthor Commented:
Thanks again!
0
Kevin CrossChief Technology OfficerCommented:
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
;

Open in new window


Anyway, good luck!

Best regards and happy coding,

Kevin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.