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,1063
SUM(Amount) = 2388.60
PLEASE HELP I NEED TO GET THE Transaction_ID of those transaction where the sum = 2388.60
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,1063
SUM(Amount) = 2388.60
PLEASE HELP I NEED TO GET THE Transaction_ID of those transaction where the sum = 2388.60
ASKER
I don't get any errors but the query should return 2 Transactions_ID and it's not returning anything.
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 ASKER
There is 1 transaction for 1,390.60 and 1 for 998.00
HAVING SUM(Amount) = 2388.60
so because of above it did not return anything.
is this amount - 2388.60 for single transaction ?
so because of above it did not return anything.
is this amount - 2388.60 for single transaction ?
ASKER
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
Where do you enter the amount you are looking for?
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
Where do you enter the amount you are looking for?
We can do one of these below
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
HAVING SUM(Amount) = someValue
OR
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
HAVING SUM(Amount) = someValue
OR
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
ASKER
No there is more than 1 transaction that equals 2388.60
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,1063
GROUP BY Transaction_ID
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
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..
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) < 2389ASKER
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
HAVING SUM(Amount) = 2388.6
RTURNS NO RECORDS
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
HAVING SUM(Amount) = 2388.6
RTURNS NO RECORDS
ASKER
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
RETURNS NO RECORDS
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
RETURNS NO RECORDS
Can you please give me output of below ??
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
ASKER
The amount we are looking for is 2388.60 I've tried 2388.60 and 2388.6
Data Please ??
Hv u tried below -
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) < 2389ASKER
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,1063
GROUP BY Transaction_ID
The results is the Headers
Transaction_ID and amt
but no data
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
The results is the Headers
Transaction_ID and amt
but no data
Please run below -
SELECT * FROM AccountsTransactions
and copy all the data with headers into excel and attach. I will check for you.
SELECT * FROM AccountsTransactions
and copy all the data with headers into excel and attach. I will check for you.
ASKER
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
HAVING SUM(Amount) >= 2388 AND SUM(Amount) < 2389
SAME THING HEADERS ONLY
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
HAVING SUM(Amount) >= 2388 AND SUM(Amount) < 2389
SAME THING HEADERS ONLY
Please run below -
SELECT * FROM AccountsTransactions
and copy all the data with headers into excel and attach. I will check for you.
SELECT * FROM AccountsTransactions
and copy all the data with headers into excel and attach. I will check for you.
ASKER
See image file attached
Query-2017-11-24_11-24-58.jpg
Query-2017-11-24_11-24-58.jpg
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,1063
GROUP BY Transaction_ID
HAVING SUM(Amount) = 1390.60
Below will work without any issues.
SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,1063
GROUP BY Transaction_ID
HAVING SUM(Amount) = 1390.60
ASKER
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.
When the query is run I will not know what the lowest or highest amount is.
ASKER
There could be 30 or 40 transactions and it might take 6 or 7 to make up the amount requested.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, Thanks for the help
welcome. glad to help as always.
Open in new window