October 26, 2018 at 11:11 am
I have a table of product lots that looks like this:
lot_no | exp_dt | qty
002133191 | 20281203 | 0
2161411 | 20311015 | 20
I need to create a number of rows from that table based on a pack size value that i supply and sorted by the exp_date. Say the pack size is 5, the result would look like this:
row_no | lot_no | exp_dt | qty
1 | 002133191 | 20281203 | 0
2 | 2161411 | 20311015 | 5
3 | 2161411 | 20311015 | 5
4 | 2161411 | 20311015 | 5
5 | 2161411 | 20311015 | 5
October 26, 2018 at 12:06 pm
Jackie Lowery - Friday, October 26, 2018 11:11 AMI have a table of product lots that looks like this:lot_no | exp_dt | qty
002133191 | 20281203 | 0
2161411 | 20311015 | 20I need to create a number of rows from that table based on a pack size value that i supply and sorted by the exp_date. Say the pack size is 5, the result would look like this:
row_no | lot_no | exp_dt | qty
1 | 002133191 | 20281203 | 0
2 | 2161411 | 20311015 | 5
3 | 2161411 | 20311015 | 5
4 | 2161411 | 20311015 | 5
5 | 2161411 | 20311015 | 5
DECLARE @PackSize int =5
;WITH myTable as (
SELECT *
FROM (VALUES ('002133191','20281203',0),('2161411','20311015',20)) T(lot_no,exp_dt,qty))
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) row_no, t.lot_no, t.exp_dt, ISNULL(x.qty,0) qty
FROM myTable t
OUTER APPLY (SELECT TOP((t.qty+@PackSize-1)/@PackSize) @PackSize qty FROM syscolumns) X
ORDER BY t.exp_dt
October 26, 2018 at 12:37 pm
Jonathan AC Roberts - Friday, October 26, 2018 12:06 PMJackie Lowery - Friday, October 26, 2018 11:11 AMI have a table of product lots that looks like this:lot_no | exp_dt | qty
002133191 | 20281203 | 0
2161411 | 20311015 | 20I need to create a number of rows from that table based on a pack size value that i supply and sorted by the exp_date. Say the pack size is 5, the result would look like this:
row_no | lot_no | exp_dt | qty
1 | 002133191 | 20281203 | 0
2 | 2161411 | 20311015 | 5
3 | 2161411 | 20311015 | 5
4 | 2161411 | 20311015 | 5
5 | 2161411 | 20311015 | 5
DECLARE @PackSize int =5
;WITH myTable as (
SELECT *
FROM (VALUES ('002133191','20281203',0),('2161411','20311015',20)) T(lot_no,exp_dt,qty))SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) row_no, t.lot_no, t.exp_dt, ISNULL(x.qty,0) qty
FROM myTable t
OUTER APPLY (SELECT TOP((t.qty+@PackSize-1)/@PackSize) @PackSize qty FROM syscolumns) X
ORDER BY t.exp_dt
Ah. That syscolumns trick is nice. 😎
October 26, 2018 at 3:26 pm
Jackie Lowery - Friday, October 26, 2018 11:11 AMI have a table of product lots that looks like this:lot_no | exp_dt | qty
002133191 | 20281203 | 0
2161411 | 20311015 | 20I need to create a number of rows from that table based on a pack size value that i supply and sorted by the exp_date. Say the pack size is 5, the result would look like this:
row_no | lot_no | exp_dt | qty
1 | 002133191 | 20281203 | 0
2 | 2161411 | 20311015 | 5
3 | 2161411 | 20311015 | 5
4 | 2161411 | 20311015 | 5
5 | 2161411 | 20311015 | 5
>> I have a table of product lots that looks like this: <<
If this is a table, then where is the DDL for it? You do understand that in order to be a table it must have a key. This is not an option. This is the definition. We also have no idea what to do when the quantity on hand is not an even multiple of the pack size. Do we throw away the leftovers or do we create a short package? How do we tell the difference between the packages that were split out from the lots? Row number will not do this. It's a definite attribute of each package not a property of the physical storage. Why do you think you need to create a new row for each of the packages? What makes each of them unique and different, so they have to go on a separate row?
You might also want to look at the N-TILE() function.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 27, 2018 at 1:06 am
Jackie Lowery - Friday, October 26, 2018 11:11 AMI have a table of product lots that looks like this:lot_no | exp_dt | qty
002133191 | 20281203 | 0
2161411 | 20311015 | 20I need to create a number of rows from that table based on a pack size value that i supply and sorted by the exp_date. Say the pack size is 5, the result would look like this:
row_no | lot_no | exp_dt | qty
1 | 002133191 | 20281203 | 0
2 | 2161411 | 20311015 | 5
3 | 2161411 | 20311015 | 5
4 | 2161411 | 20311015 | 5
5 | 2161411 | 20311015 | 5
Simple math will do the trick here.
😎
The assumption is that the last segment contains the remainder.
USE TEEST;
GO
SET NOCOUNT ON;
-- https://qa.sqlservercentral.com/Forums/2006659/Splitting-value-into-multiple-rows-based-on-size-variable
DECLARE @PackSize int = 5;
;WITH myTable as (
SELECT *
FROM (VALUES ('002133191','20281203',6),('2161411','20311015',21)) T(lot_no,exp_dt,qty))
,NUMS(N) AS (SELECT X.N FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10))X(N))
SELECT
MT.lot_no
,MT.exp_dt
,MT.qty
,(MT.qty / CONVERT(FLOAT,@PackSize,0)) AS LOT_COUNT
,CASE
WHEN ((NX.N * @PackSize) * SIGN(MT.qty)) <= MT.qty THEN ((@PackSize) * SIGN(MT.qty))
ELSE MT.qty - ((NX.N - 1) * @PackSize)
END AS PACK_QTY
FROM myTable MT
CROSS APPLY
(
SELECT TOP(1 + CONVERT(INT,FLOOR((MT.qty / CONVERT(FLOAT,@PackSize,0))),0)) NM.N
FROM NUMS NM
) NX;
Output
lot_no exp_dt qty LOT_COUNT PACK_QTY
--------- -------- ---- ---------- -----------
002133191 20281203 6 1.2 5
002133191 20281203 6 1.2 1
2161411 20311015 21 4.2 5
2161411 20311015 21 4.2 5
2161411 20311015 21 4.2 5
2161411 20311015 21 4.2 5
2161411 20311015 21 4.2 1
October 27, 2018 at 8:29 am
jcelko212 32090 - Friday, October 26, 2018 3:26 PMJackie Lowery - Friday, October 26, 2018 11:11 AMI have a table of product lots that looks like this:lot_no | exp_dt | qty
002133191 | 20281203 | 0
2161411 | 20311015 | 20I need to create a number of rows from that table based on a pack size value that i supply and sorted by the exp_date. Say the pack size is 5, the result would look like this:
row_no | lot_no | exp_dt | qty
1 | 002133191 | 20281203 | 0
2 | 2161411 | 20311015 | 5
3 | 2161411 | 20311015 | 5
4 | 2161411 | 20311015 | 5
5 | 2161411 | 20311015 | 5>> I have a table of product lots that looks like this: <<
If this is a table, then where is the DDL for it? You do understand that in order to be a table it must have a key. This is not an option. This is the definition. We also have no idea what to do when the quantity on hand is not an even multiple of the pack size. Do we throw away the leftovers or do we create a short package? How do we tell the difference between the packages that were split out from the lots? Row number will not do this. It's a definite attribute of each package not a property of the physical storage. Why do you think you need to create a new row for each of the packages? What makes each of them unique and different, so they have to go on a separate row?
You might also want to look at the N-TILE() function.
To answer most of your questions, please see the solution post just before yours. Also, if you think N-TILE() would be better to achieve the required relational multiplication, please post your working coded solution because I'm not seeing how you could use it for such a thing in this case.
At the point of where you posted in this thread, I do agree that it wasn't defined nor demonstrated what to do for "odd lots" where the qty isn't evenly divisible by the pack size and that question still remains. My suspicion would be to create multiple full pack sizes with a single short pack containing the remainder result of integer division but that suspicion would only be an assumption. I agree that the answer to that question still needs to be clearly defined as a requirement.
--Jeff Moden
October 27, 2018 at 9:27 am
Jeff Moden - Saturday, October 27, 2018 8:29 AMjcelko212 32090 - Friday, October 26, 2018 3:26 PMJackie Lowery - Friday, October 26, 2018 11:11 AMI have a table of product lots that looks like this:lot_no | exp_dt | qty
002133191 | 20281203 | 0
2161411 | 20311015 | 20I need to create a number of rows from that table based on a pack size value that i supply and sorted by the exp_date. Say the pack size is 5, the result would look like this:
row_no | lot_no | exp_dt | qty
1 | 002133191 | 20281203 | 0
2 | 2161411 | 20311015 | 5
3 | 2161411 | 20311015 | 5
4 | 2161411 | 20311015 | 5
5 | 2161411 | 20311015 | 5>> I have a table of product lots that looks like this: <<
If this is a table, then where is the DDL for it? You do understand that in order to be a table it must have a key. This is not an option. This is the definition. We also have no idea what to do when the quantity on hand is not an even multiple of the pack size. Do we throw away the leftovers or do we create a short package? How do we tell the difference between the packages that were split out from the lots? Row number will not do this. It's a definite attribute of each package not a property of the physical storage. Why do you think you need to create a new row for each of the packages? What makes each of them unique and different, so they have to go on a separate row?
You might also want to look at the N-TILE() function.
To answer most of your questions, please see the solution post just before yours. Also, if you think N-TILE() would be better to achieve the required relational multiplication, please post your working coded solution because I'm not seeing how you could use it for such a thing in this case.
At the point of where you posted in this thread, I do agree that it wasn't defined nor demonstrated what to do for "odd lots" where the qty isn't evenly divisible by the pack size and that question still remains. My suspicion would be to create multiple full pack sizes with a single short pack containing the remainder result of integer division but that suspicion would only be an assumption. I agree that the answer to that question still needs to be clearly defined as a requirement.
He he, theoretically ajar😀 Approaching a problem like this one, one has to state the assumptions upfront as there are many variants, which will affect the answer. The "production line bucket filling problem" that has a fixed size buckets is simple, just as the math I posted, unless the OP defines the the requirements further, that's my input.
😎
Jeff, JC has his hammer, it's called ANSI/ISO, not every problem would be a ANSi/ISO nail, JC has to realize that😛
October 27, 2018 at 10:12 am
Eirikur Eiriksson - Saturday, October 27, 2018 9:27 AMHe he, theoretically ajar😀 Approaching a problem like this one, one has to state the assumptions upfront as there are many variants, which will affect the answer. The "production line bucket filling problem" that has a fixed size buckets is simple, just as the math I posted, unless the OP defines the the requirements further, that's my input.
😎Jeff, JC has his hammer, it's called ANSI/ISO, not every problem would be a ANSi/ISO nail, JC has to realize that😛
Thanks for all the great input guys. We don't pack the remainder as we only pack full cases. I ended up using the round() function using the third argument to round off the remainder. So, the TOP solution has worked great. Sorry I didn't stick to the ANSi/ISO params with my question, but I didn't really need a fully detailed answer, just a way to break the ser_lots into blocks of the pack sizes, which TOP did just fine. Thanks again everyone.
October 27, 2018 at 12:28 pm
Eirikur Eiriksson - Saturday, October 27, 2018 9:27 AMJeff Moden - Saturday, October 27, 2018 8:29 AMjcelko212 32090 - Friday, October 26, 2018 3:26 PMJackie Lowery - Friday, October 26, 2018 11:11 AMI have a table of product lots that looks like this:lot_no | exp_dt | qty
002133191 | 20281203 | 0
2161411 | 20311015 | 20I need to create a number of rows from that table based on a pack size value that i supply and sorted by the exp_date. Say the pack size is 5, the result would look like this:
row_no | lot_no | exp_dt | qty
1 | 002133191 | 20281203 | 0
2 | 2161411 | 20311015 | 5
3 | 2161411 | 20311015 | 5
4 | 2161411 | 20311015 | 5
5 | 2161411 | 20311015 | 5>> I have a table of product lots that looks like this: <<
If this is a table, then where is the DDL for it? You do understand that in order to be a table it must have a key. This is not an option. This is the definition. We also have no idea what to do when the quantity on hand is not an even multiple of the pack size. Do we throw away the leftovers or do we create a short package? How do we tell the difference between the packages that were split out from the lots? Row number will not do this. It's a definite attribute of each package not a property of the physical storage. Why do you think you need to create a new row for each of the packages? What makes each of them unique and different, so they have to go on a separate row?
You might also want to look at the N-TILE() function.
To answer most of your questions, please see the solution post just before yours. Also, if you think N-TILE() would be better to achieve the required relational multiplication, please post your working coded solution because I'm not seeing how you could use it for such a thing in this case.
At the point of where you posted in this thread, I do agree that it wasn't defined nor demonstrated what to do for "odd lots" where the qty isn't evenly divisible by the pack size and that question still remains. My suspicion would be to create multiple full pack sizes with a single short pack containing the remainder result of integer division but that suspicion would only be an assumption. I agree that the answer to that question still needs to be clearly defined as a requirement.
He he, theoretically ajar😀 Approaching a problem like this one, one has to state the assumptions upfront as there are many variants, which will affect the answer. The "production line bucket filling problem" that has a fixed size buckets is simple, just as the math I posted, unless the OP defines the the requirements further, that's my input.
😎Jeff, JC has his hammer, it's called ANSI/ISO, not every problem would be a ANSi/ISO nail, JC has to realize that😛
Heh.... understood and he practices it well... until it no longer suits him. 😀 I also wonder why he thinks that N-Tile will solve a relational multiplication problem.
--Jeff Moden
October 27, 2018 at 12:40 pm
Jeff Moden - Saturday, October 27, 2018 12:28 PMEirikur Eiriksson - Saturday, October 27, 2018 9:27 AMJeff Moden - Saturday, October 27, 2018 8:29 AMjcelko212 32090 - Friday, October 26, 2018 3:26 PMJackie Lowery - Friday, October 26, 2018 11:11 AMI have a table of product lots that looks like this:lot_no | exp_dt | qty
002133191 | 20281203 | 0
2161411 | 20311015 | 20I need to create a number of rows from that table based on a pack size value that i supply and sorted by the exp_date. Say the pack size is 5, the result would look like this:
row_no | lot_no | exp_dt | qty
1 | 002133191 | 20281203 | 0
2 | 2161411 | 20311015 | 5
3 | 2161411 | 20311015 | 5
4 | 2161411 | 20311015 | 5
5 | 2161411 | 20311015 | 5>> I have a table of product lots that looks like this: <<
If this is a table, then where is the DDL for it? You do understand that in order to be a table it must have a key. This is not an option. This is the definition. We also have no idea what to do when the quantity on hand is not an even multiple of the pack size. Do we throw away the leftovers or do we create a short package? How do we tell the difference between the packages that were split out from the lots? Row number will not do this. It's a definite attribute of each package not a property of the physical storage. Why do you think you need to create a new row for each of the packages? What makes each of them unique and different, so they have to go on a separate row?
You might also want to look at the N-TILE() function.
To answer most of your questions, please see the solution post just before yours. Also, if you think N-TILE() would be better to achieve the required relational multiplication, please post your working coded solution because I'm not seeing how you could use it for such a thing in this case.
At the point of where you posted in this thread, I do agree that it wasn't defined nor demonstrated what to do for "odd lots" where the qty isn't evenly divisible by the pack size and that question still remains. My suspicion would be to create multiple full pack sizes with a single short pack containing the remainder result of integer division but that suspicion would only be an assumption. I agree that the answer to that question still needs to be clearly defined as a requirement.
He he, theoretically ajar😀 Approaching a problem like this one, one has to state the assumptions upfront as there are many variants, which will affect the answer. The "production line bucket filling problem" that has a fixed size buckets is simple, just as the math I posted, unless the OP defines the the requirements further, that's my input.
😎Jeff, JC has his hammer, it's called ANSI/ISO, not every problem would be a ANSi/ISO nail, JC has to realize that😛
Heh.... understood and he practices it well... until it no longer suits him. 😀 I also wonder why he thinks that N-Tile will solve a relational multiplication problem.
Should have read his post "better" thought he said n-wit 😛
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply