May 8, 2013 at 5:03 am
Hi Chris,
I have put some transaction statement in this script.
Is this right?
--ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]
Declare@vouchertypeID int = 1,
@denomination int = 400,
@quantity int = 5,
@amountvalue int = 100,
@userid varchar(50) = 1,
@validateDays int = 4
--AS
--BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL Serializable
DECLARE
--@vouchertype varchar(20),
@transactionID varchar(20),
--@count int =1,
@VoucherNumber int, -- was @max-2
@vouchertypenew varchar(20),
@retval varchar(20),
@voucherstatus int
-- table variables receive OUTPUT
DECLARE @VoucherNumberTable TABLE (vouchervalue INT);
DECLARE @transactionIDTable TABLE (transactionID INT);
--------------------------------------------------------------------------------------
-- Load some variables. Exit immediately if vouchertype is invalid
--------------------------------------------------------------------------------------
SELECT
@vouchertypenew = SUBSTRING(TrimmedVoucherType,1+CHARINDEX(' ',TrimmedVoucherType,1),20),
@retval = LEFT(TrimmedVoucherType,1)
+ ISNULL(SUBSTRING(TrimmedVoucherType,1+NULLIF(CHARINDEX(' ',TrimmedVoucherType,1),0),1),'')
FROM GV_VoucherType v
CROSS APPLY (SELECT TrimmedVoucherType = LTRIM(RTRIM(v.vouchertype))) x
WHERE v.VoucherTypeID = @vouchertypeID;
--IF @vouchertypenew IS NULL
--RETURN -1
SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New';
--------------------------------------------------------------------------------------
-- Read existing @VoucherNumber, set next @VoucherNumber
--------------------------------------------------------------------------------------
-- save off the current value to @VoucherNumber
-- and update to the next available value
UPDATE gv_vouchervalue
SET vouchervalue = ISNULL(vouchervalue,0) + @quantity
OUTPUT deleted.vouchervalue INTO @VoucherNumberTable
WHERE voucherabbreviation = 'V' + @retval;
SELECT @VoucherNumber = ISNULL(vouchervalue,0) FROM @VoucherNumberTable;
--------------------------------------------------------------------------------------
-- Read existing @transactionID, set next @transactionID
--------------------------------------------------------------------------------------
-- save off the current value to @transactionID
-- and update to the next available value
UPDATE gv_vouchervalue
SET VoucherValue = VoucherValue + 1
OUTPUT deleted.vouchervalue INTO @transactionIDTable
WHERE VoucherAbbreviation = 'TRNID'
SELECT @transactionID = ISNULL(transactionID,0) FROM @transactionIDTable;
--------------------------------------------------------------------------------------
-- Insert required number of rows into GV_Voucher
--------------------------------------------------------------------------------------
-- Inline tally table generates (@quantity) rows
BEGIN Tran
;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),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)
--select N from iTally
INSERT INTO GV_Voucher
SELECT TOP(@quantity)
@vouchertypeID,
'V'+@retval + Replace( Str( @VoucherNumber + t.n, 7), ' ', '0'),
@denomination,
DATEADD(MM,6,GETDATE()),
GETDATE(),
GETDATE(),
@voucherstatus,
'TRN' + REPLACE( Str( @transactionID + 1, 7), ' ', '0'),
@quantity,
CASE @vouchertypenew
WHEN 'Discount' THEN @amountvalue
ELSE 0
END,
@validateDays,
0
FROM iTally t
COmmit Tran
--END
--RETURN 0
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 8, 2013 at 5:11 am
Here's what I'd recommend you do.
Firstly, read the error trapping article. If your batch fails in the middle of the transaction then you need a graceful way to back out and report it. Build error trapping into your script before defining a transaction.
Secondly, don't change the transaction isolation level without understanding exactly what you are doing and what the impact may be.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 8, 2013 at 5:17 am
ChrisM@Work (5/8/2013)
Here's what I'd recommend you do.Firstly, read the error trapping article. If your batch fails in the middle of the transaction then you need a graceful way to back out and report it. Build error trapping into your script before defining a transaction.
Secondly, don't change the transaction isolation level without understanding exactly what you are doing and what the impact may be.
Actually as per the problem that I posted
Hi, I have a scenario in which I am performing insertion on a table using a stored procedure. For every insertion there is a ID generation for the rows like if a quantity is 100 then for 100 rows there will be a single transaction ID as there will be 100 unique vouchers. Suppose if I inserted 1000 rows who should get ID 001 but on another screen I am again performing inserting then it should get another unique Id say 002 but I am getting same ID for both windows.
I find that for it transaction level serializable is good for this scenario...But I am confused where to write those transaction statement 🙁
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 8, 2013 at 5:50 am
One more doubt Chris,
;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),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)
as with this tally table I can insert at max 10k rows but what if I want to insert 1 lakh records, do I need to write SELECT 1 UNION ALL 100 times ?
or is there any other solution for this
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 8, 2013 at 6:12 am
kapil_kk (5/8/2013)
ChrisM@Work (5/8/2013)
Here's what I'd recommend you do.Firstly, read the error trapping article. If your batch fails in the middle of the transaction then you need a graceful way to back out and report it. Build error trapping into your script before defining a transaction.
Secondly, don't change the transaction isolation level without understanding exactly what you are doing and what the impact may be.
Actually as per the problem that I posted
Hi, I have a scenario in which I am performing insertion on a table using a stored procedure. For every insertion there is a ID generation for the rows like if a quantity is 100 then for 100 rows there will be a single transaction ID as there will be 100 unique vouchers. Suppose if I inserted 1000 rows who should get ID 001 but on another screen I am again performing inserting then it should get another unique Id say 002 but I am getting same ID for both windows.
I find that for it transaction level serializable is good for this scenario...But I am confused where to write those transaction statement 🙁
The UPDATE...OUTPUT statements are designed to circumvent this. Instead of updating the control tables with the next number available then reading it for each new row required, the quantity of rows required is added to generate a new starting number for the next batch. Also, instead of adding one row at a time to GV_Voucher, all of the rows are added in one shot. Taken together, these changes also reduce the execution time of the batch, so there's statistically less chance of interference between batches.
There are further changes to make, however I highly recommend that you experiment with the code you now have, without adding the transaction, so you get a feel for what the existing changes have achieved.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 8, 2013 at 6:37 am
kapil_kk (5/8/2013)
One more doubt Chris,;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),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)
as with this tally table I can insert at max 10k rows but what if I want to insert 1 lakh records, do I need to write SELECT 1 UNION ALL 100 times ?
or is there any other solution for this
Nope. Simple change:
FROM E2 a, E2 b, E2 c
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 8, 2013 at 6:49 am
ChrisM@Work (5/8/2013)
kapil_kk (5/8/2013)
One more doubt Chris,;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),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)
as with this tally table I can insert at max 10k rows but what if I want to insert 1 lakh records, do I need to write SELECT 1 UNION ALL 100 times ?
or is there any other solution for this
Nope. Simple change:
FROM E2 a, E2 b, E2 c
oopes... ok
SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New';
SET @VoucherStatus = (SELECT gvs.VoucherStatusId FROM GV_VoucherStatus gvs WHERE gvs.VoucherStatus = 'New')
Is there any difference between these two assignemnts?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 8, 2013 at 6:54 am
kapil_kk (5/8/2013)
ChrisM@Work (5/8/2013)
kapil_kk (5/8/2013)
One more doubt Chris,;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),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)
as with this tally table I can insert at max 10k rows but what if I want to insert 1 lakh records, do I need to write SELECT 1 UNION ALL 100 times ?
or is there any other solution for this
Nope. Simple change:
FROM E2 a, E2 b, E2 c
oopes... ok
SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New';
SET @VoucherStatus = (SELECT gvs.VoucherStatusId FROM GV_VoucherStatus gvs WHERE gvs.VoucherStatus = 'New')
Is there any difference between these two assignemnts?
The first statement is shorter than the second
and ...try the second statement without the WHERE clause 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 8, 2013 at 7:20 am
Hi Chris,
I tried this tally table to my another scenario for creating booklet...
What I want in this is that if @quantity =2 and @leaf =10 then 20 voucherno will get generated but there will be 2 bookletno as quantity is 2. But its not working as I required..
here is the script that I created:
--ALTER procedure [dbo].[BS_Voucher_CreateBooklet]
Declare@vouchertypeID int =1,
@denomination int = 110,
@quantity int = 2,
@amountvalue int =0,
@leaf int =10,
@userid varchar(50) =1,
@ValidateDays int =30
--AS
BEGIN
SET NOCOUNT ON;
DECLARE
--@vouchertype varchar(20),
@transactionID varchar(20),
--@count int =1,
--@innercount int =1,
@VoucherNumber int,
--@max int,
@vouchertypenew varchar(20),
@retval varchar(20),
@BookletID int = 0,
@VoucherStatus int
-- table variables receive OUTPUT
DECLARE @VoucherNumberTable TABLE (vouchervalue INT);
DECLARE @transactionIDTable TABLE (transactionID INT);
DECLARE @BookletIDTable TABLE (BookletID int);
SELECT
@vouchertypenew = SUBSTRING(TrimmedVoucherType,1+CHARINDEX(' ',TrimmedVoucherType,1),20),
@retval = LEFT(TrimmedVoucherType,1)
+ ISNULL(SUBSTRING(TrimmedVoucherType,1+NULLIF(CHARINDEX(' ',TrimmedVoucherType,1),0),1),'')
FROMGV_VoucherType v
CROSS APPLY (SELECT TrimmedVoucherType = LTRIM(RTRIM(v.vouchertype))) x
WHEREv.VoucherTypeID = @vouchertypeID;
If @vouchertypenew IS NULL
Return -1
SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New';
------------------------------------------------------------------------------
--Read Existing Booklet ID and set next @quantity
------------------------------------------------------------------------------
--SET @BookletID = (SELECT ISNULL(MAX(RIGHT(BookletID,5)),0) FROM GV_Booklet)
Update Gv_VoucherValue
SET VoucherValue = ISNULL(VoucherValue,0)+ @quantity
OUTPUT deleted.VoucherValue INTO @BookletIDTable
WHERE VoucherAbbreviation = 'BOKID'
SELECT @BookletID = ISNULL(BookletID,0) FROM @BookletIDTable
--------------------------------------------------------------------------------------
-- Read existing @VoucherNumber, set next @VoucherNumber
--------------------------------------------------------------------------------------
-- save off the current value to @VoucherNumber
-- and update to the next available value
UPDATE gv_vouchervalue
SET vouchervalue = ISNULL(vouchervalue,0) + @leaf
OUTPUT deleted.vouchervalue INTO @VoucherNumberTable
WHERE voucherabbreviation = 'B' + @retval;
SELECT @VoucherNumber = ISNULL(vouchervalue,0) FROM @VoucherNumberTable;
--------------------------------------------------------------------------------------
-- Read existing @transactionID, set next @transactionID
--------------------------------------------------------------------------------------
-- save off the current value to @transactionID
-- and update to the next available value
UPDATE gv_vouchervalue
SET VoucherValue = VoucherValue + 1
OUTPUT deleted.vouchervalue INTO @transactionIDTable
WHERE VoucherAbbreviation = 'TRNID'
SELECT @transactionID = ISNULL(transactionID,0) FROM @transactionIDTable;
--------------------------------------------------------------------------------------
-- Insert required number of rows into GV_Voucher
--------------------------------------------------------------------------------------
-- Inline tally table generates (@quantity) rows
BEGIN Tran
;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),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b, E2 c)
--select N from iTally
INSERT INTO GV_Booklet
SELECT TOP(@leaf)
'B'+ REPLACE(STR(CONVERT(varchar,(@BookletID+1)),5),' ','0') ,
@leaf,
@denomination,
@vouchertypeID,
'B' + @retval + Replace( Str( @VoucherNumber+ t.N, 7), ' ', '0') ,
@quantity,
GETDATE(),
GETDATE(),
DATEADD(MM,6,GETDATE()),
@VoucherStatus,
'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0'),
@amountvalue,
0,
@ValidateDays
FROM iTally T
--SET @vouchertype = (SELECT VoucherType FROM GV_VoucherType where VoucherTypeID = @vouchertypeID)
--SET @vouchertypenew = RTRIM(LTRIM(@vouchertype));
--SET @retval = LEFT(@vouchertypenew,1);
--SET @VoucherStatus = (SELECT gvs.VoucherStatusId FROM GV_VoucherStatus gvs WHERE gvs.VoucherStatus = 'New')
--WHILE CHARINDEX(' ',@vouchertypenew,1)>0
--BEGIN
--SET @vouchertypenew = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype) - CHARINDEX(' ',@vouchertype,1)));
--SET @retval += LEFT(@vouchertypenew,1);
--END
--WHILE @count <= @quantity
--BEGIN
--BEGIN TRAN
--WHILE @innercount <= @leaf
--BEGIN
--SET @max-2 = (select ISNULL(vouchervalue,0) from gv_vouchervalue WHERE voucherabbreviation = 'B'+@retval )
--SET @transactionID = (select ISNULL(vouchervalue,0) from gv_vouchervalue where voucherabbreviation = 'TRNID')
--If EXIsts (SELECT VoucherType FROM GV_VoucherType WHERE VoucherType = @vouchertype)
--BEGIN
--INSERT INTO GV_Booklet
--VALUES
--(
--'B'+ REPLACE(STR(CONVERT(varchar,(@BookletID+1)),5),' ','0') ,
--@leaf,
--@denomination,
--@vouchertypeID,
--'B' + @retval + Replace( Str( @max-2+1, 7), ' ', '0') ,
--@quantity,
--GETDATE(),
--GETDATE(),
--DATEADD(MM,6,GETDATE()),
--@VoucherStatus,
--'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0'),
--@amountvalue,
--@userid,
--@userid,
--0,
--@ValidateDays
--)
--SET @innercount = @innercount + 1
--Update gv_vouchervalue
--SET vouchervalue = @max-2 + 1
--WHERE voucherabbreviation = 'B'+@retval
--END
--END
--SET @BookletID = @BookletID + 1
--SET @innercount = 1
--SET @count = @count + 1
--END
--UPDATE gv_vouchervalue
--SET VoucherValue = @transactionID + 1
--WHERE VoucherAbbreviation = 'TRNID'
COMMIT TRAN
--select * from GV_Booklet
END
--truncate table GV_Booklet
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 8, 2013 at 7:22 am
kapil_kk (5/8/2013)
Hi Chris,I tried this tally table to my another scenario for creating booklet...
What I want in this is that if @quantity =2 and @leaf =10 then 20 voucherno will get generated but there will be 2 bookletno as quantity is 2. But its not working as I required..
<<snip>>
Haven't I done this for you already, a few weeks ago?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 8, 2013 at 7:24 am
ChrisM@Work (5/8/2013)
kapil_kk (5/8/2013)
Hi Chris,I tried this tally table to my another scenario for creating booklet...
What I want in this is that if @quantity =2 and @leaf =10 then 20 voucherno will get generated but there will be 2 bookletno as quantity is 2. But its not working as I required..
<<snip>>
Haven't I done this for you already, a few weeks ago?
Here's the code:
-- 20th March 2003
DECLARE
@LastBookNo INT = 23,
@LastPageNo INT = 80,
@BooksToInsert INT = 2,
@PagesPerBook INT = 10
;WITH
-- Make an inline tally table (CTE)
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
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
-- Use the tally CTE to construct a booklets CTE
-- containing one row for each booklet required, in this case, 2 booklets.
-- Adding the row number to the last book number (obtained elsewhere) yields
-- the new book numbers
Booklets AS (SELECT TOP (@BooksToInsert) BookNo = @LastBookNo + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E4),
-- use the tally CTE to construct a Pages CTE containing one row per page required
-- in this case, 10 pages per book
Pages AS (SELECT TOP (@PagesPerBook) N FROM E4)
-- cross join the Booklets CTE (2 rows) and the Pages CTE (10 rows) to generate 20 rows.
-- We've already calculated the two new BookNo values in the Booklets CTE
-- In this query we perform the same type of operation with the page number
-- ROW_NUMBER() numbers the rows 1 to 20, added to @LastPageNo gives the new page number
SELECT
@PagesPerBook ,
100,
1,
'VB' + CAST(@LastPageNo + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(6)),
@BooksToInsert,
GETDATE(),
GETDATE(),
DATEADD(MM,6,GETDATE()),
1,
1,
'TRN' + CAST(b.BookNo AS VARCHAR(6))
FROM Booklets b
CROSS JOIN Pages p
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 8, 2013 at 7:25 am
this is the data i get after runing this script:
IdBookletIDLeafCountDenominationVoucherTypeIdVoucherNoQuantityCreatedDateModifyDateExpiryDateVoucherStatusIdTransactionIDAmountValueModifiedByCreatedByIsDeletedValidateDays
21B00005101101BVB848979722013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
22B00005101101BVB848979822013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
23B00005101101BVB848979922013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
24B00005101101BVB848980022013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
25B00005101101BVB848980122013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
26B00005101101BVB848980222013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
27B00005101101BVB848980322013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
28B00005101101BVB848980422013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
29B00005101101BVB848980522013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
30B00005101101BVB848980622013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 8, 2013 at 7:26 am
ChrisM@Work (5/8/2013)
kapil_kk (5/8/2013)
Hi Chris,I tried this tally table to my another scenario for creating booklet...
What I want in this is that if @quantity =2 and @leaf =10 then 20 voucherno will get generated but there will be 2 bookletno as quantity is 2. But its not working as I required..
<<snip>>
Haven't I done this for you already, a few weeks ago?
I apologize Chris, but I missed that thing 🙁 🙁
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 8, 2013 at 7:30 am
kapil_kk (5/8/2013)
this is the data i get after runing this script:IdBookletIDLeafCountDenominationVoucherTypeIdVoucherNoQuantityCreatedDateModifyDateExpiryDateVoucherStatusIdTransactionIDAmountValueModifiedByCreatedByIsDeletedValidateDays
21B00005101101BVB848979722013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
22B00005101101BVB848979822013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
23B00005101101BVB848979922013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
24B00005101101BVB848980022013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
25B00005101101BVB848980122013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
26B00005101101BVB848980222013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
27B00005101101BVB848980322013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
28B00005101101BVB848980422013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
29B00005101101BVB848980522013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
30B00005101101BVB848980622013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
Is there something wrong with it?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 8, 2013 at 7:34 am
ChrisM@Work (5/8/2013)
kapil_kk (5/8/2013)
this is the data i get after runing this script:IdBookletIDLeafCountDenominationVoucherTypeIdVoucherNoQuantityCreatedDateModifyDateExpiryDateVoucherStatusIdTransactionIDAmountValueModifiedByCreatedByIsDeletedValidateDays
21B00005101101BVB848979722013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
22B00005101101BVB848979822013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
23B00005101101BVB848979922013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
24B00005101101BVB848980022013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
25B00005101101BVB848980122013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
26B00005101101BVB848980222013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
27B00005101101BVB848980322013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
28B00005101101BVB848980422013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
29B00005101101BVB848980522013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
30B00005101101BVB848980622013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
Is there something wrong with it?
yes, only 10 rows i get with a single bookletID
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 15 posts - 31 through 45 (of 51 total)
You must be logged in to reply to this topic. Login to reply