SQL Server 2008 R2 – Function to Perform Calculations Based on Variable

sql serversql-server-2008-r2t-sql

I saw an answer by @ypercubeᵀᴹ that gave a great answer on how to calculate what appeared to be a quantity ordered by 3 and give whole number answers. Which that got me thinking, how would you handle a situation if there was a variable number of times to split?

Meaning this was the OP where you would always split by 3 (Original Poster

Create Table #Orders 
(
    id int IDENTITY(1,1) PRIMARY KEY NOT NULL
    ,partid varchar(100) NOT NULL
    ,qtyordered int DEFAULT '0'
    ,orderedby varchar(100) NOT NULL
    ,ordereddate date DEFAULT GETDATE()
) ;

Insert Into #Orders (partid, qtyordered, orderedby) VALUES
('SS100', 10, 'James'), ('RR200', 5, 'Bob'), ('NN300', 3, 'Jake'), ('OO400', 5, 'Blue') ;

SELECT 
   partid,
   qtyordered,
   [First], 
   [Second],
   [Third] 
FROM 
    #Orders 
  CROSS APPLY
    ( SELECT [Third]  = (qtyordered)                      / 3 )  AS q3
  CROSS APPLY
    ( SELECT [Second] = (qtyordered - [Third])            / 2 )  AS q2
  CROSS APPLY
    ( SELECT [First]  = (qtyordered - [Third] - [Second]) / 1 )  AS q1;

dbfiddle here

However, what if instead of always splitting by 3, you had an int variable that stated how many times to split, say same DDL but instead of splitting by 3 you use

Declare @TTS int = 5

And now you split each scenario 5 ways instead of 3. Basically a re-usable function that can "on-the-fly" split based off a variable?

Best Answer

UPDATE

I've used a recursive function to calculate each int item, and a dynamic query to pivot the result.

Declare @TTS int = 5

--= Add divisors 
;with AddDiv as
(
    SELECT partid, qtyordered, t.divisions
    FROM   #Orders
    CROSS APPLY (SELECT TOP (@TTS) ROW_NUMBER() OVER (ORDER BY S.[object_id]) [divisions]
                 FROM    sys.all_objects S) t
)
    --= Recursively calc every int item
    , CalDiv as
    (
        SELECT partid, qtyordered, divisions, 
               part = qtyordered / divisions, rest = qtyordered - (qtyordered/divisions)
        FROM   AddDiv WHERE divisions = @tts
        UNION ALL
        SELECT ad.partid, ad.qtyordered, ad.divisions,
               part = rest / ad.divisions, rest = rest - (rest / ad.divisions) 
        FROM   AddDiv ad 
        INNER JOIN CalDiv cd 
        ON ad.partid = cd.partid
        WHERE ad.divisions = cd.divisions - 1
    )
    SELECT * 
    INTO   Results
    FROM   CalDiv;

And now the dynamic PIVOT:

DECLARE @col AS nvarchar(MAX),
        @cmd AS nvarchar(MAX);

SET @col = STUFF((SELECT distinct ',' + QUOTENAME(divisions) 
            FROM Results
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,1,'')

SET @cmd =   'SELECT partid, qtyordered, ' + @col
           + ' FROM'  
           + ' (SELECT partid, qtyordered, divisions, part'
           + ' FROM Results) src'
           + ' PIVOT (MAX(part) FOR divisions IN (' + @col + ')) pvt';
    
EXECUTE (@cmd);

And this is the final result:

partid | qtyordered | 1  | 2  | 3  | 4  | 5 
:----- | ---------: | :- | :- | :- | :- | :-
NN300  |          3 | 1  | 1  | 1  | 0  | 0 
OO400  |          5 | 1  | 1  | 1  | 1  | 1 
SS100  |         10 | 2  | 2  | 2  | 2  | 2 
RR200  |         15 | 3  | 3  | 3  | 3  | 3

dbfiddle here

This is my first answer, It uses a function to build a comma delimited string with all int items.

Create Table #Orders 
(
    id int IDENTITY(1,1) PRIMARY KEY NOT NULL
    ,partid varchar(100) NOT NULL
    ,qtyordered int DEFAULT '0'
    ,dividedBy int DEFAULT 1
    ,orderedby varchar(100) NOT NULL
    ,ordereddate date DEFAULT GETDATE()
) ;

Insert Into #Orders (partid, qtyordered, dividedBy, orderedby) VALUES
('SS100', 10, 3, 'James'), ('RR200', 15, 6, 'Bob'), ('NN300', 3, 2, 'Jake'), ('OO400', 5, 4, 'Blue') ;
GO

CREATE FUNCTION fnCF(@Qty decimal, @TTS int)
RETURNS varchar(1024)
AS
BEGIN
    DECLARE @step int = 0;
    DECLARE @sv varchar(1024) = '';
    DECLARE @Q0 int;
    DECLARE @Q1 decimal;
    
    SET @step = @TTS
    SET @Q1 = @Qty

    WHILE  @step > 0
    BEGIN
        SET @Q0 = @Q1 / @step;
        SELECT @sv = @sv + CAST(@Q0 AS varchar(100)) 
        SET @step = @step -1
        SET @Q1 = @Q1 - @Q0
        IF @step > 0 
        SET @sv = @sv + ', '
    END
    
    return @sv;
END

GO

✓

SELECT 
   partid,
   qtyordered,
   dividedBy,
   dbo.fnCF(qtyordered, dividedBy) divInt
FROM 
    #Orders;
GO

partid | qtyordered | dividedBy | divInt          
:----- | ---------: | --------: | :---------------
SS100  |         10 |         3 | 3, 3, 4         
RR200  |         15 |         6 | 2, 2, 2, 3, 3, 3
NN300  |          3 |         2 | 1, 2            
OO400  |          5 |         4 | 1, 1, 1, 2

dbfiddle here

Related Question