How to Update this request Balance Column

[Robymon]

EmpID Leave_Year Taken Credit Balance 435 2014 0 22 22 435 2013 10 22 12 435 2012 24 22 0 435 2011 20 22 2 435 2010 23 22 0 435 2009 21 22 1 I want to Update this table Balance column value with the Next row Balance column value eg: Leave_Year 2011, Balance value (2) Update with Leave_Year 2012 Balance Value (0) 0 - 2 = -2 should get in 2011 Balance column, use with this where condition, where Next row (2012) Taken>0 How to write Update sql for this

[Blue_Boy]

Can you explain more in details your case?

---

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.

[gvprabu]

Hi, Check the Script

SELECT M.ID,(ROW_NUMBER() OVER(PARTITION BY M.EmpID ORDER BY M.EmpID)) 'RowNumber', M.EmpID, M.Leave_Year, M.Taken, M.Credit,
(SELECT SUM((ISNULL(Balance,0.0))
FROM Table_Name WHERE ID<=M.ID AND EmpID=M.EmpID)
FROM Table_Name M
ORDER BY M.EmpID

[Jorgen Andersson]

I wouldn't do that. It's against the "Rules" of normalization. You don't want to make a row dependant on another row in the same table. Think of all the fuzz to update the table when you find out someone inserted some wrong data last year, or when HR decides to change the rules (quite probable actually). So you should make a query that gets you the result you want instead. Hint, checkout ROLLUP. For example:

SELECT EmpID,
CASE WHEN (GROUPING(Leave_Year) = 1) THEN 'Total Balance'
ELSE ISNULL(Leave_Year, 'UNKNOWN')
END AS Leave_Year,
SUM(Balance) AS Balance
FROM MyTable
GROUP BY EmpID, Leave_Year WITH ROLLUP

I haven't tested this code, but it should give you an idea. I also assumed SQLServer, Different Databases have different syntax. Use Google.

Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln