Solved

Union query help

Posted on 2013-11-29
8
354 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

22 Experts available now in Live!

Get 1:1 Help Now