sql update table from different columns of another table

I have 2 tables:

Table1

S/N  OnId    FromDate      ToDate      Value  
aaa   1     2010-10-09    2010-10-15     ?  
aaa   2     2010-10-10    20开发者_JAVA百科10-10-29     ?  
bbb   1     2010-10-16    2010-10-20     ?  

Table2

Id     Date          Count  
1     2010-10-03     100  
2     2010-10-03     150  
1     2010-10-08     200  
2     2010-10-12     250  
1     2010-10-13     300  
1     2010-10-14     400  
2     2010-10-17     450  
1     2010-10-20     500  

I need to find the most recent Count from Table2 with the same Id and a Date less than FromDate (call it 'FromCount'), and the most recent Count with the same Id and a Date less than ToDate (call it 'ToCount'), and update the Value column in Table1 with their difference. So the intermediate calculation would look like this:

S/N  OnId    FromDate      ToDate      FromCount  ToCount
aaa   1     2010-10-09    2010-10-15      200       400   
aaa   2     2010-10-10    2010-10-29      150       450  
bbb   1     2010-10-16    2010-10-20      400       500    

and after the UPDATE, Table1 would look like this:

Table1

S/N  OnId    FromDate      ToDate      Value  
aaa   1     2010-10-09    2010-10-15     200 (400-200)   
aaa   2     2010-10-10    2010-10-29     300 (450-150)  
bbb   1     2010-10-16    2010-10-20     100 (500-400)    

Afterwards I will need to summerize all the values for same S/N :

Table3

S/N    Total
aaa     500
bbb     100

I use sql server 2005 and hope to succeed using sql and will not need to write all this in my application in c# :)

Thanks!!!

You need two correlated subqueries:

Here's a query with one subquery:

SELECT t1.SN, t1.OnId, t1.FromDate, t1.ToDate,
      (SELECT TOP 1 Count
       FROM table2 t2To
       WHERE t2To.Id = t1.OnId AND t2To.Date <= t1.ToDate
       ORDER BY t2To.Date DESC)
FROM table1 t1

That gives you this:

aaa         1   2010-10-09    2010-10-15    400  
aaa         2   2010-10-10    2010-10-29    450  
bbb         1   2010-10-16    2010-10-20    500  

You need to add the other one, and then turn it into an UPDATE query:

UPDATE table1
SET Value = 
   (SELECT ...) - (SELECT ...)

This works:

 UPDATE table1  
 SET Value =   
 (
    isnull( 
       (SELECT TOP 1 Count 
        FROM table2 t2To 
        WHERE t2To.Id = t1.OnId AND t2To.Date <= t1.ToDate 
        ORDER BY t2To.Date DESC) 
    ,0.0)
 )-(
    isnull(
       (SELECT TOP 1 Count 
        FROM table2 t2To 
        WHERE t2To.Id = t1.OnId AND t2To.Date <= t1.FromDate 
        ORDER BY t2To.Date DESC) 
    ,0.0)
 )

Thanks!