Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

QUEYR USING TABLE DATA AS FILTER

Hi All,

I have below tables :

1. TMLEDGER
    AccNo
    Amount

2. TMFORM
    FormNo
    FormAccNo
    FromAccNo
    ToAccNo

3. TDFORMDATA
    FormNo
    FormAccNo
    FormAmount

I want to query using table 1 and 2 to get result into table 3.

How could I do it ?

Thank you.
0
emi_sastra
Asked:
emi_sastra
  • 6
  • 4
  • 3
1 Solution
 
emi_sastraAuthor Commented:
More info.

TMFORM as the table for filtering data from TMLEDGER.

Thank you.

0
 
LowfatspreadCommented:
what is the relation of the account numbers on the form table?

FromAccNo
    ToAccNo


how do they relate to

accno on tmledger ?
0
 
LowfatspreadCommented:
is this what you want ?

insert into tdformdata
 (formno,formaccno,formamount)
select formno,formaccno,sum(amount)
from tmledger as l
inner join tmform as f
on l.accno between f.fromaccno and f.toaccno
group by formno,formaccno

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Saurabh BhadauriaCommented:
please specify the relationship between tables ....
0
 
LowfatspreadCommented:
or perhaps this if you always want a row per form account ?

insert into tdformdata
 (formno,formaccno,formamount)
select formno,formaccno,coalesce(sum(amount),0)

from tmform as f
left outer join tmledger as l
on l.accno between f.fromaccno and f.toaccno
group by formno,formaccno

Open in new window

0
 
emi_sastraAuthor Commented:
Hi All,

I am sorry just back.

Thank you.
0
 
emi_sastraAuthor Commented:
Hi All,


FromAccNo
    ToAccNo is related to TMLEDGER AccNo.

Thank you.
0
 
Saurabh BhadauriaCommented:
OK  Emi that's fine..
one more thing how do you want to calculate the amount in 3rd table ?
May be you want to subtract the FromAccNo amount to ToAccNo amount or addition , or may you want to directly insert or update the first table  amt to third table amt based upon FromAccNo or ToAccNo...?
0
 
emi_sastraAuthor Commented:
I want to sum (addition) to the result table base on formaccno.

Thank you.
0
 
Saurabh BhadauriaCommented:

<< I want to sum (addition) to the result table base on formaccno. >>

ok here is your code..

insert into tdformdata
 (FormNo,FormAccNo,FormAmount)
select tm.FormNo,tm.FormAccNo,sum(tl.Amount)
from tmledger as tl
inner join tmform as tf
on tl.AccNo=tf.FormAccNo 
group by tf.FormNo,tf.FormAccNo

Open in new window

0
 
emi_sastraAuthor Commented:
For example :

FormAccNo      FromAccNo   ToAccNo
100                 100.001       100.010
100                 100.020       100.090
200                 200.001       300.001



Thus Result is sum from AccNo 100 = 100.001  to  100.010 + 100.020  To  100.090

Thank you.
0
 
LowfatspreadCommented:
then my answer 36480926 should do that...

unless there is a problem with the datatype you have for account...

what data types are you using for the account number columns?
0
 
emi_sastraAuthor Commented:
Yes, I've just tested it.

Thank you very much for your help.
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now