June 29, 2016 at 11:57 am
I'm trying to create a stored procedure in SQL Server 2012 to be used by Crystal reports to print some labels for jobs. What I presently have is the SP creating two temp tables, OrderData and LotData. These are populated from various tables in our MRP database. The parameter to pull the data is the order number.
CREATE TABLE #OrderData
(
PARPRTCHAR(30)NULL,
COMPRTCHAR(30)NULL,
DESC1CHAR(60)NULL,
CurQtyFLOATNULL,
QtyPerFLOATNULL,
COSTFLOATNULL,
YIELDFLOATNULL,
PULLQTYINTNULL
)
CREATE TABLE #LotData
(
COMPRTCHAR(30)NULL,
LOTNUMCHAR(30)NULL,
LOTQTYFLOATNULL,
SERNUMCHAR(30)NULL,
STKDATEDATETIMENULL
)
The result of these two tables will look like this:
COMPRT DESC1 CurQty QtyPer Cost Yield PULLQty
038-611-0020-RDIO 1N4002 100120.0126951200
038-611-0020-RDIO 1N4002 100120.0126951200
038-611-0020-RDIO 1N4002 100120.0126951200
038-611-0020-RDIO 1N4002 100120.0126951200
COMPRT LOTNUM LOTQTY SERNUM STKDATE
038-611-0020-R AE21 115 12/3/2014
038-611-0020-R AE28 808 2/25/2015
038-611-0020-R AA24 63 8/18/2015
038-611-0020-R A1542 5000 5/25/2016
What I need is to create one table to be passed to Crystal to,
1) Generate the required labels for the parts to be pulled for the kit to include COMPRT, LOTNUM, SERNUM, QTY for each lot until no more are required in the kit.
In the example above I would need, 115 of lot AE21, 808 of lot AE28, 63 of lot AA24, 214 of A1542.
2) Generate a new label for the parts left in stock for any quantity not consumed by the kit. In this case 4786 of lot A1542.
The only way I can think of to do this for Crystal is to create a third temp table, call it lbldata which will be the one passed to crystal from my SP, that would evaluate each row in these two tables then create the required rows for each label.
There may be cases where the most current lot won't be used at all and I want it to stop for that part when the pull qty reaches zero. In other words if my pullqty for the sample part was only 940 it would insert rows for 115 of lot AE21, 808 of lot AE28, 17 of lot AA24, plus the row for the other 46 of lot AA24 and skip A1542.
Unfortunately, I have no idea how to write the code to evaluate and insert the appropriate rows. All help or suggestions on a better way would be much appreciated.
June 29, 2016 at 1:58 pm
a tally or numbers table is perfect for this. Jeff Moden has a great article on it here[/url].
Basically, you join your table that has the quantity you want to the Tally table.
SELECT <fields>
FROM <MyTable> m INNER JOIN Tally t ON m.Quantity>=t.Num
Then the result set will contain t.Num duplicates for each record in MyTable
June 29, 2016 at 2:02 pm
I've actually been able to get most of this working but still have a pretty big issue. I've added the following into my SP and it seems to be doing most of what I need correctly. The big issue remaining is that if there are many more parts in stock than needed it's continuing to generate more labels. I believe I need to update the pullqty to fix this as each label is created but, again, I'm not sure how to do that. I've also include a sample of the issue above the added code.
PRTNUM LOTNUMSERNUMPULLQTYQTYREMQTYLBLQTYSTKDATE
038-615-0122-R913 300 70NULL 70 10/4/2013
038-615-0122-R1514 300 490190 300 10/16/2015
038-615-0122-RFeb-04 0 190NULL 190 10/16/2015
CREATE TABLE #LblData
(
PRTNUMchar(30)null,
LOTNUMchar(30)null,
SERNUMchar(30)null,
PULLQTYfloatnull,
QTYfloatnull,
REMQTYfloatnull,
LBLQTYFLOATNULL,
STKDATEDATETIMENULL
)
INSERT INTO #LblData
(PRTNUM, LOTNUM, SERNUM, PULLQTY, QTY, STKDATE)
SELECT o.COMPRT, l.LOTNUM, l.SERNUM, o.PULLQTY, l.LOTQTY, l.STKDATE
FROM #OrderData o join #LotData l on l.COMPRT = o.COMPRT
UPDATE #LblData
set REMQTY = QTY-PULLQTY
where PULLQTY < QTY
INSERT INTO #LblData
(PRTNUM, LOTNUM, SERNUM, PULLQTY, QTY, STKDATE)
SELECT PRTNUM, LOTNUM, SERNUM, 0, REMQTY, STKDATE
FROM #LblData
where REMQTY > 0
UPDATE #LblData
SET LBLQTY =
Case when REMQTY > 0 THEN PULLQTY
ELSE QTY END
June 29, 2016 at 2:03 pm
I'll take a look at that link! Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply