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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy