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;
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?