Problem in getting Sum

Question

I have this data:

name    qty     date                    flag  
---------------------------------------------    
abc     255     11/10/1986 12:00:00 AM  IN
abc     300     11/10/2010 12:00:00 AM  IN
abc     12      11/10/2012 12:00:00 AM  OUT
abc     13      11/9/2010 12:00:00 AM   OUT
NULL    NULL    NULL                    NULL

I want to get sum of qty at that specific row:

• If flag is "in", then it will add to the sum
• if flag is "out", then it will subtract from the sum

Answer 1

SELECT SUM(case flag when 'IN' then qty else -qty end)
from table
WHERE ..... Your conditions here ...

Answer 2

I can think of three ways to do this. The first is a loop with an if statement, I probably wouldn't use that option.

The second is to use Linq to do the calculation, check out this website if you don't know Linq http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

You could probably use Lamda Expressions however I'm not great with them and I'm not 100% sure how to go about it, but I feel Lamda Expressions will most likely give you the most elegant solution.