If Statement in Table valued Function

I have 2 table-valued function that are very similar, the only difference is that I need one additional Max() call on the second one. I want to consolidate these into one but I can't quite figure out how to do it.

First Function

ALTER FUNCTION [DayTrade].[udf_GetTotalLast90Days] 
(   
    @Date       datetime
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT    Acct, Sum(AGGCnt) As AGGCnt, AGGNumb
    FROM      DT.vwGet_CountHist
    WHERE   (PostDate >= @Date - 90) AND (PostDate <= @Date)
    GROUP BY Acct, AGGNumb
)

Second Function

ALTER FUNCTION [DayTrade].[udf_GetTotalLast90Days] 
(   
    @Date       datetime
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT     Acct, Sum(AGGCnt) As AGGCnt, Max(AGGNumb) As AGGNumb
    FROM      DT.vwGet_CountHist
    WHERE   (PostDate >= @Date - 90) AND (PostDate <= @Date)
    GROUP BY Acct
)

As you can see the difference in the second one is I am adding the Max(AGGNumb) As AGGNumb and removing one of the Group Bys. I attempted to change this and pass in a additional variable @Agg tha开发者_如何学Ct was a bit field to then use an IF statement to choose which function to use but I couldn't get it to work.

Any suggestions about how to consolidate these two functions into 1?

Thanks

One parameter will be NULL, so the WHERE fails for one clause only.

ALTER FUNCTION [DayTrade].[udf_GetTotalLast90Days] 
(   
    @Date       datetime = NULL,
    @BusinessDate = NULL
)
RETURNS TABLE  AS RETURN  
(
    SELECT    Acct, Sum(AGGCnt) As AGGCnt, AGGNumb
    FROM      DT.vwGet_CountHist
    WHERE   (PostDate >= @Date - 90) AND (PostDate <= @Date)
    GROUP BY Acct, AGGNumb
    UNION ALL
    SELECT     Acct, Sum(AGGCnt) As AGGCnt, Max(AGGNumb) As AGGNumb
    FROM      DT.vwGet_CountHist
    WHERE   (PostDate >= @BusinessDate - 90) AND (PostDate <= @BusinessDate)
    GROUP BY Acct
)

You could pass a control parameter to the function like so (untested)

EDIT: added nested SELECT

ALTER FUNCTION [DayTrade].[udf_GetTotalLast90Days]  
    (        @Date       datetime, @GetMax tinyint ) 
    RETURNS TABLE  AS RETURN  
    (     SELECT     Acct, Sum(AGGCnt) As AGGCnt, 
    CASE @GetMax = 1 THEN (SELECT Max(AGGNumb) FROM DT.vwGet_CountList WHERE (...)GROUP BY Max(aGGNumb)) 
ELSE (SELECT AGGNumb FROM DT.vwGet_CountList WHERE (...)) AS AGGNum  END 
        FROM      DT.vwGet_CountHist     
        WHERE   (PostDate >= @BusinessDate - 90) AND (PostDate <= @BusinessDate)     
        GROUP BY Acct ) 

If that doesnt work, then you may need to implement the two SELECTs separately, but in the same Procedure - i know doesnt really solve the problem but it will only require 1 procedure.

I think, this should work:

ALTER FUNCTION [DayTrade].[udf_GetTotalLast90Days] 
(   
    @Date       datetime,
    @Agg        bit
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT    Acct, Sum(AGGCnt) As AGGCnt, MAX(AGGNumb) AS AGGNumb
    FROM      DT.vwGet_CountHist
    WHERE   (PostDate >= @Date - 90) AND (PostDate <= @Date)
    GROUP BY Acct, AGGNumb * (1 - @Agg)
)

When @Agg = 1, it will group by Acct only, when @Agg = 0, then by Acct, AGGNumb.