3

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?

3

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

5

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
  • 2
    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
  • 2
    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
  • 1
    @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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.