Store difference of 2 fields from 2 queries

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

SQL Server Development (2000)

Store difference of 2 fields from 2 queries

Author

Topic

flamz
Starting Member

21 Posts

Posted - 2008-04-04 : 18:25:40

Hi,
I need to store the result of the difference between a certain field in a table but from 2 different rows.
Example:



[TBL1]
[ID],[TOTAL],[DATE]
1, 10, '04/03/2008'
2, 8, '04/01/2008'
3, 6, '02/17/2008'

[TBL2]
[ID],[RESULT]

Now, I want to make a query that will do roughly this:


TBL2.RESULT = (SELECT TOTAL FROM TBL1 WHERE DATE='04/03/2008') - (SELECT TOTAL FROM TBL1 WHERE DATE='04/01/2008')

Not sure this is possible using one query...

Any tips appreciated!
tx.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-04-05 : 00:52:25

You show that your result needs to have an ID column and a RESULT column, but you don't show what the results need to be. Put the required results in there and someone can help you get to the results you want.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-04-05 : 03:28:45

Yes , it's possible , store the values in variables and then do the relevant INSERT/UPDATE statement

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-05 : 05:11:18

[code]INSERT INTO Table2 (ID,RESULT)
SELECT t1.ID,t1.TOTAL - t2.TOTAL
FROM Table1 t1
INNER JOIN Table1 t2
ON t1.ID = t2.ID-1[/code]

flamz
Starting Member

21 Posts

Posted - 2008-04-05 : 14:06:00

I described the tables wrong, let me try this again.


T1
[T1ID], [TOTAL], [DATE]
1,      10,      '04/03'2008'
1,      8,       '04/02'2008'
1,      6,       '04/01'2008'
2,      11,      '04/03'2008'
2,      10,      '04/02'2008'
2,      1,       '04/01'2008'
3,      10,      '04/03'2008'
3,      1,       '04/02'2008'
3,      0,       '04/01'2008'

/* This is the result I would expect if I ran a query that would 
give me, for each T1ID in table 1 the difference between the 
total of 04/03/2008 and 04/02/2008 */

T2
[T1ID], [RESULT]
1,      2
2,      1
3,      9

Now, I almost have it running using visakh16's tip, except I'm having trouble specifying the T1ID correctly. Here's what I have:


UPDATE T2
SET RESULT = res.DIFF FROM T1 INNER JOIN
(
SELECT (a.TOTAL - b.TOTAL) as DIFF
FROM T1 a
INNER JOIN T1 b
ON a.DATE='04/03/2008' AND b.DATE='04/02/2008'
) res
ON T2.T1ID = res.T1ID

Any idea what I am doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-05 : 14:45:37

[code]UPDATE t2
SET t2.[RESULT]=t1.[RESULT]
FROM T2 t2
INNER JOIN
(SELECT [T1ID],
SUM(CASE WHEN [DATE]='04/03/2008' THEN [TOTAL] ELSE 0 END) -
SUM(CASE WHEN [DATE]='04/02/2008' THEN [TOTAL] ELSE 0 END) AS [RESULT]
GROUP BY [T1ID])t1
ON t1.[T1ID]=t2.[T2ID][/code]

flamz
Starting Member

21 Posts

Posted - 2008-04-06 : 11:10:44

that's it!
Thanks visakh16!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources