Function To Perform Calculations Based Off Variable

Question

I saw an answer by @ypercubeTM 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?

Answer 1

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

Answer 2

Basically, we're just looking to spread the remainder from the division out over the values. This procedure does exactly that; it will return a table with the result vals from the split. I assume we do not want to return a variable number of columns (depending on the divisors we receive), and I decided that actually generating the text "First", "Second", etc. wasn't really a part of the question, so I'm returning each value in its own row.

CREATE PROCEDURE split_into_ints(@dividend int, @divisor int)
AS
BEGIN
 WITH result_rows AS
 (SELECT TOP (@divisor) ROW_NUMBER() OVER (ORDER BY o1.object_id)
 as result_num
 FROM sys.objects o1 CROSS JOIN sys.objects o2
 )
 ,base_vals AS
 (SELECT @dividend / @divisor as base_result
 ,@dividend % @divisor as remainder
 )
 SELECT r.result_num
 ,CASE WHEN r.result_num <= remainder THEN 1 ELSE 0 END
 + v.base_result as result_val
 FROM base_vals v CROSS JOIN result_rows r
 ORDER BY result_num
END;
GO

The following:

EXECUTE split_into_ints 10,3;
EXECUTE split_into_ints 49,6;
EXECUTE split_into_ints 2000,11;

Yields these results:

result_num result_val
-------------------- -----------
1 4
2 3
3 3
result_num result_val
-------------------- -----------
1 9
2 8
3 8
4 8
5 8
6 8
result_num result_val
-------------------- -----------
1 182
2 182
3 182
4 182
5 182
6 182
7 182
8 182
9 182
10 181
11 181

Alternately, you could make this an inline table-valued function:

CREATE FUNCTION fn_split_into_ints(@dividend int, @divisor int)
RETURNS TABLE
AS 
RETURN
(
 WITH result_rows AS
 (SELECT TOP (@divisor) ROW_NUMBER() OVER (ORDER BY o1.object_id)
 as result_num
 FROM sys.objects o1 CROSS JOIN sys.objects o2
 )
 ,base_vals AS
 (SELECT @dividend / @divisor as base_result
 ,@dividend % @divisor as remainder
 )
 SELECT r.result_num
 ,CASE WHEN r.result_num <= remainder THEN 1 ELSE 0 END
 + v.base_result as result_val
 FROM base_vals v CROSS JOIN result_rows r
);
GO

You run the same queries as above thusly:

SELECT * FROM fn_split_into_ints(10,3);
SELECT * FROM fn_split_into_ints(49,6);
SELECT * FROM fn_split_into_ints(2000,11);

Results are the same. To answer one comment: this would allow you to pull result_val into a variable:

SELECT @result_val = result_val
 FROM fn_split_into_ints(49,6)
 WHERE result_num = 6
;

EDIT: Changed function name, so function and procedure can co-exist (if you'd want them to, for some reason).