December 13, 2016 at 12:55 am
Hello All,
I am facing problem while spliting a number in closest equality. say i want to divide 11 into 4 groups then output shd be 3,3,3,2. same way 10 for for group would be 3,3,2,2. I tried it with function and i called this function in my select statement with help of cross apply but looks like not working. for first records its working but for rest records its not working. please see my code-
create function dbo.fn_getShopsAllocation(@Shops int,@WBNmber int)
Returns @Tmp TABLE (Id int identity , value int NOT NULL)
AS
BEGIN
declare @i int,@Reminder int
set @i=1
while @i<=@Shops
Begin
insert into @Tmp values (convert(decimal,@WBNmber)/@Shops)
set @i=@i+1
end
set @Reminder= @WBNmber%@Shops
update @Tmp set value=value+1 where Id<=@Reminder
return;
END
i want the following output
ACWeekShopsTotal Salesexpected output
AW14113,3,3,2
Aw24103,3,2,2
BW13114,4,3
Bw23166,5,5
thanks everyone for your help
December 13, 2016 at 2:17 am
Your function does what you expect it to do, so if you are seeing wrong results somewhere, it is being caused by how you are calling it.
Try selecting from the function directly with some of your values and you will see it does return what you want.
December 13, 2016 at 3:25 am
yes i know its because of calling the function. but is there any other way to call this function with help of join like cross join or any alternate solution. if i call it with a value its working fine but in that case i will have to call it in a loop or in cursor. any other ideas?
thanks
December 13, 2016 at 3:55 am
No need for a loop in your function. This will work for any number of weeks up to 10.DECLARE @weeks tinyint;
DECLARE @no int;
SET @no = 16;
SET @weeks = 3;
WITH Numbers AS (
SELECT nbr FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(nbr)
)
, UpperandLower AS (
SELECT
@no/@weeks AS MyLower
,@no/@weeks + 1 AS MyUpper
)
SELECT
n.nbr + 1 AS WeekNo
,CASE
WHEN n.nbr < @no - u.MyLower * @weeks THEN u.MyLower + 1
ELSE u.MyLower
END AS Share
FROM Numbers n
CROSS JOIN UpperandLower u
WHERE n.nbr < @weeks;
John
December 13, 2016 at 4:51 am
You have posted the part of your problem that works, not the part that doesn't work, so it is impossible to help you.
If you post the code you are using to call the function, maybe someone will be able to help.
December 13, 2016 at 7:33 am
There's no need to create more rows, just do the math and replicate numbers.
DECLARE @Sample TABLE(
AC char(1),
Week varchar(3),
Shops int,
TotalSales int
)
INSERT INTO @Sample
VALUES
( 'A', 'W1', 4, 12),
( 'A', 'W1', 4, 11),
( 'A', 'W2', 4, 10),
( 'B', 'W1', 3, 11),
( 'B', 'W2', 3, 16),
( 'B', 'W2', 3, 15);
SELECT *,
STUFF( ISNULL( REPLICATE( ',' + CAST((TotalSales / Shops) + 1 AS varchar(2)), TotalSales % Shops), '')
+ REPLICATE( ',' + CAST((TotalSales / Shops) AS varchar(2)), Shops - (TotalSales % Shops)), 1, 1, '') Value
FROM @Sample;
And if you want to make it simpler by using a function (never use multi statement table-valued functions) here's an option.
CREATE FUNCTION dbo.Ifn_getShopsAllocation(@Shops int,@WBNmber int)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN SELECT STUFF( ISNULL( REPLICATE( ',' + CAST((@WBNmber / @Shops) + 1 AS varchar(2)), @WBNmber % @Shops), '')
+ REPLICATE( ',' + CAST((@WBNmber / @Shops) AS varchar(2)), @Shops - (@WBNmber % @Shops)), 1, 1, '') AS value
GO
DECLARE @Sample TABLE(
AC char(1),
Week varchar(3),
Shops int,
TotalSales int
)
INSERT INTO @Sample
VALUES
( 'A', 'W1', 4, 12),
( 'A', 'W1', 4, 11),
( 'A', 'W2', 4, 10),
( 'B', 'W1', 3, 11),
( 'B', 'W2', 3, 16),
( 'B', 'W2', 3, 15);
SELECT S.*,
sa.value
FROM @Sample S
CROSS APPLY Ifn_getShopsAllocation(Shops, TotalSales) sa;
I'm including the sample data for ease of testing, as you should have done.
Let me know if you have any questions.
December 13, 2016 at 8:28 am
Thanks Louis, I was looking for this. is it possible to bring value column in 4 row like this-
ShopWeekValue
AW13
AW13
AW13
AW12
thanks for your help
December 13, 2016 at 8:37 am
Now, that's different. For this you actually need to create rows and a tally table is extremely helpful for this.
Here's a function that uses a cteTally which has zero reads and will create the rows on the fly as needed. Once understanding how the cteTally is created, the rest of the code is easier.
CREATE FUNCTION dbo.Ifn_getShopsAllocation(@Shops int,@WBNmber int)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(@Shops) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT CASE WHEN n <= (@WBNmber % @Shops)
THEN (@WBNmber / @Shops) + 1
ELSE (@WBNmber / @Shops) END AS value
FROM cteTally;
GO
December 13, 2016 at 8:41 am
Thank you so much Luis for your quick reply. I will try it and will let you know.
thanks again
December 13, 2016 at 8:45 am
snsingh (12/13/2016)
Thank you so much Luis for your quick reply. I will try it and will let you know.thanks again
Be sure to understand how it works and ask any questions you might have.
Here's an article that would help you to understand tally tables: http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/
December 14, 2016 at 1:24 pm
I want to divide 11 into 4 groups then output should be 3,3,3,2. same way 10 for for group would be 3,3,2,2. ..
Look up the NTILE() function.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
December 14, 2016 at 2:57 pm
CELKO (12/14/2016)
I want to divide 11 into 4 groups then output should be 3,3,3,2. same way 10 for for group would be 3,3,2,2. ..
Look up the NTILE() function.
That would imply additional work on the server.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply