Move aggregate logic to separate function in SQL Server 2008 R2

Question

Please correct me if I'm using the wrong terminology or description, but I have business logic within an aggregate function (MIN and MAX). I wish to move to the logic to it's own function. I wish to do this so I don't have to make multiple changes in different locations when the client requests. I have the following

SELECT DISTINCT 
        DATA_MONTH,
        DATA_DATE,
                CASE 
                  WHEN MIN(DATA_YEAR) = MAX(DATA_YEAR) 
                       AND MIN(DATA_YEAR) = @BEGIN_YR THEN 'Started' 
                  WHEN MIN(DATA_YEAR) = MAX(DATA_YEAR) 
                       AND MIN(DATA_YEAR) <> @CURRENTFY THEN 
                  CAST((MIN(DATA_YEAR) %100)-1 AS VARCHAR) 
                  + '/' 
                  + CAST(MIN(DATA_YEAR)%100 AS VARCHAR) 
                  WHEN MIN(DATA_YEAR) <> MAX(DATA_YEAR) 
                       AND MIN(DATA_YEAR) = @BEGIN_YR THEN 
                  'Started' + ' from ' 
                  + CAST((MAX(DATA_YEAR) %100)-1 AS VARCHAR) 
                  + '/' 
                  + CAST(MAX(DATA_YEAR)%100 AS VARCHAR) 
                  ELSE CAST((MIN(DATA_YEAR) %100)-1 AS VARCHAR) 
                       + '/' 
                       + CAST((MIN(DATA_YEAR) %100) AS VARCHAR) 
                       + ' through ' 
                       + CAST((MAX(DATA_YEAR) %100)-1 AS VARCHAR) 
                       + '/' 
                       + CAST(MAX(DATA_YEAR)%100 AS VARCHAR) 
                END                                 AS [STATUS] 
FROM   TABLE_A 
WHERE   DATA_MONTH IN ('03', '04')
        AND DATA_DATE = '01'
GROUP  BY DATA_MONTH, 
          DATA_DATE

Which I would like to change it to this:

SELECT DISTINCT 
        DATA_MONTH,
        DATA_DATE,
        dbo.getStatus(DATA_YEAR) AS [STATUS]
FROM   TABLE_A 
WHERE   DATA_MONTH IN ('03', '04')
        AND DATA_DATE = '01'
GROUP  BY DATA_MONTH, 
          DATA_DATE

The logic for getStatus() has the case statements and can output:

NULL
Started
15/16
Started from 15/16
15/16 through 16/17

My question is how can I restructure my logic to make this possible as I have a GROUP BY clause?

Answer 1

You can create a scalar user defined function and use it in the SELECT. Since it needs to be aggregated, you should be able to throw a MAX around it.

Although, I'd be interested to see if this works.

Nicarus is probably right and that you need a user defined aggregate function.

USE [AdventureWorks2012]
GO

/****** Object:  Table [dbo].[TABLE_A]    Script Date: 7/20/2016 3:23:39 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TABLE_A](
    [DATA_DATE] [date] NULL,
    [DATA_MONTH] [int] NOT NULL,
    [DATA_YEAR] [int] NOT NULL
) ON [PRIMARY]

GO

INSERT INTO TABLE_A SELECT CAST ('2016-03-02'  as DATE) AS DATA_DATE, 3 as DATA_MONTH, 2016 AS DATA_YEAR 

CREATE FUNCTION dbo.getStatus(@MAXYR int, @MINYR int, @BEGIN_YR int ,@CURRENTFY int)  
RETURNS varchar(30)
AS   
BEGIN  
    DECLARE @ret varchar(30);  
    SELECT @ret = CASE 
                  WHEN @MINYR = @MAXYR 
                       AND @MINYR = @BEGIN_YR THEN 'Started' 
                  WHEN @MINYR = @MAXYR 
                       AND @MINYR <> @CURRENTFY THEN 
                  CAST((@MINYR %100)-1 AS VARCHAR) 
                  + '/' 
                  + CAST(@MINYR%100 AS VARCHAR) 
                  WHEN @MINYR <> @MAXYR 
                       AND @MINYR = @BEGIN_YR THEN 
                  'Started' + ' from ' 
                  + CAST((@MAXYR %100)-1 AS VARCHAR) 
                  + '/' 
                  + CAST(@MAXYR%100 AS VARCHAR) 
                  ELSE CAST((@MINYR %100)-1 AS VARCHAR) 
                       + '/' 
                       + CAST((@MINYR %100) AS VARCHAR) 
                       + ' through ' 
                       + CAST((@MAXYR %100)-1 AS VARCHAR) 
                       + '/' 
                       + CAST(@MAXYR%100 AS VARCHAR) 
                END 
    RETURN @ret;  
END; 

DECLARE @BEGIN_YR INT = 2;
DECLARE @CURRENTFY int = 2016;

SELECT DISTINCT 
DATA_MONTH,
DATA_DATE,
dbo.getStatus(MAX(DATA_YEAR), MIN(DATA_YEAR), @BEGIN_YR, @CURRENTFY)  as STATUS
FROM TABLE_A
GROUP BY DATA_MONTH, DATA_DATE