Geek Answers Handbook

The problem of getting the amount

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 the qty amount in this particular line:

  • If flag "in", then it will add to the amount
  • if flag "out", then it will be subtracted from the amount
+6
sql sql-server tsql
source share
2 answers
 SELECT SUM(case flag when 'IN' then qty else -qty end) from table WHERE ..... Your conditions here ...
+13
source share

I can come up with three ways to do this. The first is a loop with an if statement, I probably won’t use this option.

The second is to use Linq for calculation, check this site if you do not know Linq http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

You could probably use Lamda Expressions, but I'm not very good with them, and I'm not 100% sure how to do this, but I feel that Lamda Expressions is likely to give you the most elegant solution.

-one
source share

More articles:


All Articles