Hi All,
I Have Three Tables,
Customer
NewTransaction
Transactions
The Customer Table Have details about customer and the Transactions table had details of the transactions with gross amount and the
Transactions table had the Installment amounts for a particular transaction.
Now, i want the closed transactions i.e NewTransaction.GrossAmount=sum(Transactions.Inst_A mount)
My Query, - SELECT c.name AS [Customer Name], nt.tran_id AS [Transaction ID], nt.bill_number AS [Bill Number], nt.amount AS [Gross Amount], sum
-
(t.inst_amount) AS [Amount Received]
-
FROM customer AS c, newtransaction AS nt, transactions AS t
-
WHERE c.id=nt.id and c.id=t.customer_ID and nt.tran_id=t.transaction_id and nt.amount IN (SELECT sum(t.inst_amount) FROM
-
transactions AS t, newtransaction AS nt, customer AS c WHERE c.id=nt.id and c.id=t.customer_id and nt.tran_id=t.transaction_id GROUP
-
BY t.transaction_id)
-
GROUP BY c.name, nt.bill_number, nt.tran_id, nt.amount; I had a problem when there will be two equal gross amount.
Another record which was not closed also displayed, which has the same gross amount.
Solutions Pls.......
15 2407 PEB 1,418
Expert 1GB
Hi
Try
Abs(nt.amount - (SELECT sum(t.inst_amount) FROM
transactions AS t, newtransaction AS nt, customer AS c WHERE c.id=nt.id and c.id=t.customer_id and nt.tran_id=t.transaction_id GROUP
BY t.transaction_id))
And then sort the obtained recordset by this column...
The first row will be closer! Get it!
:)
Hi
Try
Abs(nt.amount - (SELECT sum(t.inst_amount) FROM
transactions AS t, newtransaction AS nt, customer AS c WHERE c.id=nt.id and c.id=t.customer_id and nt.tran_id=t.transaction_id GROUP
BY t.transaction_id))
And then sort the obtained recordset by this column...
The first row will be closer! Get it!
:)
Hi PEB,
Sorry yar, I Didn't get you clear. Your's will return Zero if its closed. I Want list of the closed transactions only.
Hi
Try
Abs(nt.amount - (SELECT sum(t.inst_amount) FROM
transactions AS t, newtransaction AS nt, customer AS c WHERE c.id=nt.id and c.id=t.customer_id and nt.tran_id=t.transaction_id GROUP
BY t.transaction_id))
And then sort the obtained recordset by this column...
The first row will be closer! Get it!
:)
Hi PEB,
Can i know what is the problem with my above query ?
PEB 1,418
Expert 1GB
Bé under closer I understand...
If one of the values = 1
and the compared values are 1, 2, 3, 15 , 25, 30
The range of the closer numbers have to be 2, 3, 15, 25, 30
isn't it?
And in your query it seems that search the eqivalance between the amount and any of your sums!
Take care my frien!
:)
Bé under closer I understand...
If one of the values = 1
and the compared values are 1, 2, 3, 15 , 25, 30
The range of the closer numbers have to be 2, 3, 15, 25, 30
isn't it?
And in your query it seems that search the eqivalance between the amount and any of your sums!
Take care my frien!
:)
This one is good PEB, but why my query with the IN Key word fails there. It returns wrong values i.e returns the records with Same GROSS Amount, I mean if i have two transactions of 3000 (One is Open in that), It Returns both. Whats the problem with that query. And also what is the need of ABS Keyword here ?
PEB 1,418
Expert 1GB
In fact the Abs() function returns the absolute value of a number!
When You do a difference between 5-6 it gives 1 and 6-5 gives also 1
This function helps to detect the closer values from positive and negative part of given number!
:)
And for the other I'm affraid it's difficult!
:)
Hi Friend,
Can u please send me the whole query ? I Will try with that ? Its some what difficult to digest.
:)
PEB 1,418
Expert 1GB
Hi,
In fact you need to join the 2 tables: This with the different sums and the other in which you need the closer values...
With subqueries it wonnat go... Because there are multiple values in the subquery!
However If in your subquery has only one value that is returned then your SQL should be: -
SELECT c.name AS [Customer Name], nt.tran_id AS [Transaction ID], nt.bill_number AS [Bill Number], nt.amount AS [Gross Amount], sum
-
(t.inst_amount) AS [Amount Received]
-
FROM customer AS c, newtransaction AS nt, transactions AS t
-
WHERE c.id=nt.id and c.id=t.customer_ID and nt.tran_id=t.transaction_id and abs(nt.amount - (SELECT sum(t.inst_amount) FROM
-
transactions AS t, newtransaction AS nt, customer AS c WHERE c.id=nt.id and c.id=t.customer_id and nt.tran_id=t.transaction_id GROUP
-
BY t.transaction_id)) Between 1 AND 100
-
GROUP BY c.name, nt.bill_number, nt.tran_id, nt.amount; I had a problem when there will be two equal gross amount.
-
-
:)
Hi PEB,
The same resule i got here after abs (). Why did u go for BETWEEN 1 AND 100. Is there any need of that check ?
Here is the query i tried,
SELECT c.name AS [Customer Name], nt.tran_id AS [Transaction ID], nt.bill_number AS [Bill Number], nt.amount AS [Gross Amount], sum(t.inst_amount) AS [Amount Received]
FROM customer AS c, newtransaction AS nt, transactions AS t
WHERE c.id=nt.id and c.id=t.customer_ID and nt.tran_id=t.transaction_id and abs(nt.amount) IN (SELECT abs(sum(t.inst_amount)) FROM transactions AS t, newtransaction AS nt, customer AS c WHERE c.id=nt.id and c.id=t.customer_id and nt.tran_id=t.transaction_id GROUP BY t.transaction_id)
GROUP BY c.name, nt.bill_number, nt.tran_id, nt.amount;
The query u had sent never execute, since it returns more than one value when compare. Since you used BETWEEN, i won't get why this error came "At most one record can be returned by this sub query"
PEB 1,418
Expert 1GB
Sorry, it seems that I don't understand you very well with my foulish English...
Can you explain me using some data from your tables what is closer transaction and what transaction appears as well!
Sorry to boder you but obviously I don't understand you very well, maybe If I understand better with exemple...
:(
Customer Table -
ID Name Address City Phone
-
1 Vijay Stores 6,Gandhi Road Pondy 0413-276564
-
2 Ram Stores 3, MG Road, Pondicherry 0413-29543756
-
3 Balu Papers 3, RG St, Neyveli +919366228639
-
4 Senthil Papers 3, JN Road Cuddalore 04142-287654
-
NewTransaction Table -
id Tran_ID Bill_Number Amount Bill_Date
-
7 9 1111 3000 28-Aug-06
-
4 10 2345 3000 04-Sep-06
-
Transactions Table -
Customer_ID Transaction_ID Inst_Amount Date_of_transaction
-
7 9 1500 01-Sep-06
-
7 9 1500 12-Sep-06
-
4 10 1700 12-Sep-06
-
The Customer Table Have details about customer and the Transactions table had details of the transactions with gross amount and the Transactions table had the Installment amounts for a particular transaction.
Now, i want the closed transactions i.e NewTransaction.GrossAmount=sum(Transactions.Inst_A mount)
My Query,
SELECT c.name AS [Customer Name], nt.tran_id AS [Transaction ID], nt.bill_number AS [Bill Number], nt.amount AS [Gross Amount], sum(t.inst_amount) AS [Amount Received]
FROM customer AS c, newtransaction AS nt, transactions AS t
WHERE c.id=nt.id and c.id=t.customer_ID and nt.tran_id=t.transaction_id and nt.amount IN (SELECT sum(t.inst_amount) FROM transactions AS t, newtransaction AS nt, customer AS c WHERE c.id=nt.id and c.id=t.customer_id and nt.tran_id=t.transaction_id GROUP BY t.transaction_id)
GROUP BY c.name, nt.bill_number, nt.tran_id, nt.amount;
Shows the result belw, -
CustomerName Transaction ID Bill Number Gross Amount Amount Received
-
Senthil Papers 10 2345 3000 1700
-
xxxx 9 1111 3000 3000
-
The Second one is correct and the first one is wrong. I had a problem when there will be two equal gross amount. According to my current records, it has to show only the first record.
Solutions Pls.......
PEB 1,418
Expert 1GB
Hi,
Now it's more clear, I've changed some things in your query
Can you tell me if it works better? -
-
SELECT c.Name AS [Customer Name], nt.Tran_ID AS [Transaction ID], nt.Bill_Number AS [Bill Number], nt.Amount AS [Gross Amount], Sum(t.Inst_Amount) AS [Amount Received]
-
FROM (customer AS c INNER JOIN newtransaction AS nt ON c.ID = nt.id) INNER JOIN transactions AS t ON (nt.id = t.Customer_ID) AND (nt.Tran_ID = t.Transaction_ID)
-
WHERE (((nt.Amount) In (SELECT sum(t.inst_amount) FROM (customer AS c INNER JOIN newtransaction AS nt ON c.ID = nt.id) INNER JOIN transactions AS t ON (nt.Tran_ID = t.Transaction_ID) AND (nt.id = t.Customer_ID) GROUP BY t.transaction_id)))
-
GROUP BY c.Name, nt.Tran_ID, nt.Bill_Number, nt.Amount;
-
-
:)
PEB 1,418
Expert 1GB
Also try this one: -
SELECT c.Name AS [Customer Name], nt.Tran_ID AS [Transaction ID], nt.Bill_Number AS [Bill Number], nt.Amount AS [Gross Amount], Sum(t.Inst_Amount) AS [Amount Received]
-
FROM (customer AS c INNER JOIN newtransaction AS nt ON c.ID = nt.id) INNER JOIN transactions AS t ON (nt.id = t.Customer_ID) AND (nt.Tran_ID = t.Transaction_ID)
-
WHERE (((nt.Amount) In (SELECT sum(transactions.inst_amount) FROM (customer INNER JOIN newtransaction ON customer.ID = newtransaction.id) INNER JOIN transactions ON (newtransaction.Tran_ID = transactions.Transaction_ID) AND (newtransaction.id = transactions.Customer_ID) GROUP BY transactions.transaction_id HAVING transactions.transaction_id=t.Transaction_ID)))
-
GROUP BY c.Name, nt.Tran_ID, nt.Bill_Number, nt.Amount;
-
:)
Hi Friend,
Excellent, the later one works. Can i know whats problem with my query with out the INNER JOIN key word. Actually i wrote it in brief by checking with the IN Key word.
:)
PEB 1,418
Expert 1GB
If the last one works, so i've introduced in your subquery a complementary condition and i think this is the reason to work!
Your previous query with this condition implemented mayb will work also good!
:)
Best regards!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Rittercorp |
last post by:
I am debugging an app which blocks many processes in a SQL7 server DB.
The app log writes every transaction "open" and "close".
The weird thing is : when the app logfile says the transaction is...
|
by: Avanish Pandey |
last post by:
Hello All
We have 3 differen services (in 3 different server) Service A,B,C . We
want to implement distributed transaction when call methods of B and C
from A. Is it possible? if yes then how?
...
|
by: Rob Nicholson |
last post by:
I'm starting to worry a bit now. We're getting the above error when two
users hit the same database/page on an ASP.NET application using ADO.NET,
talking to a SQL 7 server. The error is perfectly...
|
by: Janez Kostanj¹ek |
last post by:
Hello everyone,
this is my first post to postgres mailing list.
System that I use:
I am using Postgres under last CygWin published on public mirrors at the moment.
OS system runing is...
|
by: radcaesar |
last post by:
Customer Table
ID Name Address City Phone
1 Vijay Stores 6,Gandhi Road Pondy 0413-276564
2 Ram Stores 3, MG Road, Pondicherry 0413-29543756
3 Balu Papers 3, RG...
|
by: vijay.db |
last post by:
Hi Group,
Getting the below error in AIX server 5.2 and my DB2 ESE is V8.1 and
Fixpack 11.
I'm able to successfully connect to the database and able to run a
SELECT query with out any...
|
by: madhupk |
last post by:
this is regarding C trigger function in postgresql 8.2
The function trigf reports the number of rows in the table ttest and skips the actual operation if the command attempts to insert a null...
|
by: BLUE |
last post by:
I've a TransactionScope in which I select some data and I want to do some
queries for each record retrieved with my select.
I'm using a DataReader and for each record I do factory.CreateCommand()...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |