finding the difference in values from two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

finding the difference in values from two tables

Author

Topic

neosapien
Starting Member

3 Posts

Posted - 2012-03-11 : 05:44:14

Hi there

I have a table extracted using the following query:

select col_1, max(col_2) from table_1 where
col_3_val = 2 and
col_4_val > '2012-03-11 00:00:00.000' and col_1 in
(select col_1 from table_2 where
col_5_val in (1024, 1053) and
col_4_val > '2012-03-11 00:00:00.000')
group by col_1 order by 1

and another table using the same query but with one extra condition in the parent query. Both tables have exactly the same number(2) of columns and the field types(integer) are also the same. Lets say the output gives table_3 and table_4. I want to find out for each value in the second column table_3, the difference from the corresponding value in the second column of table_4.

Can someone help me please?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-11 : 09:26:02

If you want to just find which rows are in one table and not in the other, you can use the EXCEPT keyword. For example:

SELECT * FROM table_3
EXCEPT
SELECT * FROM table_4;

Or vice versa for finding rows that are in table_4, but not in table_3. INTERSECT keyword will let you pick up rows that are common in both tables.

If you want to compare the values side by side, you can do a full join on the two tables like this:

SELECT
	COALESCE(t3.col_1,t4.col_1) AS col_1,
	t3.MaxCol AS T3MaxCol,
	t4.MaxCol AS T4MaxCol
FROM
	table_3 t3
	FULL JOIN table_4 t4 ON
		t3.col_1 = t4_col1;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-11 : 11:42:41

quote:
Originally posted by neosapien

Hi there

I have a table extracted using the following query:

select col_1, max(col_2) from table_1 where
col_3_val = 2 and
col_4_val > '2012-03-11 00:00:00.000' and col_1 in
(select col_1 from table_2 where
col_5_val in (1024, 1053) and
col_4_val > '2012-03-11 00:00:00.000')
group by col_1 order by 1

and another table using the same query but with one extra condition in the parent query. Both tables have exactly the same number(2) of columns and the field types(integer) are also the same. Lets say the output gives table_3 and table_4. I want to find out for each value in the second column table_3, the difference from the corresponding value in the second column of table_4.

Can someone help me please?

sounds like a JOIN scenario to me

If you can post some sample data and explain your requirement we might be to help you out.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neosapien
Starting Member

3 Posts

Posted - 2012-03-18 : 02:32:34

Thanks for the response, guys. Sorry for the delay. Here's some sample data - the output tables are:

Table_3

id max(ticketcount)
1 5214
2 154
3 2341
4 521
5 552

Table_4

id max(ticketcount)
1 5277
2 163
3 2348
4 537
5 558

What I want to find here is the values in table_4 where the max(ticketcount) is greater than those in table_3 by 15. So the result I'm expecting is:

Table_5

id difference
1 63
4 16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-18 : 12:42:34

use maxcol queries like this


SELECT
FROM (first query) q1
INNER JOIN (second query) q2
ON q2.id = q1.id
WHERE (q2.maxcount - q1.maxcount ) >15

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neosapien
Starting Member

3 Posts

Posted - 2012-03-19 : 03:00:52

yup....that did it...thanx a ton, visakh16 n sunitabeck.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-19 : 09:39:33

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources