May 29, 2013 at 11:09 am
Hi all,
I have a question I hope the community can help me with. I have a query with count function and the result is 97, I need divide the result like this:
Quant name
30cccccc
30 cccccc
30 cccccc
7 cccccc
May 29, 2013 at 11:13 am
I don't know what you want to divide by. Do you want to total the Quant column and then divide by a count of rows to get an average?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 29, 2013 at 11:18 am
I want divide de result of query by 30. if the result is
Quant name
97 cccccc
I want divide like this:
Quant name
30 cccccc
30 cccccc
30 cccccc
7 cccccc
Thanks
May 29, 2013 at 12:04 pm
Okay, so you want to divide a single row into multiple rows based on the max quantity a single row should contain, 30 in your case. That's an interesting problem to try to solve with T-SQL.
Something like this works, although there is probably a better way.
DECLARE @test-2 TABLE
(
quantity INT,
item CHAR(1)
)
DECLARE @quanityPerRow INT = 5;
INSERT INTO @test-2
(quantity, item)
VALUES
(97, -- quantity - int
'c' -- item - char(1)
),
(140, 'd');
WITH rowsDesired
AS (
SELECT
CONVERT(INT, CEILING(quantity / @quanityPerRow)) AS rowsDesired,
item,
quantity
FROM
),
nums
AS (
SELECT TOP 1000
ROW_NUMBER() OVER (ORDER BY AO.OBJECT_ID) AS N
FROM
sys.all_objects AS AO
CROSS JOIN sys.all_objects AS AO2
)
SELECT
CASE WHEN R.quantity - (@quanityPerRow * N.N) >= @quanityPerRow OR
R.quantity - (@quanityPerRow * N.N) <= 0 THEN @quanityPerRow
ELSE R.quantity - (@quanityPerRow * n.n)
END AS newQuantity,
item
FROM
rowsDesired AS R
CROSS JOIN nums AS N
WHERE
R.rowsDesired > = N.N
ORDER BY
item,
newQuantity DESC;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 29, 2013 at 12:37 pm
There's a small mistake on Jack's code. A simple tweak on line 19 will correct it.
--Corrected line
CONVERT(INT, CEILING(quantity / CAST( @quanityPerRow AS decimal(10,2)))) AS rowsDesired,
May 29, 2013 at 12:42 pm
Luis Cazares (5/29/2013)
There's a small mistake on Jack's code. A simple tweak on line 19 will correct it.
--Corrected line
CONVERT(INT, CEILING(quantity / CAST( @quanityPerRow AS decimal(10,2)))) AS rowsDesired,
Wait, I made I mistake :crazy:
Thanks for correcting it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 29, 2013 at 12:59 pm
Jack Corbett (5/29/2013)
Luis Cazares (5/29/2013)
There's a small mistake on Jack's code. A simple tweak on line 19 will correct it.
--Corrected line
CONVERT(INT, CEILING(quantity / CAST( @quanityPerRow AS decimal(10,2)))) AS rowsDesired,
Wait, I made I mistake :crazy:
Thanks for correcting it.
This sounded like a fun one so I took a shot at it too. I noticed the same integer issue but I just multiplied by 1.0 instead of an extra cast.
CONVERT(INT, CEILING(quantity * 1.0 / @quanityPerRow)) AS rowsDesired
I came up with a very similar query. At first glance the red flag goes up because it is so obviously a triangular join. However that is basically what Jack's code is doing also. I think in this case a triangular join is actually ok???
select Item,
case when N * @quanityPerRow <= quantity then @quanityPerRow else quantity % @quanityPerRow end as newQuantity
from @test-2 t
join nums n on n.N <= ceiling(quantity * 1.0 / @quanityPerRow);
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2013 at 1:15 pm
Sean,
I think your choices to produce N rows from 1 row are some type of explicit loop or a triangular join. I think the triangular join will outperform an explicit loop in most cases, especially as you get into larger datasets. For getting 4 rows out 1 you could probably do a loop and be okay, but how long is it going to be that small a dataset?
Oh, and I like your query better than mine.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 29, 2013 at 1:23 pm
Jack Corbett (5/29/2013)
Sean,I think your choices to produce N rows from 1 row are some type of explicit loop or a triangular join. I think the triangular join will outperform an explicit loop in most cases, especially as you get into larger datasets. For getting 4 rows out 1 you could probably do a loop and be okay, but how long is it going to be that small a dataset?
Oh, and I like your query better than mine.
It is definitely a triangular join. Although yours is basically the same thing because you first do a cross join then reduce that to the same rows as my explicit triangular join. I say that since a triangular join is just a portion of a cross join. As the dataset grows it may very well perform far worse than a loop or even a cursor according to Jeff's testing for his triangular join article.
http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]
I think in this case a triangular join is exactly what is needed though. In both cases we were careful not to do any of the really nasty stuff that can cause them to melt cpu's into a puddle on the inside of the case. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 1, 2013 at 11:19 am
Hi
Only now had time to test the code. Is working.
Thanks to all
🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply