473,325 Members | 2,308 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Closed Transaction

radcaesar
759 Expert 512MB
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,

Expand|Select|Wrap|Line Numbers
  1. SELECT c.name AS [Customer Name], nt.tran_id AS [Transaction ID], nt.bill_number AS [Bill Number], nt.amount AS [Gross Amount], sum
  2. (t.inst_amount) AS [Amount Received]
  3. FROM customer AS c, newtransaction AS nt, transactions AS t
  4. 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 
  5. 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
  6. BY t.transaction_id)
  7. 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.......
Sep 21 '06 #1
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!

:)
Sep 21 '06 #2
radcaesar
759 Expert 512MB
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.
Sep 21 '06 #3
radcaesar
759 Expert 512MB
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 ?
Sep 21 '06 #4
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!

:)
Sep 21 '06 #5
radcaesar
759 Expert 512MB
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 ?
Sep 21 '06 #6
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!

:)
Sep 21 '06 #7
radcaesar
759 Expert 512MB
Hi Friend,
Can u please send me the whole query ? I Will try with that ? Its some what difficult to digest.

:)
Sep 21 '06 #8
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT c.name AS [Customer Name], nt.tran_id AS [Transaction ID], nt.bill_number AS [Bill Number], nt.amount AS [Gross Amount], sum
  2. (t.inst_amount) AS [Amount Received]
  3. FROM customer AS c, newtransaction AS nt, transactions AS t
  4. 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 
  5. 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
  6. BY t.transaction_id)) Between 1 AND 100
  7. GROUP BY c.name, nt.bill_number, nt.tran_id, nt.amount; I had a problem when there will be two equal gross amount. 
  8.  
  9.  

:)
Sep 22 '06 #9
radcaesar
759 Expert 512MB
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"
Sep 22 '06 #10
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...

:(
Sep 22 '06 #11
radcaesar
759 Expert 512MB
Customer Table

Expand|Select|Wrap|Line Numbers
  1. ID  Name             Address                   City       Phone
  2. 1   Vijay Stores    6,Gandhi Road    Pondy   0413-276564
  3. 2   Ram Stores    3, MG Road,       Pondicherry 0413-29543756
  4. 3   Balu Papers    3, RG St,              Neyveli    +919366228639
  5. 4   Senthil Papers      3, JN Road          Cuddalore 04142-287654
  6.  
NewTransaction Table
Expand|Select|Wrap|Line Numbers
  1. id   Tran_ID   Bill_Number   Amount   Bill_Date
  2. 7    9            1111             3000        28-Aug-06
  3. 4    10           2345            3000        04-Sep-06
  4.  
Transactions Table
Expand|Select|Wrap|Line Numbers
  1. Customer_ID   Transaction_ID   Inst_Amount   Date_of_transaction
  2. 7                    9                       1500              01-Sep-06
  3. 7                    9                       1500              12-Sep-06
  4. 4                   10                      1700              12-Sep-06
  5.  
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,

Expand|Select|Wrap|Line Numbers
  1. CustomerName  Transaction ID  Bill Number  Gross Amount  Amount Received
  2. Senthil Papers   10                    2345           3000                1700
  3. xxxx           9                     1111           3000                3000
  4.  
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.......
Sep 22 '06 #12
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?

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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]
  3. 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)
  4. 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)))
  5. GROUP BY c.Name, nt.Tran_ID, nt.Bill_Number, nt.Amount;
  6.  
  7.  
:)
Sep 23 '06 #13
PEB
1,418 Expert 1GB
Also try this one:

Expand|Select|Wrap|Line Numbers
  1. 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]
  2. 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)
  3. 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)))
  4. GROUP BY c.Name, nt.Tran_ID, nt.Bill_Number, nt.Amount;
  5.  
:)
Sep 23 '06 #14
radcaesar
759 Expert 512MB
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.

:)
Sep 25 '06 #15
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!
Sep 25 '06 #16

Sign in to post your reply or Sign up for a free account.

Similar topics

1
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...
1
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? ...
15
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...
1
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...
0
radcaesar
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...
3
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...
1
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...
3
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()...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.