Is there a way to dynamically execute queries in SQL without using user defined function?

I am currently working on the table containing some documents data, focusing on the columns containing date of document and net income. What I have to do is to write an sql statement which will result in a table showing the summary of each year’s net income divided into months, like this:

    year |          january           |          february          | ... | december
    -------------------------------------------------------------------------------
    2019 | [total income in jan 2019] | [total income in feb 2019] | ...
    2018 | [total income in jan 2018] | ...
     ...

To solve this, I created a following function:

-- returns total income for given @month in given @year

CREATE FUNCTION dbo.Getincome(@year  INT, 
                              @month INT) 
returns INT 
AS 
  BEGIN 
      DECLARE @result INT 

      SELECT @result = Sum(income) 
      FROM   dk 
      WHERE  date IS NOT NULL 
             AND Year(date) = @year 
             AND Month(date) = @month 
      GROUP  BY Year(date) 

      RETURN Isnull(@result, 0) 
  END

and executed query like that:

SELECT   Year(date)                          AS 'year', 
         dbo.Getincome(Year(date), 1)        AS 'january', 
         dbo.Getincome(Year(date), 2)        AS 'february', 
         (...) dbo.getincome(year(date), 12) AS 'december' 
FROM     dk 
WHERE    date IS NOT NULL 
GROUP BY year(date) 
ORDER BY year(date) DESC

This results in the expected table. BUT The problem is, my boss would like me to not use any functions for some reason. I can declare variables, tho. So my question is, is there a way to solve this without using a function? A while loop maybe? I know I could make a select command for each month and then join them together but I am looking for a more sophisticated solution than eleven joins… Any idea? 🙂

Answer

No need for this convoluted logic involving the function. As I understand your problem statement, you can get the result that you want in a single query, using conditional aggregation:

select  year(date) year,
    sum(case when month(date) = 1 then income else 0 end) january,
    sum(case when month(date) = 2 then income else 0 end) february,
    ...
from dk
group by year(date)
order by year(date)

Advertisement

Answer