We help IT Professionals succeed at work.

MSSQL Query

Phil Chapman
Phil Chapman asked
on
73 Views
Last Modified: 2017-11-24
Issue:
A person has several invoice that may have one or more transactions in a Transaction file.
What I need to do is get the transactions ID where some of the transactions equal a specified amount.

Sample Transaction Table Fields

Transaction_ID
TranDate
InvoiceNo
Amount

The query below would give me what I need IF IT WOULD ONLY WORK

SELECT Transaction_ID
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367) AND
SUM(Amount) = 2388.60

PLEASE HELP I NEED TO GET THE Transaction_ID of those transaction where the sum = 2388.60
Comment
Watch Question

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this like this ..Added GROUP BY and Having clause.

SELECT Transaction_ID
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367) 
GROUP BY Transaction_ID
HAVING SUM(Amount) = 2388.60

Open in new window

Author

Commented:
I don't get any errors but the query should return 2 Transactions_ID and it's not returning anything.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Can you please give me output of this -

SELECT Transaction_ID , SUM(Amount) amt 
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367) 
GROUP BY Transaction_ID 

Open in new window

Author

Commented:
There is 1 transaction for 1,390.60 and 1 for 998.00
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
HAVING SUM(Amount) = 2388.60

so because of above it did not return anything.

is this amount - 2388.60 for single transaction ?

Author

Commented:
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID

Where do you enter the amount you are looking for?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
We can do one of these below

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID
HAVING SUM(Amount) = someValue

OR

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)  AND Amount = someValue
GROUP BY Transaction_ID

Author

Commented:
No there is more than 1 transaction that equals 2388.60
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
can you please give me output of this - ( copy paste - do not change anything. )

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
I think there is some decimal issue is there.

Do we have any values from 2388.60.. like 2388.6000002.. ??

Please try like this also..

SELECT Transaction_ID , SUM(Amount) amt 
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367) 
GROUP BY Transaction_ID
HAVING SUM(Amount) >= 2388 AND SUM(Amount) < 2389

Open in new window

Author

Commented:
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID
HAVING SUM(Amount) = 2388.6

RTURNS NO RECORDS

Author

Commented:
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)  AND Amount = 2388.6
GROUP BY Transaction_ID

RETURNS NO RECORDS
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Can you please give me output of below ??

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID

Author

Commented:
The amount we are looking for is 2388.60  I've tried 2388.60  and 2388.6
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Data Please ??

Hv u tried below -

SELECT Transaction_ID , SUM(Amount) amt 
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367) 
GROUP BY Transaction_ID
HAVING SUM(Amount) >= 2388 AND SUM(Amount) < 2389

Open in new window

Author

Commented:
When I run the query below in Microsoft SQL Server Management Studio

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID


The results is the Headers
Transaction_ID and amt
but no data
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please run below -

SELECT * FROM AccountsTransactions

and copy all the data with headers into excel and attach. I will check for you.

Author

Commented:
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID
HAVING SUM(Amount) >= 2388 AND SUM(Amount) < 2389

SAME THING HEADERS ONLY
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please run below -

SELECT * FROM AccountsTransactions

and copy all the data with headers into excel and attach. I will check for you.

Author

Commented:
See image file attached
Query-2017-11-24_11-24-58.jpg
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
You are doing 1390.60 + 998 ?? This is incorrect as their Transaction_IDs are different --- > 198188 , 198189 so the sum will not happen.

Below will work without any issues.

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID
HAVING SUM(Amount) = 1390.60

Author

Commented:
The query will have to sum the amounts and give me the Transaction_ID of the ones that totals the requested amount.

When the query is run I will not know what the lowest or highest amount is.

Author

Commented:
There could be 30 or 40 transactions and it might take 6 or 7 to make up the amount requested.
Database Expert
Awarded 2016
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Ok, Thanks for the help
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
welcome. glad to help as always.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.