Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ?
Please could someone out there offer some ideas ?
THANKS
JONselect t1.[id] from [inv table] t1 inner join [cost table] t2 on t1.[id]=t2.[id] where t1.amount+t1.vat<>t2.amount+t2.vat|||Hi Rafala, thanks so much for the assistance,GREATLY APPRECIATED
I have a slight problem though...
Table COST can be made up of multiple entries/rows..ie, ID is not the primary key, so it is possible to have multiple rows, all with the same ID.
Table INV has single row entries for each ID(Primary Key)
Basically, INV table has 1 row, say ID 7
COST Table could have 4 rows, all ID 7. I need to add the AMOUNT and VAT columns for all 4 rows of Table COST and measure that up against the total (AMOUNT+VAT)for the single row of Table INV.
ie.
COST
id7 12 4
id7 21 7
id7 35 1
id7 10 87 ...TOTAL 177
INV
id7 78 99 ...TOTAL 177 ..In this case all is well
Should the TOTAL of ALL rows under cost.amount and cost.vat for cost.ID7 not equal TOTAL of inv.amount+inv.vat for inv.ID7, I would need it to bring up this problem ID...
Am I making much sense...
CHEERS|||Hi Rafala, thanks so much for the assistance,GREATLY APPRECIATED
I have a slight problem though...
Table COST can be made up of multiple entries/rows..ie, ID is not the primary key, so it is possible to have multiple rows, all with the same ID.
Table INV has single row entries for each ID(Primary Key)
Basically, INV table has 1 row, say ID 7
COST Table could have 4 rows, all ID 7. I need to add the AMOUNT and VAT columns for all 4 rows of Table COST and measure that up against the total (AMOUNT+VAT)for the single row of Table INV.
ie.
COST
id7 12 4
id7 21 7
id7 35 1
id7 10 87 ...TOTAL 177
INV
id7 78 99 ...TOTAL 177 ..In this case all is well
Should the TOTAL of ALL rows under cost.amount and cost.vat for cost.ID7 not equal TOTAL of inv.amount+inv.vat for inv.ID7, I would need it to bring up this problem ID...
Am I making much sense...
CHEERS|||select t1.[id] from [inv table] t1 inner join (select t2.[id], cost_total=sum(t2.amount+t2.vat) from [cost table] t2 where t1.[id]=t2.[id] group by t2.[id]) co where t1.[id] = co.[id] and (t1.amount+t1.vat)<>co.cost_total
Showing posts with label related. Show all posts
Showing posts with label related. Show all posts
Tuesday, March 27, 2012
Comparing result set values of 2 queries ?
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ?
Please could someone out there offer some ideas ?
THANKS
JONI'd use:SELECT *
FROM inv
JOIN cost
ON (cost.id = inv.id)
WHERE inv.amount <> cost.amount
OR inv.vat <> cost.vat-PatP
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ?
Please could someone out there offer some ideas ?
THANKS
JONI'd use:SELECT *
FROM inv
JOIN cost
ON (cost.id = inv.id)
WHERE inv.amount <> cost.amount
OR inv.vat <> cost.vat-PatP
Comparing result set values of 2 queries ?
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ?
Please could someone out there offer some ideas ?
THANKS
JONselect id, sum(amount) as inv_amount, sum(vat) as inv_vat,
cast(0, decimal(11,3)) as cost_amount, cast(0, decimal(11,3)) as cost_vat
into #inv
from INV
group by id
select id, sum(amount) as cost_amount, sum(vat) as cost_vat
into #cost
from cost
group by id
update x
set x.cost_amount = v.cost_amount,
x.cost_vat = v.cost_vat
from #inv x, #cost v
where x.id = v.id
select * from #inv|||How is this different from your first post (http://www.dbforums.com/t994762.html)?
-PatP|||???|||Originally posted by mkkmg
??? Click the link I posted. This isn't the first time they've posted that question.
-PatPsqlsql
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ?
Please could someone out there offer some ideas ?
THANKS
JONselect id, sum(amount) as inv_amount, sum(vat) as inv_vat,
cast(0, decimal(11,3)) as cost_amount, cast(0, decimal(11,3)) as cost_vat
into #inv
from INV
group by id
select id, sum(amount) as cost_amount, sum(vat) as cost_vat
into #cost
from cost
group by id
update x
set x.cost_amount = v.cost_amount,
x.cost_vat = v.cost_vat
from #inv x, #cost v
where x.id = v.id
select * from #inv|||How is this different from your first post (http://www.dbforums.com/t994762.html)?
-PatP|||???|||Originally posted by mkkmg
??? Click the link I posted. This isn't the first time they've posted that question.
-PatPsqlsql
Monday, March 19, 2012
compare table related objects
Hi,
I am migrating a database from one server SQL 6.5 to another SQL 2000. I
want to have script to compare table related objects (index/PK/FK/constraint)
for all the tables in the database to make sure nothing missing.
Anyone can help?
Julia wrote:
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL
> 2000. I want to have script to compare table related objects
> (index/PK/FK/constraint) for all the tables in the database to make
> sure nothing missing.
> Anyone can help?
I'm not familiar with SQL 6.5, but in SQL 2000 there are the
INFORMATION_SCHEMA views that can be used to obtain a list of tables,
constraints, etc. You could use this to check that everything's there.
John.
|||sql6.x does not have info_ views yet. You will have to look at sysobjects
for the list of objects. If you want to compare definition, you will have to
check out syscomments.
-oj
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL 2000. I
> want to have script to compare table related objects
> (index/PK/FK/constraint)
> for all the tables in the database to make sure nothing missing.
> Anyone can help?
>
>
|||I thought that might be the case.
In which case (assuming the migration is straight over and the objects will
either exist and be correct, or be missing), sysobjects with an appropriate
filter on the xtype column sounds ideal - BOL has full documentation on this
column (e.g. a value of U for user table, F for foreign key etc.)
John.
oj wrote:[vbcol=seagreen]
> sql6.x does not have info_ views yet. You will have to look at
> sysobjects for the list of objects. If you want to compare
> definition, you will have to check out syscomments.
>
> "Julia" <Julia@.discussions.microsoft.com> wrote in message
> news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
I am migrating a database from one server SQL 6.5 to another SQL 2000. I
want to have script to compare table related objects (index/PK/FK/constraint)
for all the tables in the database to make sure nothing missing.
Anyone can help?
Julia wrote:
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL
> 2000. I want to have script to compare table related objects
> (index/PK/FK/constraint) for all the tables in the database to make
> sure nothing missing.
> Anyone can help?
I'm not familiar with SQL 6.5, but in SQL 2000 there are the
INFORMATION_SCHEMA views that can be used to obtain a list of tables,
constraints, etc. You could use this to check that everything's there.
John.
|||sql6.x does not have info_ views yet. You will have to look at sysobjects
for the list of objects. If you want to compare definition, you will have to
check out syscomments.
-oj
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL 2000. I
> want to have script to compare table related objects
> (index/PK/FK/constraint)
> for all the tables in the database to make sure nothing missing.
> Anyone can help?
>
>
|||I thought that might be the case.
In which case (assuming the migration is straight over and the objects will
either exist and be correct, or be missing), sysobjects with an appropriate
filter on the xtype column sounds ideal - BOL has full documentation on this
column (e.g. a value of U for user table, F for foreign key etc.)
John.
oj wrote:[vbcol=seagreen]
> sql6.x does not have info_ views yet. You will have to look at
> sysobjects for the list of objects. If you want to compare
> definition, you will have to check out syscomments.
>
> "Julia" <Julia@.discussions.microsoft.com> wrote in message
> news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
compare table related objects
Hi,
I am migrating a database from one server SQL 6.5 to another SQL 2000. I
want to have script to compare table related objects (index/PK/FK/constraint
)
for all the tables in the database to make sure nothing missing.
Anyone can help?Julia wrote:
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL
> 2000. I want to have script to compare table related objects
> (index/PK/FK/constraint) for all the tables in the database to make
> sure nothing missing.
> Anyone can help?
I'm not familiar with SQL 6.5, but in SQL 2000 there are the
INFORMATION_SCHEMA views that can be used to obtain a list of tables,
constraints, etc. You could use this to check that everything's there.
John.|||sql6.x does not have info_ views yet. You will have to look at sysobjects
for the list of objects. If you want to compare definition, you will have to
check out syscomments.
-oj
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL 2000. I
> want to have script to compare table related objects
> (index/PK/FK/constraint)
> for all the tables in the database to make sure nothing missing.
> Anyone can help?
>
>|||I thought that might be the case.
In which case (assuming the migration is straight over and the objects will
either exist and be correct, or be missing), sysobjects with an appropriate
filter on the xtype column sounds ideal - BOL has full documentation on this
column (e.g. a value of U for user table, F for foreign key etc.)
John.
oj wrote:[vbcol=seagreen]
> sql6.x does not have info_ views yet. You will have to look at
> sysobjects for the list of objects. If you want to compare
> definition, you will have to check out syscomments.
>
> "Julia" <Julia@.discussions.microsoft.com> wrote in message
> news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
I am migrating a database from one server SQL 6.5 to another SQL 2000. I
want to have script to compare table related objects (index/PK/FK/constraint
)
for all the tables in the database to make sure nothing missing.
Anyone can help?Julia wrote:
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL
> 2000. I want to have script to compare table related objects
> (index/PK/FK/constraint) for all the tables in the database to make
> sure nothing missing.
> Anyone can help?
I'm not familiar with SQL 6.5, but in SQL 2000 there are the
INFORMATION_SCHEMA views that can be used to obtain a list of tables,
constraints, etc. You could use this to check that everything's there.
John.|||sql6.x does not have info_ views yet. You will have to look at sysobjects
for the list of objects. If you want to compare definition, you will have to
check out syscomments.
-oj
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL 2000. I
> want to have script to compare table related objects
> (index/PK/FK/constraint)
> for all the tables in the database to make sure nothing missing.
> Anyone can help?
>
>|||I thought that might be the case.
In which case (assuming the migration is straight over and the objects will
either exist and be correct, or be missing), sysobjects with an appropriate
filter on the xtype column sounds ideal - BOL has full documentation on this
column (e.g. a value of U for user table, F for foreign key etc.)
John.
oj wrote:[vbcol=seagreen]
> sql6.x does not have info_ views yet. You will have to look at
> sysobjects for the list of objects. If you want to compare
> definition, you will have to check out syscomments.
>
> "Julia" <Julia@.discussions.microsoft.com> wrote in message
> news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
compare table related objects
Hi,
I am migrating a database from one server SQL 6.5 to another SQL 2000. I
want to have script to compare table related objects (index/PK/FK/constraint)
for all the tables in the database to make sure nothing missing.
Anyone can help?Julia wrote:
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL
> 2000. I want to have script to compare table related objects
> (index/PK/FK/constraint) for all the tables in the database to make
> sure nothing missing.
> Anyone can help?
I'm not familiar with SQL 6.5, but in SQL 2000 there are the
INFORMATION_SCHEMA views that can be used to obtain a list of tables,
constraints, etc. You could use this to check that everything's there.
John.|||sql6.x does not have info_ views yet. You will have to look at sysobjects
for the list of objects. If you want to compare definition, you will have to
check out syscomments.
--
-oj
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL 2000. I
> want to have script to compare table related objects
> (index/PK/FK/constraint)
> for all the tables in the database to make sure nothing missing.
> Anyone can help?
>
>|||I thought that might be the case.
In which case (assuming the migration is straight over and the objects will
either exist and be correct, or be missing), sysobjects with an appropriate
filter on the xtype column sounds ideal - BOL has full documentation on this
column (e.g. a value of U for user table, F for foreign key etc.)
John.
oj wrote:
> sql6.x does not have info_ views yet. You will have to look at
> sysobjects for the list of objects. If you want to compare
> definition, you will have to check out syscomments.
>
> "Julia" <Julia@.discussions.microsoft.com> wrote in message
> news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
>> Hi,
>> I am migrating a database from one server SQL 6.5 to another SQL
>> 2000. I want to have script to compare table related objects
>> (index/PK/FK/constraint)
>> for all the tables in the database to make sure nothing missing.
>> Anyone can help?
I am migrating a database from one server SQL 6.5 to another SQL 2000. I
want to have script to compare table related objects (index/PK/FK/constraint)
for all the tables in the database to make sure nothing missing.
Anyone can help?Julia wrote:
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL
> 2000. I want to have script to compare table related objects
> (index/PK/FK/constraint) for all the tables in the database to make
> sure nothing missing.
> Anyone can help?
I'm not familiar with SQL 6.5, but in SQL 2000 there are the
INFORMATION_SCHEMA views that can be used to obtain a list of tables,
constraints, etc. You could use this to check that everything's there.
John.|||sql6.x does not have info_ views yet. You will have to look at sysobjects
for the list of objects. If you want to compare definition, you will have to
check out syscomments.
--
-oj
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
> Hi,
> I am migrating a database from one server SQL 6.5 to another SQL 2000. I
> want to have script to compare table related objects
> (index/PK/FK/constraint)
> for all the tables in the database to make sure nothing missing.
> Anyone can help?
>
>|||I thought that might be the case.
In which case (assuming the migration is straight over and the objects will
either exist and be correct, or be missing), sysobjects with an appropriate
filter on the xtype column sounds ideal - BOL has full documentation on this
column (e.g. a value of U for user table, F for foreign key etc.)
John.
oj wrote:
> sql6.x does not have info_ views yet. You will have to look at
> sysobjects for the list of objects. If you want to compare
> definition, you will have to check out syscomments.
>
> "Julia" <Julia@.discussions.microsoft.com> wrote in message
> news:34ACC6D7-4157-4324-9960-2F099809C4A3@.microsoft.com...
>> Hi,
>> I am migrating a database from one server SQL 6.5 to another SQL
>> 2000. I want to have script to compare table related objects
>> (index/PK/FK/constraint)
>> for all the tables in the database to make sure nothing missing.
>> Anyone can help?
Subscribe to:
Posts (Atom)