December 19, 2006 at 11:19 pm
am doing the Serial Balance Report
The data is given in a range format i need to culculate the balance,for your information below is an example
Given Stock In Serial Range
10000 - 19999
Stock Out
15000 - 15999
Balance (this is what i calculate out)
10000-14999
16000 -19999
First Loop
BEGIN
--Given the Stock Out Get the Source Stock In and put it into another cursor(Normarlly just 1 record will found)
SECOND LOOP
BEGIN
--calculation
END
END
Is it a other way to speed up the SQL performance other than using cursorless
IF OBJECT_ID('tempdb..#tblBalance') IS NOT NULL
DROP Table #tblBalance
Create Table #tblBalance
(
RowID numeric(18,0) IDENTITY(1, 1) not null,
SerialNoFrom nvarchar(50),
SerialNoTo nvarchar(50))
Declare @SerialNoFrom varchar(50)
Declare @SerialNoTo varchar(50)
Declare @FrontSerialNoFrom nvarchar(50)
Declare @EndSerialNoTo nvarchar(50)
Declare @FrontSerialNoFromNext nvarchar(50)
Declare @EndSerialNoToNext nvarchar(50)
Declare @SKUCode nvarchar(50)
Declare @SLoc nvarchar(10)
Declare @Tmp nvarchar(12)
Declare @tmpCount integer
Declare @sql varchar(100)
Declare @count int
Declare @iRow int
Declare @countNext int
Declare @iRowNext int
SET @count = (SELECT MAX(RowID) FROM multicom_Qty_Minus)
SET @iRow = 1
Print 'Total Count' + convert(varchar,@count)
WHILE @iRow <= @count
BEGIN
PRINT 'Current Count ' + Convert(varchar,@iRow)
PRINT 'GET SERIAL NO FROM'
SET @SerialNoFrom = (SELECT SerialNoFrom FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SERIAL NO TO'
SET @SerialNoTo = (SELECT SerialNoTo FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SLOC'
SET @SLoc = (SELECT SLoc FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SKUCode'
SET @SKUCode = (SELECT SKUCode FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT @SerialNoFrom + '-' + @SerialNoTo
PRINT 'SKUCode ' + @SKUCode
PRINT 'SLoc ' + @SLoc
INSERT INTO #tblBalance
SELECT SerialNoFrom,SerialNoTo FROM multicom WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND
QuantityPlus > 0 AND
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) = Quantity AND
LEN(SerialNoTo) = LEN(@SerialNoTo) AND LEN(SerialNoFrom) = LEN(@SerialNoFrom) AND
SKUCode = @SKUCode AND SLoc = @SLoc AND
convert(numeric,SerialNoFrom) <> convert(numeric,@SerialNoFrom) AND
convert(numeric,SerialNoTo)<>convert(numeric,@SerialNoTo)AND
((CONVERT(numeric,SerialNoFrom) <= CONVERT(numeric,@SerialNoFrom) AND
CONVERT(numeric,SerialNoTo) >= CONVERT(numeric,@SerialNoTo)) OR
(CONVERT(numeric,SerialNoTo) >= CONVERT(numeric,@SerialNoTo) AND
CONVERT(numeric,SerialNoFrom)<= CONVERT(numeric,@SerialNoFrom)))
SET @countNext = @@ROWCOUNT
SET @iRowNext = 1
----IF @countNext == 0 then put the Searil into the ErrorQtyMinus
/*
--------------
--------------
*/
IF @CountNext = 0
BEGIN
INSERT INTO multicom_Error_Qty_Minus
SELECT Plnt,SLoc,
SLocDescription,SKUCode,MvT,PostDate,DocDate,[Serial No (from Material Doc.)],[Serial No (from Delivery Item)],
MatDoc,Item,InvNo,Quantity,Sign,Payer,UserID,PONo,SOff,DeliveryNo,PODate,SOrg,Salesman,MatGroup,DC,SoldTo,CoCd,InvoiceDt,
SerialNoTo,SerialNoFrom,SLocFrom,SLocTo,SalesOffDesc,PayerName1,PrdName,MvTDesc,StdCost,PrdGrp4,QuantityPlus,QuantityMinus
FROM multicom_Qty_Minus WHERE RowID = @iRow
END
PRINT 'Total 2nd loop COUNT'
PRINT @countNext
WHILE @iRowNext <= @countNext
BEGIN
Print 'Current 2nd loop Count ' + convert(varchar,@iRowNext)
SET @FrontSerialNoFrom = (SELECT SerialNoFrom From #tblBalance WHERE RowID = @iRowNext)
SET @EndSerialNoTo = (SELECT SerialNoTo From #tblBalance WHERE RowID = @iRowNext)
Print @FrontSerialNoFrom + '-' + @EndSerialNoTo
IF ISNUMERIC(@FrontSerialNoFrom) = 1 AND ISNUMERIC(@EndSerialNoTo) = 1 AND ISNUMERIC(@SerialNoFrom)=1 AND ISNUMERIC(@SerialNoTo)= 1
BEGIN
IF CONVERT(numeric,@SerialNoFrom) > CONVERT(numeric,@FrontSerialNoFrom)
BEGIN
SET @Tmp = (CONVERT(numeric,@SerialNoFrom) -1)
SET @Tmp = REPLICATE('0',(12 - (len(@Tmp)))) + @Tmp
PRINT 'BALANCE'
PRINT @FrontSerialNoFrom + '-' + CONVERT(nvarchar(12),@Tmp)
PRINT 'QUANTITY'
PRINT (Convert(numeric,@Tmp) - (Convert(numeric,@FrontSerialNoFrom)) + 1)
Insert INTO multicom_Balance
Select Plnt,SLoc,SLocDescription,SKUCode,MvT,PostDate,DocDate,MatDoc,Item,
(Convert(numeric,@Tmp) - (Convert(numeric,@FrontSerialNoFrom)) + 1),
Convert(nvarchar(12),@Tmp),@FrontSerialNoFrom,SalesOffDesc,PayerName1,PrdName,MvTDesc,StdCost,QuantityPlus,
QuantityMinus From multicom_Qty_Minus WHERE RowID = @iRow
END
IF CONVERT(numeric,@EndSerialNoTo) > CONVERT(numeric,@SerialNoTo)
BEGIN
SET @Tmp = (CONVERT(numeric,@SerialNoTo) + 1)
SET @Tmp = REPLICATE('0',(12 - (len(@Tmp)))) + @Tmp
PRINT 'Last Balance'
PRINT CONVERT(nvarchar(12),@Tmp) + '-' + @EndSerialNoTo
PRINT 'QUANTITY'
PRINT (Convert(numeric,@EndSerialNoTo) - (Convert(numeric,@Tmp)) + 1)
Insert INTO multicom_Balance
Select Plnt,SLoc,SLocDescription,SKUCode,MvT,PostDate,DocDate,MatDoc,Item,
(Convert(numeric,@EndSerialNoTo) - (Convert(numeric,@Tmp)) + 1),
@EndSerialNoTo,CONVERT(nvarchar(12),@Tmp),SalesOffDesc,PayerName1,PrdName,MvTDesc,StdCost,QuantityPlus,
QuantityMinus From multicom_Qty_Minus WHERE RowID = @iRow
END
SET @FrontSerialNoFrom = ''
SET @EndSerialNoTo = ''
END
SET @iRowNext = @iRowNext + 1
END
Truncate Table #tblBalance
SET @iRow = @iRow + 1
END
Above is the code example
December 21, 2006 at 4:16 pm
I think there is room for a lot of improvement.
Let's start by reducing the number of reads from 4 queries down to 1.
Replace "SET @SerialNoFrom = (...", "SET @SerialNoTo = (...", "SET @SLoc = (...", and "SET @SKUCode = (..." with the following:
------- BEGIN CODE -------
PRINT 'GET SERIAL NO FROM, SERIAL NO TO, SLOC, AND SKUCode'
SELECT @SerialNoFrom = SerialNoFrom
, @SerialNoTo = SerialNoTo
, @SLoc = SLoc
, @SKUCode = SKUCode
FROM multicom_Qty_Minus
WHERE ISNUMERIC(SerialNoTo) = 1
AND ISNUMERIC(SerialNoFrom) = 1
AND SerialNoFrom ''
AND SerialNoTo ''
AND RowID = @iRow
AND SKUCode '100000017'
AND CASE
WHEN SerialNoFrom ''
AND SerialNoFrom ''
AND ISNUMERIC(SerialNoTo) = 1
AND ISNUMERIC(SerialNoFrom) = 1
THEN ((CONVERT(NUMERIC,SerialNoTo) - CONVERT(NUMERIC,SerialNoFrom)) + 1)
END = Quantity)
------- END CODE -------
Now, all of your variables are set.
More to come in a couple minutes...
Enjoy!
A Brown
Manage all of your backups in one simple job using this script.
December 21, 2006 at 4:39 pm
Second...
ISNUMERIC will return 0 for '' AND NULL values, so there's no need to check for '' in the where statements...
Also, there's no need to check your @SerialNo variables again to see if they are still numeric, as you did that when you populated them.
Enjoy!
A Brown
Manage all of your backups in one simple job using this script.
December 21, 2006 at 4:48 pm
Unfortunately, I've run out of time...
But here's a cleaner version of the script, which should help others help you.
I may have more time later as well.
------ BEGIN CODE ------
IF OBJECT_ID('tempdb..#tblBalance') IS NOT NULL
DROP TABLE #tblBalance
CREATE TABLE #tblBalance
(
RowID NUMERIC(18,0) IDENTITY(1, 1) NOT NULL,
SerialNoFrom NVARCHAR(50),
SerialNoTo NVARCHAR(50))
DECLARE @SerialNoFrom VARCHAR(50)
DECLARE @SerialNoTo VARCHAR(50)
DECLARE @FrontSerialNoFrom NVARCHAR(50)
DECLARE @EndSerialNoTo NVARCHAR(50)
DECLARE @FrontSerialNoFromNext NVARCHAR(50)
DECLARE @EndSerialNoToNext NVARCHAR(50)
DECLARE @SKUCode NVARCHAR(50)
DECLARE @SLoc NVARCHAR(10)
DECLARE @Tmp NVARCHAR(12)
DECLARE @tmpCount INT
DECLARE @sql VARCHAR(100)
DECLARE @count INT
DECLARE @iRow INT
DECLARE @countNext INT
DECLARE @iRowNext INT
SET @count = (SELECT MAX(RowID) FROM multicom_Qty_Minus)
SET @iRow = 1
Print 'Total Count' + CONVERT(VARCHAR,@count)
WHILE @iRow 0
AND ((CONVERT(NUMERIC,SerialNoTo) - CONVERT(NUMERIC,SerialNoFrom)) + 1) = Quantity
AND LEN(SerialNoTo) = LEN(@SerialNoTo)
AND LEN(SerialNoFrom) = LEN(@SerialNoFrom)
AND SKUCode = @SKUCode
AND SLoc = @SLoc
AND CONVERT(NUMERIC,SerialNoFrom) != CONVERT(NUMERIC,@SerialNoFrom)
AND CONVERT(numeric,SerialNoTo) != CONVERT(NUMERIC,@SerialNoTo)
AND (
(CONVERT(NUMERIC,SerialNoFrom) = CONVERT(NUMERIC,@SerialNoTo))
OR (CONVERT(NUMERIC,SerialNoTo) >= CONVERT(NUMERIC,@SerialNoTo)
AND CONVERT(NUMERIC,SerialNoFrom) <= CONVERT(NUMERIC,@SerialNoFrom)))
SET @countNext = @@ROWCOUNT
SET @iRowNext = 1
----IF @countNext == 0 then put the Searil into the ErrorQtyMinus
----
----
IF @CountNext = 0
BEGIN
INSERT INTO multicom_Error_Qty_Minus
SELECT Plnt
, SLoc
, SLocDescription
, SKUCode
, MvT
, PostDate
, DocDate
, [Serial No (from Material Doc.)]
, [Serial No (from Delivery Item)]
, MatDoc
, Item
, InvNo
, Quantity
, Sign
, Payer
, UserID
, PONo
, SOff
, DeliveryNo
, PODate
, SOrg
, Salesman
, MatGroup
, DC
, SoldTo
, CoCd
, InvoiceDt
, SerialNoTo
, SerialNoFrom
, SLocFrom
, SLocTo
, SalesOffDesc
, PayerName1
, PrdName
, MvTDesc
, StdCost
, PrdGrp4
, QuantityPlus
, QuantityMinus
FROM multicom_Qty_Minus
WHERE RowID = @iRow
END
PRINT 'Total 2nd loop COUNT'
PRINT @countNext
WHILE @iRowNext CONVERT(NUMERIC,@FrontSerialNoFrom)
BEGIN
SET @Tmp = (CONVERT(NUMERIC,@SerialNoFrom) -1)
SET @Tmp = REPLICATE('0',(12 - (LEN(@Tmp)))) + @Tmp
PRINT 'BALANCE'
PRINT @FrontSerialNoFrom + '-' + CONVERT(NVARCHAR(12),@Tmp)
PRINT 'QUANTITY'
PRINT (CONVERT(NUMERIC,@Tmp) - (CONVERT(NUMERIC,@FrontSerialNoFrom)) + 1)
INSERT INTO multicom_Balance
SELECT Plnt
, SLoc
, SLocDescription
, SKUCode
, MvT
, PostDate
, DocDate
, MatDoc
, Item
, (CONVERT(NUMERIC,@Tmp) - (CONVERT(NUMERIC,@FrontSerialNoFrom)) + 1)
, CONVERT(NVARCHAR(12),@Tmp)
, @FrontSerialNoFrom
, SalesOffDesc
, PayerName1
, PrdName
, MvTDesc
, StdCost
, QuantityPlus
, QuantityMinus
FROM multicom_Qty_Minus
WHERE RowID = @iRow
END
IF CONVERT(NUMERIC,@EndSerialNoTo) > CONVERT(NUMERIC,@SerialNoTo)
BEGIN
SET @Tmp = (CONVERT(NUMERIC,@SerialNoTo) + 1)
SET @Tmp = REPLICATE('0',(12 - (LEN(@Tmp)))) + @Tmp
PRINT 'Last Balance'
PRINT CONVERT(NVARCHAR(12),@Tmp) + '-' + @EndSerialNoTo
PRINT 'QUANTITY'
PRINT (CONVERT(NUMERIC,@EndSerialNoTo) - (CONVERT(NUMERIC,@Tmp)) + 1)
INSERT INTO multicom_Balance
SELECT Plnt
, SLoc
, SLocDescription
, SKUCode
, MvT
, PostDate
, DocDate
, MatDoc
, Item
, (CONVERT(NUMERIC,@EndSerialNoTo) - (CONVERT(NUMERIC,@Tmp)) + 1)
, @EndSerialNoTo
, CONVERT(NVARCHAR(12),@Tmp)
, SalesOffDesc
, PayerName1
, PrdName
, MvTDesc
, StdCost
, QuantityPlus
, QuantityMinus
FROM multicom_Qty_Minus
WHERE RowID = @iRow
END
SET @FrontSerialNoFrom = ''
SET @EndSerialNoTo = ''
SET @iRowNext = @iRowNext + 1
END
TRUNCATE TABLE #tblBalance
SET @iRow = @iRow + 1
END
------ END CODE ------
Enjoy!
A Brown
Manage all of your backups in one simple job using this script.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply