• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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
0
JohnnyBCJ
Asked:
JohnnyBCJ
  • 6
  • 5
  • 2
3 Solutions
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now