Attie Wagner
Reputation: 1362
Populating rows based on the value in that row
I need to generate Serial Numbers for items currently in a batch.
I generated the serial numbers using the following code:
--insert into SerialMF (
-- SerialNumber
--, SNStockLink
--, SNDateLMove
--, CurrentLoc
--, CurrentAccLink
--, iSNLotID
--, iSNMFPID
--, iSNMFPLineID
--, SerialMF_iBranchID
--)
select
'StockOut'+cast(iStockID as varchar)+'_'+right(replicate('0',3)+cast(rank() over (partition by iStockID order by iStockID) as varchar),3) SerialNumber
, iStockID -- SNStockLink
, getdate() -- SNDateLMove
, 1 -- CurrentLoc
, iWarehouseID -- CurrentAccLink
, 0 -- iSNLotID
, 0 -- iSNMFPID
, 0 -- iSNMFPLineID
, _etblInvJrBatchLines_iBranchID -- SerialMF_iBranchID
, fQtyOut
from
(
select * from _etblInvJrBatchLines
where iInvJrBatchID = 1 and bIsSerialItem = 1
)b
left outer join
(
select * from SerialMF
)s on b.iStockID = s.SNStockLink
where SNStockLink is null
From the above, it generates the Serial Numbers correctly, however I realized that it did not generate the Serial Numbers to the amount of the actual quantity of that item.
I need Serial Numbers generated for each quantity of that particular item, however the Serial Number needs to be different for each of the quantities.
The current rows returned in the database is 1865, however the following sample data is for your reference and assistance (100 rows):
SerialNumber iStockID getdate() (No column name) iWarehouseID (No column name) (No column name) (No column name) _etblInvJrBatchLines_iBranchID fQtyOut
StockOut63041_001 63041 18/12/2018 1 479 0 0 0 0 2
StockOut63042_001 63042 18/12/2018 1 479 0 0 0 0 1
StockOut63043_001 63043 18/12/2018 1 479 0 0 0 0 1
StockOut63044_001 63044 18/12/2018 1 479 0 0 0 0 2
StockOut63045_001 63045 18/12/2018 1 479 0 0 0 0 2
StockOut63046_001 63046 18/12/2018 1 479 0 0 0 0 1
StockOut63047_001 63047 18/12/2018 1 479 0 0 0 0 1
StockOut63048_001 63048 18/12/2018 1 479 0 0 0 0 1
StockOut63049_001 63049 18/12/2018 1 479 0 0 0 0 1
StockOut63050_001 63050 18/12/2018 1 479 0 0 0 0 3
StockOut63051_001 63051 18/12/2018 1 479 0 0 0 0 1
StockOut63052_001 63052 18/12/2018 1 479 0 0 0 0 6
StockOut63053_001 63053 18/12/2018 1 479 0 0 0 0 1
StockOut63054_001 63054 18/12/2018 1 479 0 0 0 0 3
StockOut63055_001 63055 18/12/2018 1 479 0 0 0 0 1
StockOut63056_001 63056 18/12/2018 1 479 0 0 0 0 9
StockOut63057_001 63057 18/12/2018 1 479 0 0 0 0 4
StockOut63058_001 63058 18/12/2018 1 479 0 0 0 0 2
StockOut63059_001 63059 18/12/2018 1 479 0 0 0 0 1
StockOut63060_001 63060 18/12/2018 1 479 0 0 0 0 3
StockOut63061_001 63061 18/12/2018 1 479 0 0 0 0 1
StockOut63062_001 63062 18/12/2018 1 479 0 0 0 0 2
StockOut63063_001 63063 18/12/2018 1 479 0 0 0 0 1
StockOut63064_001 63064 18/12/2018 1 479 0 0 0 0 2
StockOut63065_001 63065 18/12/2018 1 479 0 0 0 0 1
StockOut63066_001 63066 18/12/2018 1 479 0 0 0 0 1
StockOut63067_001 63067 18/12/2018 1 479 0 0 0 0 6
StockOut63068_001 63068 18/12/2018 1 479 0 0 0 0 2
StockOut63069_001 63069 18/12/2018 1 479 0 0 0 0 3
StockOut63070_001 63070 18/12/2018 1 479 0 0 0 0 1
StockOut63071_001 63071 18/12/2018 1 479 0 0 0 0 2
StockOut63072_001 63072 18/12/2018 1 479 0 0 0 0 1
StockOut63073_001 63073 18/12/2018 1 479 0 0 0 0 2
StockOut63074_001 63074 18/12/2018 1 479 0 0 0 0 3
StockOut63075_001 63075 18/12/2018 1 479 0 0 0 0 5
StockOut63076_001 63076 18/12/2018 1 479 0 0 0 0 2
StockOut63077_001 63077 18/12/2018 1 479 0 0 0 0 5
StockOut63078_001 63078 18/12/2018 1 479 0 0 0 0 4
StockOut63079_001 63079 18/12/2018 1 479 0 0 0 0 1
StockOut63080_001 63080 18/12/2018 1 479 0 0 0 0 1
StockOut63081_001 63081 18/12/2018 1 479 0 0 0 0 1
StockOut63082_001 63082 18/12/2018 1 479 0 0 0 0 1
StockOut63083_001 63083 18/12/2018 1 479 0 0 0 0 3
StockOut63084_001 63084 18/12/2018 1 479 0 0 0 0 4
StockOut63085_001 63085 18/12/2018 1 479 0 0 0 0 1
StockOut63089_001 63089 18/12/2018 1 479 0 0 0 0 2
StockOut63090_001 63090 18/12/2018 1 479 0 0 0 0 3
StockOut63091_001 63091 18/12/2018 1 479 0 0 0 0 1
StockOut63092_001 63092 18/12/2018 1 479 0 0 0 0 1
StockOut63093_001 63093 18/12/2018 1 479 0 0 0 0 1
StockOut63094_001 63094 18/12/2018 1 479 0 0 0 0 1
StockOut63095_001 63095 18/12/2018 1 479 0 0 0 0 1
StockOut63096_001 63096 18/12/2018 1 479 0 0 0 0 1
StockOut63097_001 63097 18/12/2018 1 479 0 0 0 0 1
StockOut63098_001 63098 18/12/2018 1 479 0 0 0 0 3
StockOut63099_001 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63100_001 63100 18/12/2018 1 479 0 0 0 0 1
StockOut63101_001 63101 18/12/2018 1 479 0 0 0 0 4
StockOut63102_001 63102 18/12/2018 1 479 0 0 0 0 1
StockOut63104_001 63104 18/12/2018 1 479 0 0 0 0 2
StockOut63105_001 63105 18/12/2018 1 479 0 0 0 0 1
StockOut63106_001 63106 18/12/2018 1 479 0 0 0 0 1
StockOut63107_001 63107 18/12/2018 1 479 0 0 0 0 3
StockOut63108_001 63108 18/12/2018 1 479 0 0 0 0 7
StockOut63109_001 63109 18/12/2018 1 479 0 0 0 0 1
StockOut63110_001 63110 18/12/2018 1 479 0 0 0 0 1
StockOut63111_001 63111 18/12/2018 1 479 0 0 0 0 5
StockOut63112_001 63112 18/12/2018 1 479 0 0 0 0 1
StockOut63113_001 63113 18/12/2018 1 479 0 0 0 0 1
StockOut63114_001 63114 18/12/2018 1 479 0 0 0 0 1
StockOut63115_001 63115 18/12/2018 1 479 0 0 0 0 3
StockOut63116_001 63116 18/12/2018 1 479 0 0 0 0 1
StockOut63117_001 63117 18/12/2018 1 479 0 0 0 0 1
StockOut63118_001 63118 18/12/2018 1 479 0 0 0 0 1
StockOut63119_001 63119 18/12/2018 1 479 0 0 0 0 2
StockOut63120_001 63120 18/12/2018 1 479 0 0 0 0 1
StockOut63121_001 63121 18/12/2018 1 479 0 0 0 0 1
StockOut63122_001 63122 18/12/2018 1 479 0 0 0 0 1
StockOut63123_001 63123 18/12/2018 1 479 0 0 0 0 1
StockOut63124_001 63124 18/12/2018 1 479 0 0 0 0 1
StockOut63125_001 63125 18/12/2018 1 479 0 0 0 0 1
StockOut63126_001 63126 18/12/2018 1 479 0 0 0 0 1
StockOut63127_001 63127 18/12/2018 1 479 0 0 0 0 2
StockOut63128_001 63128 18/12/2018 1 479 0 0 0 0 2
StockOut63129_001 63129 18/12/2018 1 479 0 0 0 0 2
StockOut63130_001 63130 18/12/2018 1 479 0 0 0 0 1
StockOut63131_001 63131 18/12/2018 1 479 0 0 0 0 1
StockOut63132_001 63132 18/12/2018 1 479 0 0 0 0 3
StockOut63133_001 63133 18/12/2018 1 479 0 0 0 0 2
StockOut63134_001 63134 18/12/2018 1 479 0 0 0 0 1
StockOut63135_001 63135 18/12/2018 1 479 0 0 0 0 1
StockOut63136_001 63136 18/12/2018 1 479 0 0 0 0 1
StockOut63137_001 63137 18/12/2018 1 479 0 0 0 0 1
StockOut63138_001 63138 18/12/2018 1 479 0 0 0 0 1
StockOut63140_001 63140 18/12/2018 1 479 0 0 0 0 1
StockOut63141_001 63141 18/12/2018 1 479 0 0 0 0 1
StockOut63142_001 63142 18/12/2018 1 479 0 0 0 0 8
StockOut63143_001 63143 18/12/2018 1 479 0 0 0 0 1
StockOut63144_001 63144 18/12/2018 1 479 0 0 0 0 1
StockOut63145_001 63145 18/12/2018 1 479 0 0 0 0 5
How do I generate rows for each of the quantities specified with a different Serial Number for each Quantity?
My Expected Results would look like following:
SerialNumber iStockID getdate() (No column name) iWarehouseID (No column name) (No column name) (No column name) _etblInvJrBatchLines_iBranchID fQtyOut
StockOut63041_001 63041 18/12/2018 1 479 0 0 0 0 2
StockOut63041_002 63041 18/12/2018 1 479 0 0 0 0 2
StockOut63042_001 63042 19/12/2018 1 479 0 0 0 0 2
StockOut63042_002 63042 19/12/2018 1 479 0 0 0 0 2
StockOut63043_001 63043 18/12/2018 1 479 0 0 0 0 1
StockOut63044_001 63044 18/12/2018 1 479 0 0 0 0 2
StockOut63044_002 63044 18/12/2018 1 479 0 0 0 0 2
StockOut63045_001 63045 18/12/2018 1 479 0 0 0 0 2
StockOut63045_002 63045 18/12/2018 1 479 0 0 0 0 2
StockOut63046_001 63046 18/12/2018 1 479 0 0 0 0 1
StockOut63047_001 63047 18/12/2018 1 479 0 0 0 0 1
StockOut63048_001 63048 18/12/2018 1 479 0 0 0 0 1
StockOut63049_001 63049 18/12/2018 1 479 0 0 0 0 1
StockOut63050_001 63050 18/12/2018 1 479 0 0 0 0 3
StockOut63050_002 63050 18/12/2018 1 479 0 0 0 0 3
StockOut63050_003 63050 18/12/2018 1 479 0 0 0 0 3
StockOut63051_001 63051 18/12/2018 1 479 0 0 0 0 1
StockOut63052_001 63052 18/12/2018 1 479 0 0 0 0 6
StockOut63052_002 63052 18/12/2018 1 479 0 0 0 0 6
StockOut63052_003 63052 18/12/2018 1 479 0 0 0 0 6
StockOut63052_004 63052 18/12/2018 1 479 0 0 0 0 6
StockOut63052_005 63052 18/12/2018 1 479 0 0 0 0 6
StockOut63052_006 63052 18/12/2018 1 479 0 0 0 0 6
StockOut63053_001 63053 18/12/2018 1 479 0 0 0 0 1
StockOut63054_001 63054 18/12/2018 1 479 0 0 0 0 3
StockOut63054_002 63054 18/12/2018 1 479 0 0 0 0 3
StockOut63054_003 63054 18/12/2018 1 479 0 0 0 0 3
StockOut63055_001 63055 18/12/2018 1 479 0 0 0 0 1
StockOut63056_001 63056 18/12/2018 1 479 0 0 0 0 9
StockOut63056_002 63056 18/12/2018 1 479 0 0 0 0 9
StockOut63056_003 63056 18/12/2018 1 479 0 0 0 0 9
StockOut63056_004 63056 18/12/2018 1 479 0 0 0 0 9
StockOut63056_005 63056 18/12/2018 1 479 0 0 0 0 9
StockOut63056_006 63056 18/12/2018 1 479 0 0 0 0 9
StockOut63056_007 63056 18/12/2018 1 479 0 0 0 0 9
StockOut63056_008 63056 18/12/2018 1 479 0 0 0 0 9
StockOut63056_009 63056 18/12/2018 1 479 0 0 0 0 9
StockOut63057_001 63057 18/12/2018 1 479 0 0 0 0 4
StockOut63057_002 63057 18/12/2018 1 479 0 0 0 0 4
StockOut63057_003 63057 18/12/2018 1 479 0 0 0 0 4
StockOut63057_004 63057 18/12/2018 1 479 0 0 0 0 4
StockOut63058_001 63058 18/12/2018 1 479 0 0 0 0 2
StockOut63058_002 63058 18/12/2018 1 479 0 0 0 0 2
StockOut63059_001 63059 18/12/2018 1 479 0 0 0 0 1
StockOut63060_001 63060 18/12/2018 1 479 0 0 0 0 3
StockOut63060_002 63060 18/12/2018 1 479 0 0 0 0 3
StockOut63060_003 63060 18/12/2018 1 479 0 0 0 0 3
StockOut63061_001 63061 18/12/2018 1 479 0 0 0 0 1
StockOut63062_001 63062 18/12/2018 1 479 0 0 0 0 2
StockOut63062_002 63062 18/12/2018 1 479 0 0 0 0 2
StockOut63063_001 63063 18/12/2018 1 479 0 0 0 0 1
StockOut63064_001 63064 18/12/2018 1 479 0 0 0 0 2
StockOut63064_002 63064 18/12/2018 1 479 0 0 0 0 2
StockOut63065_001 63065 18/12/2018 1 479 0 0 0 0 1
StockOut63066_001 63066 18/12/2018 1 479 0 0 0 0 1
StockOut63067_001 63067 18/12/2018 1 479 0 0 0 0 6
StockOut63067_002 63067 18/12/2018 1 479 0 0 0 0 6
StockOut63067_003 63067 18/12/2018 1 479 0 0 0 0 6
StockOut63067_004 63067 18/12/2018 1 479 0 0 0 0 6
StockOut63067_005 63067 18/12/2018 1 479 0 0 0 0 6
StockOut63067_006 63067 18/12/2018 1 479 0 0 0 0 6
StockOut63068_001 63068 18/12/2018 1 479 0 0 0 0 2
StockOut63068_002 63068 18/12/2018 1 479 0 0 0 0 2
StockOut63069_001 63069 18/12/2018 1 479 0 0 0 0 3
StockOut63069_002 63069 18/12/2018 1 479 0 0 0 0 3
StockOut63069_003 63069 18/12/2018 1 479 0 0 0 0 3
StockOut63070_001 63070 18/12/2018 1 479 0 0 0 0 1
StockOut63071_001 63071 18/12/2018 1 479 0 0 0 0 2
StockOut63071_002 63071 18/12/2018 1 479 0 0 0 0 2
StockOut63072_001 63072 18/12/2018 1 479 0 0 0 0 1
StockOut63073_001 63073 18/12/2018 1 479 0 0 0 0 2
StockOut63073_002 63073 18/12/2018 1 479 0 0 0 0 2
StockOut63074_001 63074 18/12/2018 1 479 0 0 0 0 3
StockOut63074_002 63074 18/12/2018 1 479 0 0 0 0 3
StockOut63074_003 63074 18/12/2018 1 479 0 0 0 0 3
StockOut63075_001 63075 18/12/2018 1 479 0 0 0 0 5
StockOut63075_002 63075 18/12/2018 1 479 0 0 0 0 5
StockOut63075_003 63075 18/12/2018 1 479 0 0 0 0 5
StockOut63075_004 63075 18/12/2018 1 479 0 0 0 0 5
StockOut63075_005 63075 18/12/2018 1 479 0 0 0 0 5
StockOut63076_001 63076 18/12/2018 1 479 0 0 0 0 2
StockOut63076_002 63076 18/12/2018 1 479 0 0 0 0 2
StockOut63077_001 63077 18/12/2018 1 479 0 0 0 0 5
StockOut63077_002 63077 18/12/2018 1 479 0 0 0 0 5
StockOut63077_003 63077 18/12/2018 1 479 0 0 0 0 5
StockOut63077_004 63077 18/12/2018 1 479 0 0 0 0 5
StockOut63077_005 63077 18/12/2018 1 479 0 0 0 0 5
StockOut63078_001 63078 18/12/2018 1 479 0 0 0 0 4
StockOut63078_002 63078 18/12/2018 1 479 0 0 0 0 4
StockOut63078_003 63078 18/12/2018 1 479 0 0 0 0 4
StockOut63078_004 63078 18/12/2018 1 479 0 0 0 0 4
StockOut63079_001 63079 18/12/2018 1 479 0 0 0 0 1
StockOut63080_001 63080 18/12/2018 1 479 0 0 0 0 1
StockOut63081_001 63081 18/12/2018 1 479 0 0 0 0 1
StockOut63082_001 63082 18/12/2018 1 479 0 0 0 0 1
StockOut63083_001 63083 18/12/2018 1 479 0 0 0 0 3
StockOut63083_002 63083 18/12/2018 1 479 0 0 0 0 3
StockOut63083_003 63083 18/12/2018 1 479 0 0 0 0 3
StockOut63084_001 63084 18/12/2018 1 479 0 0 0 0 4
StockOut63084_002 63084 18/12/2018 1 479 0 0 0 0 4
StockOut63084_003 63084 18/12/2018 1 479 0 0 0 0 4
StockOut63084_004 63084 18/12/2018 1 479 0 0 0 0 4
StockOut63085_001 63085 18/12/2018 1 479 0 0 0 0 1
StockOut63089_001 63089 18/12/2018 1 479 0 0 0 0 2
StockOut63089_002 63089 18/12/2018 1 479 0 0 0 0 2
StockOut63090_001 63090 18/12/2018 1 479 0 0 0 0 3
StockOut63090_002 63090 18/12/2018 1 479 0 0 0 0 3
StockOut63090_003 63090 18/12/2018 1 479 0 0 0 0 3
StockOut63091_001 63091 18/12/2018 1 479 0 0 0 0 1
StockOut63092_001 63092 18/12/2018 1 479 0 0 0 0 1
StockOut63093_001 63093 18/12/2018 1 479 0 0 0 0 1
StockOut63094_001 63094 18/12/2018 1 479 0 0 0 0 1
StockOut63095_001 63095 18/12/2018 1 479 0 0 0 0 1
StockOut63096_001 63096 18/12/2018 1 479 0 0 0 0 1
StockOut63097_001 63097 18/12/2018 1 479 0 0 0 0 1
StockOut63098_001 63098 18/12/2018 1 479 0 0 0 0 3
StockOut63098_002 63098 18/12/2018 1 479 0 0 0 0 3
StockOut63098_003 63098 18/12/2018 1 479 0 0 0 0 3
StockOut63099_001 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63099_002 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63099_003 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63099_004 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63099_005 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63099_006 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63099_007 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63099_008 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63099_009 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63099_010 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63099_011 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63099_012 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63099_013 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63099_014 63099 18/12/2018 1 479 0 0 0 0 14
StockOut63100_001 63100 18/12/2018 1 479 0 0 0 0 1
StockOut63101_001 63101 18/12/2018 1 479 0 0 0 0 4
StockOut63101_002 63101 18/12/2018 1 479 0 0 0 0 4
StockOut63101_003 63101 18/12/2018 1 479 0 0 0 0 4
StockOut63101_004 63101 18/12/2018 1 479 0 0 0 0 4
StockOut63102_001 63102 18/12/2018 1 479 0 0 0 0 1
StockOut63104_001 63104 18/12/2018 1 479 0 0 0 0 2
StockOut63104_002 63104 18/12/2018 1 479 0 0 0 0 2
StockOut63105_001 63105 18/12/2018 1 479 0 0 0 0 1
StockOut63106_001 63106 18/12/2018 1 479 0 0 0 0 1
StockOut63107_001 63107 18/12/2018 1 479 0 0 0 0 3
StockOut63107_002 63107 18/12/2018 1 479 0 0 0 0 3
StockOut63107_003 63107 18/12/2018 1 479 0 0 0 0 3
StockOut63108_001 63108 18/12/2018 1 479 0 0 0 0 7
StockOut63108_002 63108 18/12/2018 1 479 0 0 0 0 7
StockOut63108_003 63108 18/12/2018 1 479 0 0 0 0 7
StockOut63108_004 63108 18/12/2018 1 479 0 0 0 0 7
StockOut63108_005 63108 18/12/2018 1 479 0 0 0 0 7
StockOut63108_006 63108 18/12/2018 1 479 0 0 0 0 7
StockOut63108_007 63108 18/12/2018 1 479 0 0 0 0 7
StockOut63109_001 63109 18/12/2018 1 479 0 0 0 0 1
StockOut63110_001 63110 18/12/2018 1 479 0 0 0 0 1
StockOut63111_001 63111 18/12/2018 1 479 0 0 0 0 5
StockOut63111_002 63111 18/12/2018 1 479 0 0 0 0 5
StockOut63111_003 63111 18/12/2018 1 479 0 0 0 0 5
StockOut63111_004 63111 18/12/2018 1 479 0 0 0 0 5
StockOut63111_005 63111 18/12/2018 1 479 0 0 0 0 5
StockOut63112_001 63112 18/12/2018 1 479 0 0 0 0 1
StockOut63113_001 63113 18/12/2018 1 479 0 0 0 0 1
StockOut63114_001 63114 18/12/2018 1 479 0 0 0 0 1
StockOut63115_001 63115 18/12/2018 1 479 0 0 0 0 3
StockOut63115_002 63115 18/12/2018 1 479 0 0 0 0 3
StockOut63115_003 63115 18/12/2018 1 479 0 0 0 0 3
StockOut63116_001 63116 18/12/2018 1 479 0 0 0 0 1
StockOut63117_001 63117 18/12/2018 1 479 0 0 0 0 1
StockOut63118_001 63118 18/12/2018 1 479 0 0 0 0 1
StockOut63119_001 63119 18/12/2018 1 479 0 0 0 0 2
StockOut63119_002 63119 18/12/2018 1 479 0 0 0 0 2
StockOut63120_001 63120 18/12/2018 1 479 0 0 0 0 1
StockOut63121_001 63121 18/12/2018 1 479 0 0 0 0 1
StockOut63122_001 63122 18/12/2018 1 479 0 0 0 0 1
StockOut63123_001 63123 18/12/2018 1 479 0 0 0 0 1
StockOut63124_001 63124 18/12/2018 1 479 0 0 0 0 1
StockOut63125_001 63125 18/12/2018 1 479 0 0 0 0 1
StockOut63126_001 63126 18/12/2018 1 479 0 0 0 0 1
StockOut63127_001 63127 18/12/2018 1 479 0 0 0 0 2
StockOut63127_002 63127 18/12/2018 1 479 0 0 0 0 2
StockOut63128_001 63128 18/12/2018 1 479 0 0 0 0 2
StockOut63128_002 63128 18/12/2018 1 479 0 0 0 0 2
StockOut63129_001 63129 18/12/2018 1 479 0 0 0 0 2
StockOut63130_001 63130 18/12/2018 1 479 0 0 0 0 1
StockOut63131_001 63131 18/12/2018 1 479 0 0 0 0 1
StockOut63132_001 63132 18/12/2018 1 479 0 0 0 0 3
StockOut63132_002 63132 18/12/2018 1 479 0 0 0 0 3
StockOut63132_003 63132 18/12/2018 1 479 0 0 0 0 3
StockOut63133_001 63133 18/12/2018 1 479 0 0 0 0 2
StockOut63133_002 63133 18/12/2018 1 479 0 0 0 0 2
StockOut63134_001 63134 18/12/2018 1 479 0 0 0 0 1
StockOut63135_001 63135 18/12/2018 1 479 0 0 0 0 1
StockOut63136_001 63136 18/12/2018 1 479 0 0 0 0 1
StockOut63137_001 63137 18/12/2018 1 479 0 0 0 0 1
StockOut63138_001 63138 18/12/2018 1 479 0 0 0 0 1
StockOut63140_001 63140 18/12/2018 1 479 0 0 0 0 1
StockOut63141_001 63141 18/12/2018 1 479 0 0 0 0 1
StockOut63142_001 63142 18/12/2018 1 479 0 0 0 0 8
StockOut63142_002 63142 18/12/2018 1 479 0 0 0 0 8
StockOut63142_003 63142 18/12/2018 1 479 0 0 0 0 8
StockOut63142_004 63142 18/12/2018 1 479 0 0 0 0 8
StockOut63142_005 63142 18/12/2018 1 479 0 0 0 0 8
StockOut63142_006 63142 18/12/2018 1 479 0 0 0 0 8
StockOut63142_007 63142 18/12/2018 1 479 0 0 0 0 8
StockOut63142_008 63142 18/12/2018 1 479 0 0 0 0 8
StockOut63143_001 63143 18/12/2018 1 479 0 0 0 0 1
StockOut63144_001 63144 18/12/2018 1 479 0 0 0 0 1
StockOut63145_001 63145 18/12/2018 1 479 0 0 0 0 5
StockOut63145_002 63145 18/12/2018 1 479 0 0 0 0 5
StockOut63145_003 63145 18/12/2018 1 479 0 0 0 0 5
StockOut63145_004 63145 18/12/2018 1 479 0 0 0 0 5
StockOut63145_005 63145 18/12/2018 1 479 0 0 0 0 5
Notice that every Serial Number is unique based on the quantity...
Upvotes: 2
Views: 58
Answers (2)
Thom A
Reputation: 95989
I'm posting this as a different answer, as the question has been significantly changed since the OP initially asked their question.
Anyway, this get's the OP what they are after. I've used an inline tally table this time, rather than the View:
--Created table only with the relevant columns for the OP's question
CREATE TABLE dbo.YourTable (SerialNumber varchar(20),
iStockID int,
fQtyOut tinyint);
INSERT INTO dbo.YourTable (SerialNumber,
iStockID,
fQtyOut)
VALUES ('StockOut63041_001',63041,2 ),
('StockOut63042_001',63042,1 ),
('StockOut63043_001',63043,1 ),
('StockOut63044_001',63044,2 ),
('StockOut63045_001',63045,2 ),
('StockOut63046_001',63046,1 ),
('StockOut63047_001',63047,1 ),
('StockOut63048_001',63048,1 ),
('StockOut63049_001',63049,1 ),
('StockOut63050_001',63050,3 ),
('StockOut63051_001',63051,1 ),
('StockOut63052_001',63052,6 ),
('StockOut63053_001',63053,1 ),
('StockOut63054_001',63054,3 ),
('StockOut63055_001',63055,1 ),
('StockOut63056_001',63056,9 ),
('StockOut63057_001',63057,4 ),
('StockOut63058_001',63058,2 ),
('StockOut63059_001',63059,1 ),
('StockOut63060_001',63060,3 ),
('StockOut63061_001',63061,1 ),
('StockOut63062_001',63062,2 ),
('StockOut63063_001',63063,1 ),
('StockOut63064_001',63064,2 ),
('StockOut63065_001',63065,1 ),
('StockOut63066_001',63066,1 ),
('StockOut63067_001',63067,6 ),
('StockOut63068_001',63068,2 ),
('StockOut63069_001',63069,3 ),
('StockOut63070_001',63070,1 ),
('StockOut63071_001',63071,2 ),
('StockOut63072_001',63072,1 ),
('StockOut63073_001',63073,2 ),
('StockOut63074_001',63074,3 ),
('StockOut63075_001',63075,5 ),
('StockOut63076_001',63076,2 ),
('StockOut63077_001',63077,5 ),
('StockOut63078_001',63078,4 ),
('StockOut63079_001',63079,1 ),
('StockOut63080_001',63080,1 ),
('StockOut63081_001',63081,1 ),
('StockOut63082_001',63082,1 ),
('StockOut63083_001',63083,3 ),
('StockOut63084_001',63084,4 ),
('StockOut63085_001',63085,1 ),
('StockOut63089_001',63089,2 ),
('StockOut63090_001',63090,3 ),
('StockOut63091_001',63091,1 ),
('StockOut63092_001',63092,1 ),
('StockOut63093_001',63093,1 ),
('StockOut63094_001',63094,1 ),
('StockOut63095_001',63095,1 ),
('StockOut63096_001',63096,1 ),
('StockOut63097_001',63097,1 ),
('StockOut63098_001',63098,3 ),
('StockOut63099_001',63099,14),
('StockOut63100_001',63100,1 ),
('StockOut63101_001',63101,4 ),
('StockOut63102_001',63102,1 ),
('StockOut63104_001',63104,2 ),
('StockOut63105_001',63105,1 ),
('StockOut63106_001',63106,1 ),
('StockOut63107_001',63107,3 ),
('StockOut63108_001',63108,7 ),
('StockOut63109_001',63109,1 ),
('StockOut63110_001',63110,1 ),
('StockOut63111_001',63111,5 ),
('StockOut63112_001',63112,1 ),
('StockOut63113_001',63113,1 ),
('StockOut63114_001',63114,1 ),
('StockOut63115_001',63115,3 ),
('StockOut63116_001',63116,1 ),
('StockOut63117_001',63117,1 ),
('StockOut63118_001',63118,1 ),
('StockOut63119_001',63119,2 ),
('StockOut63120_001',63120,1 ),
('StockOut63121_001',63121,1 ),
('StockOut63122_001',63122,1 ),
('StockOut63123_001',63123,1 ),
('StockOut63124_001',63124,1 ),
('StockOut63125_001',63125,1 ),
('StockOut63126_001',63126,1 ),
('StockOut63127_001',63127,2 ),
('StockOut63128_001',63128,2 ),
('StockOut63129_001',63129,2 ),
('StockOut63130_001',63130,1 ),
('StockOut63131_001',63131,1 ),
('StockOut63132_001',63132,3 ),
('StockOut63133_001',63133,2 ),
('StockOut63134_001',63134,1 ),
('StockOut63135_001',63135,1 ),
('StockOut63136_001',63136,1 ),
('StockOut63137_001',63137,1 ),
('StockOut63138_001',63138,1 ),
('StockOut63140_001',63140,1 ),
('StockOut63141_001',63141,1 ),
('StockOut63142_001',63142,8 ),
('StockOut63143_001',63143,1 ),
('StockOut63144_001',63144,1 ),
('StockOut63145_001',63145,5 );
GO
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),
Tally AS(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) I
FROM N N1
CROSS JOIN N N2
CROSS JOIN N N3),
CTE AS(
SELECT YT.SerialNumber,
YT.iStockID,
YT.fQtyOut,
V.SerialPrefix,
CONVERT(int,V.SerialSuffix) AS SerialSuffix
FROM dbo.YourTable YT
CROSS APPLY (VALUES(PARSENAME(REPLACE(YT.SerialNumber,'_','.'),2),PARSENAME(REPLACE(YT.SerialNumber,'_','.'),1))) V(SerialPrefix,SerialSuffix))
SELECT C.SerialPrefix + '_' + RIGHT('000' + CONVERT(varchar(3),C.SerialSuffix + T.I - 1),3) AS SerialNumber,
C.iStockID,
C.fQtyOut
FROM CTE C
JOIN Tally T ON C.fQtyOut >= T.I
ORDER BY iStockID;
GO
DROP TABLE dbo.YourTable;
Upvotes: 1
Thom A
Reputation: 95989
One method would be to use a Tally table, like the one as a view in this article (vTally).
WITH VTE AS(
SELECT *
FROM (VALUES (63564,1 ),
(66669,1 ),
(66814,4 ),
(63707,1 ),
(63708,1 ),
(63221,1 ),
(63222,1 ),
(63223,1 ),
(63224,1 ),
(63226,3 ),
(63225,7 ),
(63227,1 ),
(63228,1 ),
(53498,2 ),
(53498,2 ),
(63230,1 ),
(63445,1 ),
(63232,1 ),
(39478,1 ),
(63636,7 ),
(63637,1 ),
(63638,14),
(63639,1 ),
(63523,1 ),
(63524,3 ),
(63525,2 ),
(63526,1 ),
(63403,1 ),
(63404,1 ),
(63405,13),
(63406,14),
(63710,1 ),
(63711,1 ),
(63712,1 ),
(63713,2 ),
(63714,1 ),
(63715,2 ),
(63716,1 ),
(63717,1 ),
(63718,1 ),
(63719,1 ),
(63720,1 ),
(63721,1 ),
(66385,2 ),
(52162,1 ),
(42458,1 ),
(43590,1 ),
(63396,1 ),
(63397,1 ),
(63398,5 )) V(iStockID, fQtyOut))
SELECT V.iStockID,
V.fQtyOut
FROM VTE V
JOIN dbo.vTally T ON V.fQtyOut >= T.N
WHERE V.iStockID = 66814;
Alternatively, if you don't want to use a VIEW, or physical table, you could use a CTE to create an inline tally table:
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),
Tally AS(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) I
FROM N N1
CROSS JOIN N N2
CROSS JOIN N N3)
SELECT V.iStockID,
V.fQtyOut
FROM VTE V --Assumes VTE was declared, i just haven't repeated the code
JOIN Tally T ON V.fQtyOut >= T.I
WHERE V.iStockID = 66814;
Upvotes: 2
Related Questions
- Auto fill field with value from above row value
- Select data from a table and fill in the columns in the same table
- Populate value for new column based on other columns
- Need to populate a column based on another column in another table
- How to populate a column's value based on two other column's values
- not sure how to populate a table based on the contents of another table
- populate multiple variables based on row
- SQL: Populating Column B where Column A has a match elsewhere in Column B
- SQL SERVER : populating a column based on other other columns
- Coalescing rows based on field value in row