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 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

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

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

Compare csv file layout to SQL Server table

I am creating a facility whereby you can select a source file (.csv), and a target (SQL table).

Then I call a DTS to copy the csv file to the SQL table.

How can I validate that the two files have the same number of columns ?

Moved to SQL Server Tools forum|||In order to check the number of rows you need to work on DTS with workflow, refer to books online for more information and you can schedule the DTS package a SQLagent job by right-click on selected package.|||I need to validate that the correct file has been selected before scheduling the DTS package. This is why I need to do it in my VB.Net Windows application.|||

You can write a custom Script Task inside the SSIS package that will open the file and check how many columns it has. You can utilize Excel object model for that or just go with regular .NET IO libraries.

Hope that helps.

Maciek Sarnowicz

Compare csv file layout to SQL Server table

I am creating a facility whereby you can select a source file (.csv), and a target (SQL table).

Then I call a DTS to copy the csv file to the SQL table.

How can I validate that the two files have the same number of columns ?

Moved to SQL Server Tools forum|||In order to check the number of rows you need to work on DTS with workflow, refer to books online for more information and you can schedule the DTS package a SQLagent job by right-click on selected package.|||I need to validate that the correct file has been selected before scheduling the DTS package. This is why I need to do it in my VB.Net Windows application.|||

You can write a custom Script Task inside the SSIS package that will open the file and check how many columns it has. You can utilize Excel object model for that or just go with regular .NET IO libraries.

Hope that helps.

Maciek Sarnowicz

comment/uncomment keyboard shortcuts changed .. grrrrr!!!!

Call me old-school, but I happened to like the one-key Shift-Ctrl-C and Shift-Ctrl-R methods of commenting and uncommenting T-SQL code. I've tried to see how I can set up my own macro's to give me those key combos back instead of the double-the-work ctrl-K + ctrl-C and ctrl-K + ctrl-U keyboard short cuts.

The k-c one is the worst because it takes two hands to type it, but the two fingered k-u isn't much better.

Can someone tell me how I can override or augment the keyboard shortcuts so that I can get these two familiar short-cuts back. I don't care if I have to override something .. whatever it is, it's not nearly as imporant as this to me.In Management Studio goto Tools>Options and select Environment>Keyboard.

Change the Keyboard scheme to SQL Server 2000

--

HTH

Jasper Smith (SQL Server MVP)

http://www.sqldbatips.com

I support PASS - the definitive, global

community for SQL Server professionals -

http://www.sqlpass.org

wrote in message

news:c3b85d6a-9303-480e-877d-7bb4caf9cab6@.discussions.microsoft.com...

> Call me old-school, but I happened to like the one-key Shift-Ctrl-C and

> Shift-Ctrl-R methods of commenting and uncommenting T-SQL code. I've

> tried to see how I can set up my own macro's to give me those key combos

> back instead of the double-the-work ctrl-K + ctrl-C and ctrl-K + ctrl-U

> keyboard short cuts.

>

> The k-c one is the worst because it takes two hands to type it, but the

> two fingered k-u isn't much better.

>

> Can someone tell me how I can override or augment the keyboard shortcuts

> so that I can get these two familiar short-cuts back. I don't care if I

> have to override something .. whatever it is, it's not nearly as

> imporant as this to me.

>|||In Management Studio goto Tools>Options and select Environment>Keyboard. Change the Keyboard scheme to SQL Server 2000

Comment/Uncomment keyboard shortcut changed .. grrrrr!!!!

Call me old-school, but I happened to like the one-key Shift-Ctrl-C and Shift-Ctrl-R methods of commenting and uncommenting T-SQL code. I've tried to see how I can set up my own macro's to give me those key combos back instead of the double-the-work ctrl-K + ctrl-C and ctrl-K + ctrl-U keyboard short cuts.

The k-c one is the worst because it takes two hands to type it, but the two fingered k-u isn't much better.

Can someone tell me how I can override or augment these so that I can get these two familiar short-cuts back. I don't care if I have to override something .. whatever it is, it's not nearly as imporant as this to me.I'm getting used to just using the toolbar comment/un-comment.

Have to agree, there are a few 'little' things that are quite irritating...

I miss the line/column count in the status bar at the bottom. Made finding your errors fairly easy most of the time.

I miss the spinning globe on the dropdown Window list to see if a long running process is still running.

And the ctrl-b to move the resultpane was nice too. It's pretty hit or miss to get the mouse just right to move it now...

Nor can you scroll with a mouse wheel in the Execution Plan Results anymore.

Progress eh? :)|||Actually for Visual Studio developers the change is welcome/necessary because that is consistent with the VS environment. I have to say you have a point about the k-c combination though |||In Management Studio you should be able to go to

Tools -> Customize -> Commands -> Keyboard, then pick Keyboard in Environment and change Keyboard Scheme to SQL Server 2000. You should get your shortcuts back.

HTH,
Boris.

p.s. I should add that you will NOT get ALL of your shortcuts back. Sorry!|||That worked.

You get a Big kiss(tm) for that little tid-bit!

X X
X
X X

:-)|||Yeap, ctrl-b still doesn't work, bummer. Thanks for the tidbit though.

It's nice to have the comment/uncomment back. :)|||

As in the VS2005 IDEs you can use Ctrl-K,C Ctrl-K,U to comment/uncomment source, that's an option, or not ?

Sascha

command to reference another table

Hi all,

I am new to this site and I hope anyone out there can help me. I was tasked to change the constraints of my existing table. Lets call it table1. This table has an attribute that needs to take the value of another attribute of another table ( let's call it tables2) and that attribute must satisfy a certain expression ( I suppose I can isolate it by using the select statement ).

Anyone know how to get this done.

Please advice.

Thanks and appreciated.Hi all,

I am new to this site and I hope anyone out there can help me. I was tasked to change the constraints of my existing table. Lets call it table1. This table has an attribute that needs to take the value of another attribute of another table ( let's call it tables2) and that attribute must satisfy a certain expression ( I suppose I can isolate it by using the select statement ).

Anyone know how to get this done.

Please advice.

Thanks and appreciated.
u have given very little information.Read the sticky (first post on this forum) first|||This sounds like homework to me. If it is, please post the URL for the assignment, or at least scan the page so we can see the "whole tamale" and solve the problem once instead of giving you what you asked for, but not what the teacher wants. If this is not homework, please see How to ask a question to get quick and correct answers? (http://www.dbforums.com/showthread.php?t=1212452#post4527530) in this forums FAQ.

-PatP|||Hi all,

I have yet to try this statement:-

alter table coursefee
add check ( course_code = course.course_code and
course.quota is not null );

coursefee :- table1
course_code :- PK of table1
course :- table2
course.course_code :- PK of table2
course.quota :- another attribute of table2

My intention is set a constraint on table1 where there will be an error if there is an insert on table2 whereby quota ( from table1) is null.

Is this plausible?

Sorry to trouble all again.|||I don't think you can reference another table in a CHECK CONSTRAINT. I suspect that you'll need to use a trigger if you really want to do this, but I wouldn't recommend doing it, for many reasons.

-PatP