shorter way of finding differences

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

General SQL Server Forums

New to SQL Server Programming

shorter way of finding differences

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-03-28 : 08:35:54

Hi,

I have two tables tempaot1 and tempaot2 with
two columns:jobkey and AOT
and
the following values:
tempaot1:
jobkey AOT
1 250
2 2500
3 2400
1 270
2 2400
3 2200

tempaot2:
jobkey AOT
1 150
2 1500
3 1400

I need to create a simple query to establish the differences
between each table for each jobkey, so

jobkey tempaot1 - tempaot2
1 370
2 3400
3 3200

-------------------------------------------------------------
I have actually created two temptables and the below works but I would like something shorter than this. Thank you

create table #data
(jobkey int,
AOT1 int,AOT2 int)

insert into #data
select jobkey,sum(aot),0 from tempaot1
group by jobkey
insert into #data
select jobkey,0,sum(aot) from tempaot2
group by jobkey

select jobkey,SUM(aot1),SUM(aot2),SUM(aot1) - SUM(aot2) as diff
from #data
group by jobkey

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-28 : 08:44:51

you don't need the temp table. You can use CTE or derived query to do it.

-- CTE


; with cte as
(
    select jobkey, sum(aot) as aot1, 0 as aot2 from tempaot1 group by jobkey
union all
    select jobkey, 0 as aot1, sum(aot) as aot2 from tempaot2 group by jobkey
)
select  jobkey, SUM(aot1), SUM(aot2), SUM(aot1) - SUM(aot2) as diff
from     cte1
group by jobkey


-- Derived Table
select  jobkey, SUM(aot1), SUM(aot2), SUM(aot1) - SUM(aot2) as diff
from     
(
    select jobkey, sum(aot) as aot1, 0 as aot2 from tempaot1 group by jobkey
union all
    select jobkey, 0 as aot1, sum(aot) as aot2 from tempaot2 group by jobkey
) t
group by jobkey

KH
[spoiler]Time is always against us[/spoiler]

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-28 : 08:46:42

Something like this...?


declare @t1 table (
	jobkey int,
	AOT int
)

Insert Into @t1
Select 1, 250 Union All
Select 2, 2500 Union All
Select 3, 2400 Union All
Select 1, 270 Union All
Select 2, 2400 Union All
Select 3, 2200

declare @t2 table (
	jobkey int,
	AOT int
)
Insert Into @t2
Select 1, 150 Union All
Select 2, 1500 Union All
Select 3, 1400



Select t1.jobkey, t1.AOT - t2.AOT 
From (Select jobKey, AOT = sum(AOT) From @t1 Group By jobkey) t1
Inner Join (Select jobKey, AOT = sum(AOT) From @t2 Group By jobkey) t2
On t1.jobkey = t2.jobkey

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

tariq2
Posting Yak Master

125 Posts

Posted - 2011-03-28 : 08:54:32

Thank yu both, khtan and seventhnight.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources