I have have two tables Tbl1 has Id, CalcFld. and Tbl2 that has Id, Value, Date. For a given sets of Tbl2.Date in Tbl2 I want to set the Tbl1CalcFld = Tbl1.CalcFld + Tbl2.Value if it is an update and if it is an insert the nTbl1.CalcFld = Tbl2.Value.
I can get one or the other to work correctly only once and then it seems to just add 1 to the Tbl1.CalcFld if the Tbl2.Id value alread exist in Tbl1.
What am I missing?
SET CalcFld = (select row_Number() over(ORDER by Value DESC) as Row
from Tbl2 where tbl2.Id = Tbl1.Id and Tbl2.Date = '4/1/09')
IF @@ROWCOUNT = 0
INSERT Tbl1 (Id, CalcFld)
select Id, row_Number() over(ORDER by Value DESC) as Row
where Tbl2.Date = '4/1/09'