Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Union query help

Posted on 2013-11-29
8
359 Views
Last Modified: 2013-11-30
I am trying to work through this problem and need help.

The data looks like this:

ROID   TransactionType                               Amount
1          Invoice                                   1500
1          Payment - Undeposited Funds              1400
1          Payment - Undeposited Funds               500
2          Invoice                                   2000
2          Payment - Undeposited Funds              1500
2          Payment - Undeposited Funds               500

Open in new window


The results I am looking for is just to see ROID 1 with the amount of 400 which is the total of the multiple payments less the amount of the invoice. In this case they overpaid the invoice by $400.

I was thinking of a union query but I am not getting the results I am expecting. This is what I have so far:
SELECT 
	accountid,
	sum(amount) AS amount
FROM
	(
		(
			SELECT
				ledger.accountid,
				ledger.transactiontype,
				ledger.amount
			FROM
				ledger
			WHERE
				ledger.transactiontype = 'Invoice'
		)
		UNION 
			(
				SELECT
					ledger.accountid,
					ledger.transactiontype,
					Sum(ledger.amount) * - 1 AS amount
				FROM
					ledger
				WHERE
					ledger.transactiontype = 'Payment - Undeposited Funds'
			)
	) x

GROUP BY
	accountid
ORDER BY
	amount

Open in new window


Any thoughts, alternatives, etc is appreciated.
0
Comment
Question by:ckelsoe
8 Comments
 
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 200 total points
ID: 39686410
I think that you can do something with a CASE statement and come up with the correct data:

SELECT SUM(CASE WHEN TransactionType = 'Payment - Undeposited Funds' THEN (Amount*-1) Else Amount END) AS TOTAL
FROM ledger
GROUP BY accountid
ORDER BY amount
0
 

Author Comment

by:ckelsoe
ID: 39686415
Well that sort of does what I need. I was expecting around 10 results and this only returned 4 with the totals way off. I may need to rethink how I deal with the problem.

SELECT
  roid,
	SUM(
		CASE
		WHEN TransactionType = 'Payment - Undeposited Funds' THEN
			(Amount *- 1)
		ELSE
			Amount
		END
	) AS TOTAL
FROM
	ledger
GROUP BY
	roid
HAVING
	Round(Sum(ledger.amount), 2) < - 1

Open in new window


I also need to test for the transactiontype = 'Invoice' as there are other transaction types that I do not want to include.
0
 
LVL 4

Assisted Solution

by:rshq
rshq earned 100 total points
ID: 39686416
Hi
 Please test this
SELECT 
	accountid,
	sum(amount) AS amount
FROM
	(
		(
			SELECT
				ledger.accountid,
				ledger.transactiontype,
				ledger.amount
			FROM
				ledger
			WHERE
				ledger.transactiontype = 'Invoice'
		)
		UNION 
			(
				SELECT
					ledger.accountid,
					Min(ledger.transactiontype) as ledger.transactiontype ,
					Sum(ledger.amount) * - 1 AS amount
				FROM
					ledger
				WHERE
					ledger.transactiontype = 'Payment - Undeposited Funds'
                                Group By ledger.accountid
			)
	) x

GROUP BY
	accountid
ORDER BY
	amount

Open in new window

0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:ckelsoe
ID: 39686426
Ok - this is returning the results I am looking for I think. I am going to take a look again in the morning to make sure. Thanks

SELECT accountid, 
       Sum(amount) AS amount 
FROM   ((SELECT ledger.accountid, 
                ledger.transactiontype, 
                Round(ledger.amount, 2) AS amount 
         FROM   ledger 
         WHERE  ledger.transactiontype = 'Invoice') 
        UNION 
        (SELECT ledger.accountid, 
                Min(ledger.transactiontype)       AS transactiontype, 
                Round(Sum(ledger.amount) * -1, 2) AS amount 
         FROM   ledger 
         WHERE  ledger.transactiontype = 'Payment - Undeposited Funds' 
         GROUP  BY ledger.accountid)) x 
GROUP  BY accountid 
HAVING Round(Sum(amount), 2) < -1

Open in new window

0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 39686754
I believe the "Having" clause you keep adding is causing you problems.  Try:
SELECT
    roid,
    SUM(CASE WHEN TransactionType = 'Payment - Undeposited Funds' THEN
           Amount * -1
        ELSE
           Amount
        END
        ) AS TOTAL
FROM ledger
WHERE TransactionType = 'Payment -Undeposited Funds'
   OR TransactionType = 'Invoice'
GROUP BY roid

Open in new window

This would give you:

ROID   Total
1          -400
2               0

If you only want those where the Total is < 0 (a negative balance) then turn that into a subquery and add a where clause:
SELECT ROID, Total
FROM (
SELECT
    roid,
    SUM(CASE WHEN TransactionType = 'Payment - Undeposited Funds' THEN
           Amount * -1
        ELSE
	   Amount
        END
        ) AS TOTAL
FROM ledger
WHERE TransactionType = 'Payment -Undeposited Funds'
   OR TransactionType = 'Invoice'
GROUP BY roid
) as Temp
HAVING Temp.Total < 0

Open in new window

0
 
LVL 29

Assisted Solution

by:Göran Andersson
Göran Andersson earned 100 total points
ID: 39686821
Filter out the records that you want to use in the summation, group them on the account id, and sum up the records:

select
    accountid,
    total = sum(amount * case when transactiontype = 'Invoice' then 1 else -1 end)
from
    ledger
where
    transactiontype  in ('Invoice', 'Payment - Undeposited Funds')
group by
    accountid

Open in new window

0
 
LVL 18

Accepted Solution

by:
Jerry Miller earned 200 total points
ID: 39686988
This one will only add in the two transactionTypes that you are looking for:

SELECT ROID, Total
FROM (SELECT
    roid,
    SUM(CASE WHEN TransactionType = 'Payment - Undeposited Funds' THEN
           Amount * -1
      CASE WHEN TransactionType = 'Invoice' THEN Amount
        END
        ) AS TOTAL
FROM ledger

GROUP BY roid
)
0
 

Author Closing Comment

by:ckelsoe
ID: 39686997
Thanks for the ideas and examples. I have (hopefully) learned some new things about this level of query syntax. This has gotten me close to what I need. I need to rethink the logic based on new information I now have.

In this project an account can have many repair orders (RO). Initially I was told that they wanted to see the credit on the account overall. The code you helped with above does that just fine. Now they want to see the credits applied at each ro. So off we go again.

Anyhow thanks.
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question