March 20, 2015 at 8:53 am
Hello comunity
I need to use WHILE to avoid Cursor under certains conditions.
My SELECT statment is:
SELECT ref, ano, numberofyears ,nreint, naoreint,degress,
tabela, tax, taxamaxima,[evactual],
[evaldepact],[ereintact],nrregbt,[taxAmtAno]
FROM deprec
ORDER BY [ref] ASC
numberofyears= 100 /tax for exemple for a good where lifecycle is 4 years ,ex:
Tax = 25% Then 100/25 = 4 years
I see this WHILE script, but i need to run :
1. for each REF + Until years < 4 in this exemple, because i have goods years depend on Percent.
the WHILE script i see is:
DECLARE @table1 TABLE (Id int not null primary key identity(1,1), col1 int )
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @num_rows=@@ROWCOUNT
SET @cnt=0
WHILE @cnt<@num_rows
BEGIN
SET @cnt=@cnt+1
SELECT
@selected=col1
FROM @table1
WHERE Id=@cnt
--do your stuff here--
INSERT INTO [dbo].[bt]([ref],[deprec],taxa)
VALUES
( ref, ROUND([evactual] * taxa,2) , [taxa])
END
My dought is how to make the LOOP for each REF until Year < 4 (like my example)
Many thanks
Luis SAntos
March 20, 2015 at 9:06 am
U should try to use a set-based statement, while or cursors are both loops.
I think u can use something like:
Insert into [dbo].[bt]([ref],[deprec],taxa)
SELECT col1, --do stuff here
FROM @table1
WHERE Id between 1 and @num_rows
March 20, 2015 at 9:57 am
It would help us if you could provide some consumable test data with expected results in the form of a temp table or table variable.
This would allow us to see what you are trying to do and test possible solutions.
I'm pretty sure this can be done as a set, but need the data to test with.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 20, 2015 at 10:02 am
It's also worth noting that sometimes a cursor performs much better than a loop (e.g. the WHILE logic that you are using).
That said, and has already been mentioned, you can absolutely get this done without a cursor, loop or any other RBAR-based method.
-- Itzik Ben-Gan 2001
March 20, 2015 at 12:42 pm
Hello
Many thanks for your replies, this is a small example only with 2 diferentes references:
SELECT 'A1'[ref], 2010 [year],4 [numberyear],0 [nrreint],0 [naoreint],0[degress],
1400[tabela],25[tax],25[taxamaxima],1000[PriceValue],0[evaldepact],0[ereintact],25[taxAmtAno]
UNION ALL
SELECT 'A2'[ref], 2004 [year],10 [numberyear],0 [nrreint],0 [naoreint],0[degress],
1400[tabela],10[tax],10[taxamaxima],2000[PriceValue],0[evaldepact],0[ereintact],10[taxAmtAno]
Result FOR Ref A1 must INSERT 4 times ON TABLE TempBT, because 2000/0.10 (Tax) RETURN 4 years
INSERT INTO ##TempBT([ref],[deprec],taxa)
VALUES
( 'A1', ROUND([PriceValue] * [tax],2) , [tax])
Expect returning values :
A1, 250, 25
A1, 250, 25
A1, 250, 25
A1, 250, 25
Result for A2 must INSERT 10 times ON TABLE TempBT, because 100/10 (Tax) RETURN 10 years:
INSERT INTO ##TempBT([ref],[deprec],taxa)
VALUES
( 'A2', ROUND([PriceValue] * [tax],2) , [tax])
Expect returning values :
A2, 200, 10
A2, 200, 10
A2, 200, 10
A2, 200, 10
A2, 200, 10
A2, 200, 10
A2, 200, 10
A2, 200, 10
A2, 200, 10
A2, 200, 10
Like you see, i must control when REF change and after that how many times i must use the INSERT depending on
100 / Tax.
Best regards,
Luis Santos
March 20, 2015 at 1:32 pm
March 20, 2015 at 1:35 pm
Hello again
Sorry i have make a misture,
For referente A1 the valeu is 1000/25=4 years , tem the result expectativa is:
A1 250 25
A1 250 25
A1 250 25
A1 250 25
Best regards
Luis santos
March 21, 2015 at 11:12 am
Hello again
I found this interesting code to avoid cursor, but i still have a problema because i cannot repeat 2 INSERT for the same PRODUCTID.
This is the code:
IF OBJECT_ID('Pr_ProductSalesAlternativeReport', 'p') IS NOT NULL
BEGIN
DROP PROCEDURE Pr_ProductSalesAlternativeReport
PRINT '<< Pr_ProductSalesAlternativeReport procedure dropped >>'
END
GO
CREATE PROCEDURE Pr_ProductSalesAlternativeReport
AS
BEGIN
/*
Purpose: Calculate the total quantity and sales of the product in
product wise.
Input : No input specified. It will calculate for all the products
in Product Master table.
Output : ProductID,ProductName,Total Quantity and Grand Total of
sale.
Method : The report generated without using the Cursor
-----------------------------------------------------------------------
********************* Modification History *************************
-----------------------------------------------------------------------
S.No Name Date Version
-----------------------------------------------------------------------
1. Erode Senthilkumar Sep 01, 2009 1.0
-----------------------------------------------------------------------
*/
SET NOCOUNT ON
SET XACT_ABORT ON
-- DECLARE variables----------------------
DECLARE @ProductID VARCHAR(18)
DECLARE @ProductName VARCHAR(100)
DECLARE @TotalQty INT
DECLARE @Total MONEY
DECLARE @index INT
DECLARE @RecordCnt INT
DECLARE @nrRepeat INT
DECLARE @Years INT
DECLARE @ProdID TABLE(iSNo INT IDENTITY(1, 1), iProductID VARCHAR(18), iYears INT)
DECLARE @ProductSales TABLE
(
iSNo INT IDENTITY(1, 1),
iProductID VARCHAR(18),
vProductName VARCHAR(100),
iTotalQty INT,
iGrandTotal MONEY,
iYears INT
)
SELECT @index = 1
SELECT @nrRepeat = 0
SELECT @Years = 0
INSERT INTO @ProdID
(
iProductID,
iYears
)
SELECT ref, 100/50
FROM st (NOLOCK)
WHERE ref LIKE '0621%'
ORDER BY
ref ASC
SELECT @RecordCnt = COUNT(iSNo)
FROM @ProdID
WHILE (@Index <= @RecordCnt)
BEGIN
SELECT @ProductID = iProductID, @Years = iYears
FROM @ProdID
WHERE iSNo = @index
SELECT @ProductName = DESIGN
FROM ST (NOLOCK)
WHERE ref = @ProductID
SELECT @TotalQty = SUM(Qtt),
@Total = SUM(evu * Qtt)
FROM SL (NOLOCK)
WHERE ref = @ProductID
WHILE (@nrRepeat < @Years)
BEGIN
INSERT INTO @ProductSales
(
iProductID,
vProductName,
iTotalQty,
iGrandTotal,
iYears
)
VALUES
(
@ProductID,
@ProductName,
@TotalQty,
@Total,
@Years
)
SELECT @nrRepeat = @nrRepeat + 1
END
END
SELECT *
FROM @ProductSales
END
GO
the Result is:
iSNo iProductID vProductName iTotalQty iGrandTotal iYears
1062122-ANB CHINELO SENHORA CX 12 3984 6954,072 2
2062122-ANB CHINELO SENHORA CX 12 3984 6954,072 2
My query :
SELECT ref, 100/50 [Years]
FROM st (NOLOCK)
WHERE ref LIKE '0621%'
ORDER BY
ref ASC
Return this result:
refYears
062122-ANB 2
062172-ANB 2
062173-ANB 2
March 21, 2015 at 11:18 am
Hello again
I found this interesting code to avoid cursor, but i still have a problema because i cannot repeat 2 INSERT for each diferente PRODUCTID.
This is the code:
IF OBJECT_ID('Pr_ProductSalesAlternativeReport', 'p') IS NOT NULL
BEGIN
DROP PROCEDURE Pr_ProductSalesAlternativeReport
PRINT '<< Pr_ProductSalesAlternativeReport procedure dropped >>'
END
GO
CREATE PROCEDURE Pr_ProductSalesAlternativeReport
AS
BEGIN
/*
Purpose: Calculate the total quantity and sales of the product in
product wise.
Input : No input specified. It will calculate for all the products
in Product Master table.
Output : ProductID,ProductName,Total Quantity and Grand Total of
sale.
Method : The report generated without using the Cursor
-----------------------------------------------------------------------
********************* Modification History *************************
-----------------------------------------------------------------------
S.No Name Date Version
-----------------------------------------------------------------------
1. Erode Senthilkumar Sep 01, 2009 1.0
-----------------------------------------------------------------------
*/
SET NOCOUNT ON
SET XACT_ABORT ON
-- DECLARE variables----------------------
DECLARE @ProductID VARCHAR(18)
DECLARE @ProductName VARCHAR(100)
DECLARE @TotalQty INT
DECLARE @Total MONEY
DECLARE @index INT
DECLARE @RecordCnt INT
DECLARE @nrRepeat INT
DECLARE @Years INT
DECLARE @ProdID TABLE(iSNo INT IDENTITY(1, 1), iProductID VARCHAR(18), iYears INT)
DECLARE @ProductSales TABLE
(
iSNo INT IDENTITY(1, 1),
iProductID VARCHAR(18),
vProductName VARCHAR(100),
iTotalQty INT,
iGrandTotal MONEY,
iYears INT
)
SELECT @index = 1
SELECT @nrRepeat = 0
SELECT @Years = 0
INSERT INTO @ProdID
(
iProductID,
iYears
)
SELECT ref, 100/50 [Years]
FROM st (NOLOCK)
WHERE ref LIKE '0621%'
ORDER BY
ref ASC
SELECT @RecordCnt = COUNT(iSNo)
FROM @ProdID
WHILE (@Index <= @RecordCnt)
BEGIN
SELECT @ProductID = iProductID, @Years = iYears
FROM @ProdID
WHERE iSNo = @index
SELECT @ProductName = DESIGN
FROM ST (NOLOCK)
WHERE ref = @ProductID
SELECT @TotalQty = SUM(Qtt),
@Total = SUM(evu * Qtt)
FROM SL (NOLOCK)
WHERE ref = @ProductID
WHILE (@nrRepeat < @Years)
BEGIN
INSERT INTO @ProductSales
(
iProductID,
vProductName,
iTotalQty,
iGrandTotal,
iYears
)
VALUES
(
@ProductID,
@ProductName,
@TotalQty,
@Total,
@Years
)
SELECT @nrRepeat = @nrRepeat + 1
END
END
SELECT *
FROM @ProductSales
END
GO
the Result is:
iSNo iProductID vProductName iTotalQty iGrandTotal iYears
1062122-ANB CHINELO SENHORA CX 12 3984 6954,072 2
2062122-ANB CHINELO SENHORA CX 12 3984 6954,072 2
My query :
SELECT ref, 100/50 [Years]
FROM st (NOLOCK)
WHERE ref LIKE '0621%'
ORDER BY
ref ASC
Return this result:
refYears
062122-ANB 2
062172-ANB 2
062173-ANB 2
I pretend this final result for each REF:
iSNo iProductID vProductName iTotalQty iGrandTotal iYears
1062122-ANB CHINELO SENHORA CX 12 3984 6954,072 2
2062122-ANB CHINELO SENHORA CX 12 3984 6954,072 2
1062172-ANB CHINELO SENHORA CX 12 500 1555,332 2
2062172-ANB CHINELO SENHORA CX 12 500 1555,332 2
1062173-ANB CHINELO SENHORA CX 12 3800 4500,000 2
2062173-ANB CHINELO SENHORA CX 12 3800 4500,000 2
Please someone could help me about this script, because i don´t understand why i cannot obtain this result.
Many thanks
Luis Santos
March 21, 2015 at 8:18 pm
First of all, you didn't read the article that Alex Suprun provided the link to above. Go back to his post, click on the link, and read that article. It WILL change your life and help you get away from thinking in rows and thinking in columns, instead. Don't put it off. Go and read it before you do anything else including reading the rest of this post.
Once you've read that article, you'll need to start putting the information for it to use. One way is to build and install a Tally Table or build and install a high speed function to replace the Tally Table. Here is one way to build such a function. I suggest you install it and start using it instead of WHILE loops. The function will work in all versions of SQL Server from 2005 and up. If you want to know how to use it, read the comments in the header of the function.
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Billion.
As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.
Usage:
--===== Syntax example (Returns BIGINT)
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
Notes:
1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URLs for how it works and introduction for how it replaces certain loops.
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
that many values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending
sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.
DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);
8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN INT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) --10E1 or 10 rows
, E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c) --10E3 or 1 Thousand rows
, E9(N) AS (SELECT 1 FROM E3 a, E3 b, E3 c) --10E9 or 1 Billion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E9 -- Values from 1 to @MaxN
;
On to your immediate problem...
Here's the test data you provided. I've incorporated it into a temporary test table to make it persist so you can more easily play with all this.
SELECT 'A1'[ref], 2010 [year],4 [numberyear],0 [nrreint],0 [naoreint],0[degress],
1400[tabela],25[tax],25[taxamaxima],1000[PriceValue],0[evaldepact],0[ereintact],25[taxAmtAno]
INTO #TestTable
UNION ALL
SELECT 'A2'[ref], 2004 [year],10 [numberyear],0 [nrreint],0 [naoreint],0[degress],
1400[tabela],10[tax],10[taxamaxima],2000[PriceValue],0[evaldepact],0[ereintact],10[taxAmtAno]
SELECT * FROM #TestTable
;
Now your problem (based on what's available in your test data) becomes absolute child's play.
SELECT tt.ref
,deprec = tt.PriceValue / tt.numberyear
,taxa = taxAmtAno
FROM #TestTable tt
CROSS APPLY dbo.fnTally(1,tt.numberyear)
;
Here's the result.
ref deprec taxa
---- ----------- -----------
A1 250 25
A1 250 25
A1 250 25
A1 250 25
A2 200 10
A2 200 10
A2 200 10
A2 200 10
A2 200 10
A2 200 10
A2 200 10
A2 200 10
A2 200 10
A2 200 10
(14 row(s) affected)
You still haven't read that article, have you? Go read it now! 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 22, 2015 at 10:02 am
Hello Jeff
First thanks for your reply and also about all the advice.
I will read more carefully the article to try to understand it, because it is very important to me first understand it and then use it without doubts and adapt in my day to day.
I would also like to thank for the examples that you send me from using this technique that is totally new to me.
I am very grateful for the important help and recommendations.
Many thanks,
Luis Santos
March 22, 2015 at 2:19 pm
My pleasure, good Sir. Thank you for the feedback.
My question now is do you have any questions about how the solution I provided actually works?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply