Can someone give me some pointers on how to approach this in SQL (MS SQL 2008) ....
Table-1 has a Status, a Value and an ID
Table-2 is linked to Table-1.ID via Table-2.Table1ID (many Table-2 rows to one Table-1 row)
Table-3 is linked to Table-2 .ID via Table-3.Table2ID and has a Value (many Table-3 rows to one Table-2 row)
What I need to do is create a query that can run against Table-1 and check the total of the linked Table-3 values. If the sum of Table-3.Value equals the Table-1.Value, I need to set Table-1.Status to Closed. If not, I need to set Table-1.Status to Open
You can run the following query to compare the value in Table1 against the sum of values in table3.
select t1.[Value], t1.[Id], SUM(t3.[Value]) as T3Total from Table1 t1 left join Table2 t2 on t1.Id = t2.Table1ID left join Table3 t3 on t2.Id = t3.Table2Id group by t1.Id, t1.[Value]
If the results look right (do some spot checks) then you can do the update to status column in table1 like this
;with cte as
(
select
t1.[Value],
t1.[Id],
SUM(t3.[Value]) as T3Total
from
Table1 t1
left join Table2 t2 on
t1.Id = t2.Table1ID
left join Table3 t3 on
t2.Id = t3.Table2Id
group by
t1.Id,
t1.[Value]
)
update t1 set
[Status] = case when c.T3Total = c.Value then 'Closed' else 'Open' end
from
cte c
inner join Table1 t1 on
t1.Id = c.Id;