differences between records - SQL Server Forums

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

differences between records

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-03-15 : 08:35:51

Hello and thanks in advance for your help on this.

I have two tables: f_aot and f_rev
they both contain jobkey and AOT records

So f_aot:

jobkey AOT
--------------
1 4100
2 5000
3 6000
.....
....
...
1000 4250

And f_rev:

jobkey AOT
--------------
1 3700
2 4200
3 5400
.....
....
...
1000 9250

I need to establish the differences for each job:

So I will have the following:

differences:

jobkey AOT
--------------
1 400
2 800
3 600
.....
....
...
1000 -5000

I have started off creating a temp table but cant get the
exact format

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-15 : 08:47:31

We'll only help you with your homework when you've shown some effort yourself. This query is really easy, so give it a try and post what you came up with and we can help you from there.

Jim

Everyday I learn something that somebody else already knew

tariq2
Posting Yak Master

125 Posts

Posted - 2011-03-15 : 12:23:55

Hi,

So the following should give me three rows for jobkey 457039
1strow AOT for jobkey 457039
2ndrow rev for jobkey 457039
3rd row diff between rev and AOT for jobkey 457039

A few points about this:
1) The code
'INSERT into #Data
0,0,0,REV-aOT'
does not function

2) For each jobkey I want one row with AOt,Rev,diff
3) I would like this extended for teh whole sample of jobkeys

Regards

CREATE TABLE #data (jobkey int, rev bigint,AOT bigint,revAOT bigint)

INSERT into #Data
select max(jobkey),SUM(rev),0,0
from F_REV
where JobKey = 457039 and LedgerKey = 10 AND PeriodKey >84 and PeriodKey <97

INSERT into #Data
select max(jobkey),0,SUM(revenue),0
from F_AOT
where JobKey = 457039 and LedgerKey = 20 AND PeriodKey >84 and PeriodKey <97

INSERT into #Data
0,0,0,REV-aOT

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-03-16 : 02:43:32

select t1.jobkey,t1.AOT as table1AOT,t2.AOT as table2AOT,t1.AOT-t2.AOT as diffAOT
from table1 t1 join table2 t2 on t1.jobkey=t2.jobkey

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources