Query Assistance

Hi all,
I have three tables:
Table 1:
Amount
InvoiceNumber
Table 2:
TransactionID
InvoiceNumber
Table 3:
Amount
TransactionID
InvoiceNumber
Client
I need to compare the amounts from table 1 and table 3. However I need to
do this by client. This is where the problem arises. Table 1 does not
contain clients, and the invoicenumber amount in Table 1 is a sum of all
transactions that have that InvoiceNumber.
Thus the Invoice Number '500055' could have mulitple clients amounts
included in it in Table 1.
So my thoughts are connect Table 1 to Table 2, this will allow me to get the
individual Transactions (read clients) that are associated with an Invoice
Number. But... If an Invoice number from Table 1 has two TransactionID's in
table 2 I will obviously get a double up... As the amount is coming from
Table 1.
So... My question is this.
How do this comparison? I have no idea and have been headbutting the wall
for a couple of days now.
Thanks!
ClintOn Thu, 10 Nov 2005 09:22:55 +1100, Clint wrote:

>Hi all,
>I have three tables:
>Table 1:
>Amount
>InvoiceNumber
>Table 2:
>TransactionID
>InvoiceNumber
>Table 3:
>Amount
>TransactionID
>InvoiceNumber
>Client
>I need to compare the amounts from table 1 and table 3. However I need to
>do this by client. This is where the problem arises. Table 1 does not
>contain clients, and the invoicenumber amount in Table 1 is a sum of all
>transactions that have that InvoiceNumber.
>Thus the Invoice Number '500055' could have mulitple clients amounts
>included in it in Table 1.
>So my thoughts are connect Table 1 to Table 2, this will allow me to get th
e
>individual Transactions (read clients) that are associated with an Invoice
>Number. But... If an Invoice number from Table 1 has two TransactionID's i
n
>table 2 I will obviously get a double up... As the amount is coming from
>Table 1.
>So... My question is this.
>How do this comparison? I have no idea and have been headbutting the wall
>for a couple of days now.
Hi Clint,
I'm sorry, but I don't really undersatnd what you want. Could you
provide some more information? The info I'd need is:
* CREATE TABLE statements. The description above is too scarce; I want
to know datatypes, keys, and other constraints as well. Complete CREATE
TABLE statements (including all constraints and properties) will give me
the info I need.
* Some sample data to illustrate the problem. Pleasae provide this data
as INSERT statements that I can run (after running the CREATE TABLE
statements) in my test database.
* The expected output, plus an explanation why that is the output you
expect.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||--To get the amounts by Client in table 1:
Select
sum(t1.Amount),
t3.client
from table1 t1
join table3 t3 on t1.invoicenumber = t3.invoicenumber
group by t1.client
--To get the amounts by Client in table 3
Select
sum(t3.Amount),
t3.client
from table1 t1
group by t3.client
--To compare them in one result set:
Select
isnull(t1.client, t3.client) client,
t1.amount as table1Amount,
t3.amount as table3Amount
From
(
Select
sum(t1.Amount) amount,
t3.client
from table1 t1
join table3 t3 on t1.invoicenumber = t3.invoicenumber
group by t1.client
) t1
full outer join
(
Select
sum(t3.Amount) amount,
t3.client
from table1 t1
group by t3.client
) t3 on t1.client = t3.client|||Thanks for your offer, hope this info will assist.
Tables:
CREATE TABLE [dbo].[Table1] (
[InvoiceNum][varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[AMOUNT] [numeric](28, 12) NOT NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[InvoiceNum] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[TRANSID] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table3] (
[client] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,
[TRANSID] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[InvoiceNum] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[AMOUNT] [numeric](28, 12) NOT NULL
) ON [PRIMARY]
Table 1 Data:
INSERT INTO Table1(InvoiceNum, Amount)
VALUES ('Invoice1', '0.3')
go
INSERT INTO Table1(InvoiceNum, Amount)
VALUES ('Invoice1', '0.5')
go
INSERT INTO Table1(InvoiceNum, Amount)
VALUES ('Invoice1', '0.8')
go
INSERT INTO Table1(InvoiceNum, Amount)
VALUES ('Invoice1', '1')
go
INSERT INTO Table1(InvoiceNum, Amount)
VALUES ('Invoice1', '1.5')
go
INSERT INTO Table1(InvoiceNum, Amount)
VALUES ('Invoice1', '7')
Table 2 Data
INSERT INTO Table2(InvoiceNum, TransID)
VALUES ('Invoice1', '1124')
go
INSERT INTO Table2(InvoiceNum, TransID)
VALUES ('Invoice1', '1124')
go
INSERT INTO Table2(InvoiceNum, TransID)
VALUES ('Invoice1', '1111')
go
INSERT INTO Table2(InvoiceNum, TransID)
VALUES ('Invoice1', '1234')
go
INSERT INTO Table2(InvoiceNum, TransID)
VALUES ('Invoice1', '567')
go
INSERT INTO Table2(InvoiceNum, TransID)
VALUES ('Invoice1', '8')
Data Table 3
INSERT INTO table3(InvoiceNum, TransID, Client, Amount)
VALUES ('Invoice1', '1124', 'Bob','2')
go
INSERT INTO table3(InvoiceNum, TransID, Client, Amount)
VALUES ('Invoice1', '1124', 'Jane','.22')
go
INSERT INTO table3(InvoiceNum, TransID, Client, Amount)
VALUES ('Invoice1', '1111', 'Mary','65')
go
INSERT INTO table3(InvoiceNum, TransID, Client, Amount)
VALUES ('Invoice1', '1234', 'Tom','.56')
go
INSERT INTO table3(InvoiceNum, TransID, Client, Amount)
VALUES ('Invoice1', '567', 'Brady','5')
go
INSERT INTO table3(InvoiceNum, TransID, Client, Amount)
VALUES ('Invoice1', '8','Jake','10')
My wanted results are:
For Client = Bob
Invoice, table1amount, table3amount
'Invoice 1','3','7'
This will allow me to start the process into why data is entering one table
but not the other. Essentially my problem is caused by the fact that I dont
have clients in table 1.
Hope this helps.
Clint
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:2lu4n1hkqjm2sj4fv8c8itea7g3ek8oijk@.
4ax.com...
> On Thu, 10 Nov 2005 09:22:55 +1100, Clint wrote:
>
> Hi Clint,
> I'm sorry, but I don't really undersatnd what you want. Could you
> provide some more information? The info I'd need is:
> * CREATE TABLE statements. The description above is too scarce; I want
> to know datatypes, keys, and other constraints as well. Complete CREATE
> TABLE statements (including all constraints and properties) will give me
> the info I need.
> * Some sample data to illustrate the problem. Pleasae provide this data
> as INSERT statements that I can run (after running the CREATE TABLE
> statements) in my test database.
> * The expected output, plus an explanation why that is the output you
> expect.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 10 Nov 2005 11:10:34 +1100, Clint wrote:

>Thanks for your offer, hope this info will assist.
>
(snip CREATE TABLE and INSERT statements)

>My wanted results are:
>For Client = Bob
>Invoice, table1amount, table3amount
>'Invoice 1','3','7'
>This will allow me to start the process into why data is entering one table
>but not the other. Essentially my problem is caused by the fact that I don
t
>have clients in table 1.
>Hope this helps.
Hi Clint,
Thanks for posting the CREATE TABLE and INSERT statements. I was able to
run them smoothly in my test database.
Unfortunately, this didn't help me to understand the logic of your
problem. I've bene looking at your data for some time now, and I've
reread your messages - but I fail to see how the data provided would
lead to the amounts '3' and '7' for Client = Bob.
Maybe you can explain me, step by step, how you get to that data? I can
then attempt to translate that in a corresponding SQL statement.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Thanks!
I must admit my desired result was made up... Using the data that I gave
you.. Client = BOB will have a value of 2.
What I am trying to do is compare table 1's invoices and amounts with the
corresponding values in table 3. However as Table 1 does not have client I
am finding it very difficult to do this.
Quite simply table 1 an invoice number in table 1 could be listed 10 times
with 5 different clients. However seeing how the invoice number is the same
I cannot determine what values are for client bob and what are for client
jane. Obviously I can do this in Table number 3 as the client is listed in
this table.
I apologise for my incorrect results concept. I would using the data I have
provided expect that Bob would have a value of 2 in table 3... but how would
I link this to table 1?
Clint
I really appreciate the assistance Hugo. :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:h8g7n1pqpd1er96d7h0ssdqg20ass458is@.
4ax.com...
> On Thu, 10 Nov 2005 11:10:34 +1100, Clint wrote:
>
> (snip CREATE TABLE and INSERT statements)
>
> Hi Clint,
> Thanks for posting the CREATE TABLE and INSERT statements. I was able to
> run them smoothly in my test database.
> Unfortunately, this didn't help me to understand the logic of your
> problem. I've bene looking at your data for some time now, and I've
> reread your messages - but I fail to see how the data provided would
> lead to the amounts '3' and '7' for Client = Bob.
> Maybe you can explain me, step by step, how you get to that data? I can
> then attempt to translate that in a corresponding SQL statement.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 11 Nov 2005 10:23:12 +1100, Clint wrote:

>Hugo,
>Thanks!
>I must admit my desired result was made up... Using the data that I gave
>you.. Client = BOB will have a value of 2.
Hi Clint,
Okay, that clarifies half the problem. The data you originally listed
was '3' for table1 value, and '7' for table3 value. The latter is now
corrected to '2' - which indeed makes a lot more sense.
But how about the other value? The value '3' for table1? I don't see any
row in table1 that holds this value, nor any logical combination of rows
that would help me to arrive at this value.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Ah... yes... that is a sum of three records... 1.5+1+0.5 = 3
So now you can see that one record in table three might be three or more
records in table one.
Thus I need to know how to connect these two tables :)
I am very .
THANKS!
Clint
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rjn7n19a4vepqnheo3q5rur22b5v760va3@.
4ax.com...
> On Fri, 11 Nov 2005 10:23:12 +1100, Clint wrote:
>
> Hi Clint,
> Okay, that clarifies half the problem. The data you originally listed
> was '3' for table1 value, and '7' for table3 value. The latter is now
> corrected to '2' - which indeed makes a lot more sense.
> But how about the other value? The value '3' for table1? I don't see any
> row in table1 that holds this value, nor any logical combination of rows
> that would help me to arrive at this value.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 11 Nov 2005 12:20:06 +1100, Clint wrote:

>Ah... yes... that is a sum of three records... 1.5+1+0.5 = 3
Hi Clint,
Okay - one step closer to the target. But not quite there yet.
The following six rows are in Table1:
InvoiceNum | Amount
--+--
Invoice1 | 0.3
Invoice1 | 0.5
Invoice1 | 0.8
Invoice1 | 1
Invoice1 | 1.5
Invoice1 | 7
The three rows you want to sum are present - but so are three other
rows, that you (apparently) don't want to sum. Why? How can you tell, bu
looking at the data in this table or in any of the other tables, that
three out of the six rows should be summed? And that it should be
*THESE* three rows, not the rows with value 0.3, 0.8, and 7 ?
This appears to be a specification problem, rather than a query-wriiting
query!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Exactly. How can I tell. This is the way our ERP system is. This is what
I am after. How do I tell?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:n61an1t46pcj78goomqocjuu5as5d954q4@.
4ax.com...
> On Fri, 11 Nov 2005 12:20:06 +1100, Clint wrote:
>
> Hi Clint,
> Okay - one step closer to the target. But not quite there yet.
> The following six rows are in Table1:
> InvoiceNum | Amount
> --+--
> Invoice1 | 0.3
> Invoice1 | 0.5
> Invoice1 | 0.8
> Invoice1 | 1
> Invoice1 | 1.5
> Invoice1 | 7
> The three rows you want to sum are present - but so are three other
> rows, that you (apparently) don't want to sum. Why? How can you tell, bu
> looking at the data in this table or in any of the other tables, that
> three out of the six rows should be summed? And that it should be
> *THESE* three rows, not the rows with value 0.3, 0.8, and 7 ?
> This appears to be a specification problem, rather than a query-wriiting
> query!
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)