MSSQL Query

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
LVL 2
Phil ChapmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Pawan KumarDatabase ExpertCommented:
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

0
Phil ChapmanAuthor Commented:
I don't get any errors but the query should return 2 Transactions_ID and it's not returning anything.
0
Pawan KumarDatabase ExpertCommented:
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

0
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Phil ChapmanAuthor Commented:
There is 1 transaction for 1,390.60 and 1 for 998.00
0
Pawan KumarDatabase ExpertCommented:
HAVING SUM(Amount) = 2388.60

so because of above it did not return anything.

is this amount - 2388.60 for single transaction ?
0
Phil ChapmanAuthor 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?
0
Pawan KumarDatabase ExpertCommented:
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
0
Phil ChapmanAuthor Commented:
No there is more than 1 transaction that equals 2388.60
0
Pawan KumarDatabase ExpertCommented:
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
0
Pawan KumarDatabase ExpertCommented:
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

0
Phil ChapmanAuthor 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
0
Phil ChapmanAuthor 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
0
Pawan KumarDatabase ExpertCommented:
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
0
Phil ChapmanAuthor Commented:
The amount we are looking for is 2388.60  I've tried 2388.60  and 2388.6
0
Pawan KumarDatabase ExpertCommented:
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

0
Phil ChapmanAuthor 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
0
Pawan KumarDatabase ExpertCommented:
Please run below -

SELECT * FROM AccountsTransactions

and copy all the data with headers into excel and attach. I will check for you.
0
Phil ChapmanAuthor 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
0
Pawan KumarDatabase ExpertCommented:
Please run below -

SELECT * FROM AccountsTransactions

and copy all the data with headers into excel and attach. I will check for you.
0
Phil ChapmanAuthor Commented:
See image file attached
Query-2017-11-24_11-24-58.jpg
0
Pawan KumarDatabase ExpertCommented:
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
0
Phil ChapmanAuthor 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.
0
Phil ChapmanAuthor Commented:
There could be 30 or 40 transactions and it might take 6 or 7 to make up the amount requested.
0
Pawan KumarDatabase ExpertCommented:
This is not possible as there is no logic for this. We can have 1 transaction up to any number of transaction to sum ...to any combination..this is a never ending process. Even manually it is not possible.
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
Phil ChapmanAuthor Commented:
Ok, Thanks for the help
0
Pawan KumarDatabase ExpertCommented:
welcome. glad to help as always.
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
Query Syntax

From novice to tech pro — start learning today.