# Function To Perform Calculations Based Off Variable

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?

UPDATE

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

``````Declare @TTS int = 5

(
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
part = rest / ad.divisions, rest = rest - (rest / ad.divisions)
INNER JOIN CalDiv cd
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

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).

• Will you provide sample on how to call procedure? – user2676140 Apr 11 '17 at 16:03
• As requested.... – RDFozz Apr 11 '17 at 16:54
• @RDFozz - you could pivot the results if you wanted to return a single row. – Max Vernon Apr 11 '17 at 16:58
• You can't use a procedure that way; you'd either have to do a `SELECT ... EXECUTE`, or change the procedure to a function. I have now added the function version to my answer. Note that the arguments must be in parentheses for a function. – RDFozz Apr 11 '17 at 18:18
• @MaxVernon - A pivot is probably the simplest way to switch to a single row of results. As I noted, in practical terms, as a procedure (or a function), I think it's best to return something consistent, which is why I didn't push it farther; I don't know how someone would actually plan on using it.... – RDFozz Apr 11 '17 at 18:20