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?
 
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
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
 
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
 
Phil ChapmanAuthor Commented:
Ok, Thanks for the help
0
 
Pawan KumarDatabase ExpertCommented:
welcome. glad to help as always.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.