SQLTeam.com | Weblogs | Forums

SQL Approach


#1

Hi

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

Hope this makes sense.

Can anyone get me started?

Thanks

Phil


#2

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;

#3

Thanks James; just what I needed

Phil