CODEWITHSUNDEEP
sqlsql-server
intrigued_66
intrigued_66

Reputation: 17220

Easiest way to implement this SQL query?

I have an excel function: =Max(0, -Min(A1-B1, C1-B1))

and A1,B1,C1 are the Value column in the table:

Date, Key, Value

assume for now the Date is irrelevant and the Key is A,B,C for A1,B1,C1

How do I implement this excel function? The way I originally did it I had two joins (to get the values all in separate columns on the same row) but this seemed like overkill and there must be a smarter way?

Upvotes: 2

Views: 69

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Your question is suggesting a separate value for each row. If so, then this is the query:

select t.*
       (case when -leastval < 0 then 0
             else -lesatval
        end) as TheValue
    end
from (select t.*,
             (case when A1 - B1 < C1 and A1 - B2 < B1 then A1 - B1
                   when C1 < B1 then C1
                   else B1
              end) as leastval
      from table t
     ) t

Now that I read the question right, let me assume that there is one value of A, B, and C per date. Then you would use a variation on the above, by simply denomalizing the data first:

select t.*
       (case when -leastval < 0 then 0
             else -leastval 
        end) as TheValue
    end
from (select t.*,
             (case when A - B < C and A - B < B then A - B
                   when C < B then C
                   else B
              end) as leastval
      from (select date, 
                   min(case when key = 'A' then value end) as A,
                   min(case when key = 'B' then value end) as B,
                   min(case when key = 'C' then value end) as C
            from table t
            group by date
           ) t
     ) t

There are other ways to formulate this. I tried to stay reasonably close to the Excel formulation.

By the way, some other databases offer Greatest() and Least() functions which may such a formula much easier to create.

Upvotes: 1

Andomar
Andomar

Reputation: 238058

You could probably reduce it to one join, since you only need B for the A part:

select  case 
        when min(Value - isnull(yt2.Value,0)) > 0 then 0
        else -min(Value - isnull(yt2.Value,0))
        end
from    YourTable yt1
join    YourTable yt2
on      yt1.Key = 'A' and yt2.key = 'B'
        or
        yt1.Key = 'B' and yt2.key = 'C'

Upvotes: 2

Related Questions