Solved

Union query help

Posted on 2013-11-29
8
356 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
 

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now