Home Storing case statement result in a variable SQL SEVER
Reply: 3

Storing case statement result in a variable SQL SEVER

Ryan Gadsdon
1#
Ryan Gadsdon Published in 2017-09-13 09:31:21Z

Is there anyway to store the result of a case statement in a variable so that it has a fixed result that i can return at any point.

e.g.

    CASE 
    WHEN MONTH([DATE]) = MONTH(getdate()) AND
    YEAR([DATE]) = YEAR(getdate())
    THEN SUM(Figure)
    OVER (PARTITION BY [Name], 
    MONTH([DATE])) 
    ELSE 0 
    END INTO @MTD

Ideally i want to be able to return a consisted month to date value where the calculation is not affected by where clauses

HLGEM
2#
HLGEM Reply to 2017-09-14 14:24:06Z

If you use a variable to store the profit it will only be able to store one row, therefore you should add a where clause to select a specific [Name], [Month] and probably [Year] for which you want to retrieve the overall profit. You should add [Year] to your partition as well as to the WHERE clause unless you want to sum profits from the same month in different years.

The resulting code would be like this:

declare
    @val int = 0;

select     
    @val = CASE 
        WHEN MONTH([DATE]) = MONTH(getdate()) AND
        YEAR([DATE]) = YEAR(getdate())
        THEN SUM(PROFIT)
        OVER (PARTITION BY [Name], 
            MONTH([DATE]), YEAR([DATE])) 
        ELSE 0 
        END 
FROM table_name
WHERE [Name] = @Name AND
      MONTH([DATE]) = @Month AND
      YEAR([DATE]) = @Year

print @val
Rahul Tripathi
3#
Rahul Tripathi Reply to 2017-09-13 09:34:08Z

You can try like this:

declare
  @MTD varchar(100) = null;

select 
  @MTD = CASE 
    WHEN MONTH([DATE]) = MONTH(getdate()) AND
    YEAR([DATE]) = YEAR(getdate())
    THEN SUM(PROFIT)
    OVER (PARTITION BY [Name], 
    MONTH([DATE])) 
    ELSE 0 
    END
Anagha
4#
Anagha Reply to 2017-09-13 10:11:50Z

Try this:

declare
  @val int = 0;

select 
  @val = CASE 
    WHEN MONTH([DATE]) = MONTH(getdate()) AND
    YEAR([DATE]) = YEAR(getdate())
    THEN SUM(isnull(PROFIT,0))
    OVER (PARTITION BY [Name], 
    MONTH([DATE])) 
    ELSE 0 
    END
    from table_name

    print @val
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.433122 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO