need help with a sql query...

Hello, my table structure is as follows:
transaction table:
account_num        cycle          item     amount      date
 001                        01              A           100         2007/10/01
 001                        01              B           100          2007/10/01
 001                        01           Payment  -100         2007/10/15
 001                        02              A           100         2007/11/01
 001                        02              B           100          2007/11/01
 002                        03              A            100         2007/11/01
 002                        03              B            100         2007/11/01
 002                        03           Payment    -200       2007/11/30
 001                        03              A            100         2007/12/01
 001                        03              B            100         2007/12/01
 002                        03              A            100         2007/12/01
 002                        03              B            100         2007/12/01


Customer_details table :
account_num   items       price         qty
 001                   A             50             2
 001                   B              50             2
 002                   A             50             2
 002                   B              50             2

I am looking for a query to be able to generate the following output :

account_num     balance_due        monthly_charge    payments_last_30_days    31_60days   61_90_days
001                            500                           200                          0                                0                -100
002                            200                           200                         0                               -200                0

How can I get an output of this form? Thank you for the help.

A
aej1973Asked:
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.

BenMorelCommented:
Hi,
Probably not a good idea to put all this stuff into one single query, but that should do the trick.
payments_last_xxx are incorrect in your post, we are in January, not in December :)
Regards,
Ben
SELECT
	T.account_num,
	T.balance_due,
	C.monthly_charge,
	T.payments_last_30_days,
	T.`31_60days`,
	T.`61_90_days`
FROM
(
	SELECT
		account_num,
		SUM(amount) AS balance_due,
		SUM(IF(item = 'Payment' AND DATEDIFF(NOW(), date) <= 30, amount, 0)) AS payments_last_30_days,
		SUM(IF(item = 'Payment' AND DATEDIFF(NOW(), date) > 30 AND DATEDIFF(NOW(), date) <= 60, amount, 0)) AS 31_60days,
		SUM(IF(item = 'Payment' AND DATEDIFF(NOW(), date) > 60 AND DATEDIFF(NOW(), date) <= 90, amount, 0)) AS 61_90_days
	FROM transaction
	GROUP BY account_num
) T
 
CROSS JOIN
(
	SELECT 
		account_num,
		SUM(qty * price) AS monthly_charge
	FROM Customer_details
	GROUP BY account_num
) C
 
WHERE T.account_num = C.account_num

Open in new window

0
aej1973Author Commented:
Thank you Ben, this is what I was looking for, but I have one more question;
I need to also capture the last payment data. For eg, the output should be;

account_num     balance_due        monthly_charge    30_days    31_60   61_90    last_payment
001                            500                           200            0                    0       -100           2007/10/15
002                            200                           200            0                 -200        0             2007/11/30

Thank you.
A        
0
BenMorelCommented:
Hi, this should work :
SELECT
	T.account_num,
	T.balance_due,
	C.monthly_charge,
	T.payments_last_30_days,
	T.`31_60days`,
	T.`61_90_days`,
	T.last_payment
FROM
(
	SELECT
		account_num,
		SUM(amount) AS balance_due,
		SUM(IF(item = 'Payment' AND DATEDIFF(NOW(), date) <= 30, amount, 0)) AS payments_last_30_days,
		SUM(IF(item = 'Payment' AND DATEDIFF(NOW(), date) > 30 AND DATEDIFF(NOW(), date) <= 60, amount, 0)) AS 31_60days,
		SUM(IF(item = 'Payment' AND DATEDIFF(NOW(), date) > 60 AND DATEDIFF(NOW(), date) <= 90, amount, 0)) AS 61_90_days,
		MAX(IF(item = 'Payment', date, NULL)) AS last_payment
	FROM transaction
	GROUP BY account_num
) T
 
CROSS JOIN
(
	SELECT 
		account_num,
		SUM(qty * price) AS monthly_charge
	FROM Customer_details
	GROUP BY account_num
) C
 
WHERE T.account_num = C.account_num

Open in new window

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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

aej1973Author Commented:
Thank you, this works great. You had mentioned earlier "Probably not a good idea to put all this stuff into one single query". What did you mean by this, what would be the best way to do this? Thanks again for all the help.

A
0
aej1973Author Commented:
Thanks a ton!
0
BenMorelCommented:
To avoid a database overhead, I'd just do two separate queries (as seen in the code snippet below).
You'd get the desired fields in two times, but I think this would be quicker for the db (no need for a cross join in this case). However don't bother if it works well like that.
Thank you for the points !
Ben
	SELECT
		account_num,
		SUM(amount) AS balance_due,
		SUM(IF(item = 'Payment' AND DATEDIFF(NOW(), date) <= 30, amount, 0)) AS payments_last_30_days,
		SUM(IF(item = 'Payment' AND DATEDIFF(NOW(), date) > 30 AND DATEDIFF(NOW(), date) <= 60, amount, 0)) AS 31_60days,
		SUM(IF(item = 'Payment' AND DATEDIFF(NOW(), date) > 60 AND DATEDIFF(NOW(), date) <= 90, amount, 0)) AS 61_90_days,
		MAX(IF(item = 'Payment', date, NULL)) AS last_payment
	FROM transaction
	GROUP BY account_num
 
 
and then :
 
	SELECT 
		account_num,
		SUM(qty * price) AS monthly_charge
	FROM Customer_details
	GROUP BY account_num

Open in new window

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.