August 1, 2011 at 5:10 am
Hi Experts,
I have a scenario to solve as below. Here the code which I have written by using a CURSOR is updating NetAmount cumlatively as per the CustomerId and OrderId. Part-1 is table and data creation. Part-2 is the CURSOR code.
Can we do the same without using a CURSOR or WHILE loop?
Any help highly appreciated.
SET NOCOUNT ON
--========================================
--PART-1
--========================================
--// Drop table if exists
IF EXISTS (SELECT 1 FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[tbl_CustomerOrder]')
AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[tbl_CustomerOrder]
END
GO
--// Create table
CREATE TABLE tbl_CustomerOrder
(
cit_RowId INT IDENTITY,
cit_CustomerId INT,
cit_OrderId INT,
cdt_OrderDate DATETIME,
cch_OrderType CHAR(1),
cde_OrderAmount DECIMAL(22, 6),
cde_NetAmount DECIMAL(22, 6)
)
GO
--// Insert data to table
INSERT INTO tbl_CustomerOrder
(cit_CustomerId, cit_OrderId, cdt_OrderDate, cch_OrderType, cde_OrderAmount)
SELECT 1, 1, '2011-01-01', 'A', '10000'
UNION ALL
SELECT 2, 2, '2011-02-01', 'A', '20000'
UNION ALL
SELECT 3, 3, '2011-03-01', 'A', '30000'
UNION ALL
SELECT 1, 1, '2011-01-01', 'B', '1000'
UNION ALL
SELECT 2, 2, '2011-02-01', 'B', '2000'
UNION ALL
SELECT 3, 3, '2011-03-01', 'B', '3000'
UNION ALL
SELECT 1, 1, '2011-01-01', 'A', '100'
UNION ALL
SELECT 2, 2, '2011-02-01', 'A', '200'
UNION ALL
SELECT 3, 3, '2011-03-01', 'A', '300'
GO
--========================================
--PART-2
--========================================
--// Declare local variables
DECLARE @lit_RowId INT
DECLARE @lit_CustomerId INT
DECLARE @lit_OrderId INT
DECLARE @ldt_OrderDate DATETIME
DECLARE @lch_OrderType CHAR(1)
DECLARE @lde_OrderAmount DECIMAL(22, 6)
DECLARE @lde_Current_NetAmount DECIMAL(22, 6)
DECLARE @lit_Previous_CustomerId INT
DECLARE @lit_Previous_OrderId INT
--// Setting defaults
SET @lit_Previous_CustomerId = 0
SET @lit_Previous_OrderId = 0
--// Declare cursor
DECLARE rCur_Net CURSOR FOR
SELECT cit_RowId
, cit_CustomerId
, cit_OrderId
, cdt_OrderDate
, cch_OrderType
, cde_OrderAmount
FROM tbl_CustomerOrder
ORDER BY cdt_OrderDate
, cit_CustomerId
, cit_RowId
--// Open and fetch data from cursor
OPEN rCur_Net
FETCH NEXT FROM rCur_Net INTO @lit_RowId, @lit_CustomerId, @lit_OrderId, @ldt_OrderDate, @lch_OrderType, @lde_OrderAmount
WHILE(@@FETCH_STATUS = 0)
BEGIN
--// Setting new customer details
IF (@lit_Previous_CustomerId <> @lit_CustomerId
OR @lit_Previous_OrderId <> @lit_OrderId)
BEGIN
SET @lde_Current_NetAmount = 0
SET @lit_Previous_CustomerId = @lit_CustomerId
SET @lit_Previous_OrderId = @lit_OrderId
END
--// Setting Net Amount
--====================================================================
--// If Order Type is 'A' add amount with the existing else deducting.
--====================================================================
SELECT @lde_Current_NetAmount = ISNULL(@lde_Current_NetAmount, 0)
+ (CASE WHEN @lch_OrderType = 'A' THEN @lde_OrderAmount ELSE -@lde_OrderAmount END)
--// Update Net Amount in table
UPDATE tbl_CustomerOrder
SET cde_NetAmount = @lde_Current_NetAmount
WHERE cit_RowId = @lit_RowId
--// Fetch next row
FETCH NEXT FROM rCur_Net INTO @lit_RowId, @lit_CustomerId, @lit_OrderId, @ldt_OrderDate, @lch_OrderType, @lde_OrderAmount
END
GO
--// Close and deallocate cursor
CLOSE rCur_Net
GO
DEALLOCATE rCur_Net
GO
--// Get data again to confirm the updated NET AMOUNT
SELECT * FROM tbl_CustomerOrder ORDER BY cdt_OrderDate, cit_CustomerId, cit_RowId
_____________________________________________
One ounce of practice is more important than tonnes of dreams
August 1, 2011 at 5:28 am
August 2, 2011 at 2:33 am
Ninja's_RGR'us (8/1/2011)
http://www.sqlservercentral.com/articles/T-SQL/68467/
Hi Ninja,
Thanks for that link. I solved the issue by using the "Quirky Update" 🙂 solution. To do that we need the data to be in proper order. We may required a Temp Table. For time being I have changed the insert query to get the data in proper order. You can see the code below.
Thanks a lot again.
SET NOCOUNT ON
--========================================
-- PART-1
--========================================
--// Drop table if exists
IF EXISTS (SELECT 1 FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[tbl_CustomerOrder]')
AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[tbl_CustomerOrder]
END
GO
--// Create table
CREATE TABLE tbl_CustomerOrder
(
cit_RowId INT IDENTITY,
cit_CustomerId INT,
cit_OrderId INT,
cdt_OrderDate DATETIME,
cch_OrderType CHAR(1),
cde_OrderAmount DECIMAL(22, 6),
cde_NetAmount DECIMAL(22, 6)
)
GO
--// Insert data to table
INSERT INTO tbl_CustomerOrder
(cit_CustomerId, cit_OrderId, cdt_OrderDate, cch_OrderType, cde_OrderAmount)
SELECT 1, 1, '2011-01-01', 'A', '10000'
UNION ALL
SELECT 1, 1, '2011-01-01', 'B', '1000'
UNION ALL
SELECT 1, 1, '2011-01-01', 'A', '100'
UNION ALL
SELECT 2, 2, '2011-02-01', 'A', '20000'
UNION ALL
SELECT 2, 2, '2011-02-01', 'B', '2000'
UNION ALL
SELECT 2, 2, '2011-02-01', 'A', '200'
UNION ALL
SELECT 3, 3, '2011-03-01', 'A', '30000'
UNION ALL
SELECT 3, 3, '2011-03-01', 'B', '3000'
UNION ALL
SELECT 3, 3, '2011-03-01', 'A', '300'
GO
--========================================
-- PART-2 - Quirky Update :)
--========================================
SET NOCOUNT ON
--// Declare local variables
DECLARE @PrevCustomerID INT
DECLARE @PrevOrderID INT
DECLARE @CurrentNetAmount DECIMAL(22,6)
--// Update table with "Quirky Update" !!
UPDATE dbo.tbl_CustomerOrder
SET @CurrentNetAmount
= cde_NetAmount
= (CASE WHEN (cit_CustomerId = @PrevCustomerID AND cit_OrderId = @PrevOrderID)
THEN @CurrentNetAmount + (CASE WHEN cch_OrderType = 'A' THEN cde_OrderAmount ELSE -cde_OrderAmount END)
ELSE (CASE WHEN cch_OrderType = 'A' THEN cde_OrderAmount ELSE -cde_OrderAmount END) END),
@PrevCustomerID = cit_CustomerId,
@PrevOrderID = cit_OrderId
FROM dbo.tbl_CustomerOrder WITH (TABLOCKX) OPTION (MAXDOP 1)
GO
--// Getting the final data
SELECT * FROM tbl_CustomerOrder ORDER BY cdt_OrderDate, cit_CustomerId, cit_RowId
_____________________________________________
One ounce of practice is more important than tonnes of dreams
August 2, 2011 at 6:12 am
I'm not 100% sure this matches the article (read it months / years ago).
I'll simply deffer to Jeff's amazing work and point out that you should do exactly as he says in the article and not skip anything.
I know this is a very long article but you can't really afford to miss anything in that process.
August 2, 2011 at 7:59 am
There's actually a serious improvement that can be made to the code which will guarantee that if something goes wrong, an error will be generated. If you can wait, I'll take a look at this tonight after work.
In the meantime, you've done a nice job of following most of the essential rules, but don't use the code you've developed... it's missing the quintessential Clustered Index and the new safety check that has been mentioned in the rather lengthy discussion that followed the article.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2011 at 8:06 am
Note to self, always follow you gut feelings ;-).
and what a gut if I say so myself.
August 3, 2011 at 9:38 am
Here's the "Full Monty" for this problem using the very high performance Quirky Update method. I've included not only the new safety check for the method (thank you Paul White and Tom Tompson) but I also included a safety check to ensure that only known order types are being used.
As is normal with most of my code, the full explanation is in the comments in the code.
--=====================================================================================================================
-- Create and populate the test table...
-- This is NOT a part of the solution. We're just making data to test with here.
--=====================================================================================================================
--===== Do this experiment in a nice, safe place that everyone has.
USE TempDB
;
--===== Conditionally drop the test table to make reruns easier in SSMS.
-- The 3 part naming convention is overkill but it makes sure no one accidently drops a real table if they happen
-- to be in the wrong database.
IF OBJECT_ID('TempDB.dbo.tbl_CustomerOrder','U') IS NOT NULL
DROP TABLE TempDB.dbo.tbl_CustomerOrder
;
--===== Create the table as before.
CREATE TABLE dbo.tbl_CustomerOrder
(
cit_RowId INT IDENTITY(1,1),
cit_CustomerId INT,
cit_OrderId INT,
cdt_OrderDate DATETIME,
cch_OrderType CHAR(1),
cde_OrderAmount DECIMAL(22,6),
cde_NetAmount DECIMAL(22,6)
)
;
--===== Populate the table with the given test data.
INSERT INTO dbo.tbl_CustomerOrder
(cit_CustomerId, cit_OrderId, cdt_OrderDate, cch_OrderType, cde_OrderAmount)
SELECT 1, 1, '2011-01-01', 'A', '10000' UNION ALL
SELECT 2, 2, '2011-02-01', 'A', '20000' UNION ALL
SELECT 3, 3, '2011-03-01', 'A', '30000' UNION ALL
SELECT 1, 1, '2011-01-01', 'B', '1000' UNION ALL
SELECT 2, 2, '2011-02-01', 'B', '2000' UNION ALL
SELECT 3, 3, '2011-03-01', 'B', '3000' UNION ALL
SELECT 1, 1, '2011-01-01', 'A', '100' UNION ALL
SELECT 2, 2, '2011-02-01', 'A', '200' UNION ALL
SELECT 3, 3, '2011-03-01', 'A', '300'
;
--=====================================================================================================================
-- Do the "partitioned" running total as requested.
-- Note that a cch_OrderType of "A" is considered to be a positive number and that "B" is considered to be a
-- negative number.
--
-- Since it's not likely that the Clustered Index is in the correct columnar order to solve the problem, we'll
-- copy the original data into a temporary table where we can add the correct Clustered Index. This will still be
-- much faster than any cursor you could write.
--=====================================================================================================================
-----------------------------------------------------------------------------------------------------------------------
-- Copy the data to a place where we have absolute control
-- ***THIS WHOLE SECTION CAN BE ELIMINATED IF THE CORRECT CLUSTERED INDEX IS AVAILABLE ON THE ORIGINAL TABLE.***
-- Just remember the page splits that will occur if you suffer a lot of inserts on the original table.
-----------------------------------------------------------------------------------------------------------------------
--===== Conditionally drop the Temp Table to make reruns in SSMS easier.
IF OBJECT_ID('TempDB..#CustomerOrder','U') IS NOT NULL
DROP TABLE #CustomerOrder
;
--===== Create and copy the table on the fly
SELECT cit_RowId, cit_CustomerId, cit_OrderId, cdt_OrderDate, cch_OrderType, cde_OrderAmount,
cde_NetAmount = CAST(NULL AS DECIMAL(22,6)) --Just to be sure it's empty to start with.
INTO #CustomerOrder
FROM dbo.tbl_CustomerOrder
;
--===== Check and make sure there's at least 1 row to work with.
-- Otherwise, there's nothing to do here.
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('No rows copied so nothing to do. Exiting early.',10,1) WITH NOWAIT;
RETURN;
END
;
--===== Add the quintessential Clustered Index
-- Notice that the columnar order must be in the same order that you want the running total to occur in. Also note
-- that cit_RowId is being used as a temporal tie-breaker.
CREATE UNIQUE CLUSTERED INDEX IX_#CustomerOrder_QuComposite
ON #CustomerOrder (cit_CustomerId, cit_OrderId, cdt_OrderDate, cit_RowId) WITH FILLFACTOR = 100
;
-----------------------------------------------------------------------------------------------------------------------
-- Calculate the running total
-----------------------------------------------------------------------------------------------------------------------
--===== Declare some obviously named variables.
-- I didn't follow your naming convention because it uses Hungarian Notation which I believe is a terrible mistake
-- to make. What will you do if a column datatype ever changes? Rework ALL the code that addresses the column?
DECLARE @SafetyCounter BIGINT, --Same a ROW_NUMBER()
@PrevCustomerID INT,
@PrevOrderID INT,
@PrevNetAmount DECIMAL(22,6)
;
--===== Preset the Safety Counter
SELECT @SafetyCounter = 1 --We're assuming there's at least one row at this point.
;
--===== Do the Quirky Update Running Total partitioned by the Customer ID and the Order ID.
-- It's worth saying again that the cit_RowID is being used as the temporal tie-breaker. To decrease the
-- likelyhood of negative daily balances, you COULD use the cch_OrderType as part of the Clustered Index to ensure
-- that all positive amounts are processed before any negative amounts on a daily basis. Also notice that the
-- SafetyCounter is in the same order as the Clustered Index.
WITH
cteEnumerate AS
(
SELECT SafetyCounter = ROW_NUMBER() OVER (ORDER BY cit_CustomerId, cit_OrderId, cdt_OrderDate, cit_RowId),
cit_CustomerId, cit_OrderId, cch_OrderType, cde_OrderAmount, cde_NetAmount
FROM #CustomerOrder
)
UPDATE cte
SET @PrevNetAmount
= cde_NetAmount
= CASE
WHEN SafetyCounter = @SafetyCounter --Makes sure we're working on the correct row.
AND cch_OrderType IN ('A','B') --Makes sure there's no surprises for Order Type.
THEN CASE
WHEN cit_CustomerId = @PrevCustomerID
AND cit_OrderId = @PrevOrderID
THEN @PrevNetAmount
+ CASE --Order type is guaranteed to be "A" or "B" at this point
WHEN cch_OrderType = 'A'
THEN cde_OrderAmount
WHEN cch_OrderType = 'B'
THEN -cde_OrderAmount
END
ELSE cde_OrderAmount
END
ELSE 1/0 --Forces error if Order Type is unexpected or we get out of sync.
END,
@PrevCustomerID = cit_CustomerId,
@PrevOrderID = cit_OrderId,
@SafetyCounter = @SafetyCounter + 1
FROM cteEnumerate cte
OPTION (MAXDOP 1)
;
-----------------------------------------------------------------------------------------------------------------------
-- Update the original table with the partitioned running total.
-- ***AGAIN, THIS SECTION CAN BE ELIMINATED IF THE ORIGINAL TABLE HAS THE CORRECT CLUSTERED INDEX TO BEGIN WITH***
-- Just remember what I previously said about page splits.
-----------------------------------------------------------------------------------------------------------------------
--===== Update the original table with the partitioned running total
UPDATE original
SET cde_NetAmount = updated.cde_NetAmount
FROM dbo.tbl_CustomerOrder original
INNER JOIN #CustomerOrder updated
ON original.cit_RowId = updated.cit_RowId
;
Please let me know how it works out for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2011 at 7:43 am
Hi Jeff, Thank you. Thank you very much. :Wow:
Jeff Moden (8/3/2011)
--===== Do this experiment in a nice, safe place that everyone has.
USE TempDB
;
--===== Conditionally drop the test table to make reruns easier in SSMS.
-- The 3 part naming convention is overkill but it makes sure no one accidently drops a real table if they happen
-- to be in the wrong database.
IF OBJECT_ID('TempDB.dbo.tbl_CustomerOrder','U') IS NOT NULL
DROP TABLE TempDB.dbo.tbl_CustomerOrder
Yeah, I will take care this next time onwards.
-----------------------------------------------------------------------------------------------------------------------
-- Copy the data to a place where we have absolute control
-- ***THIS WHOLE SECTION CAN BE ELIMINATED IF THE CORRECT CLUSTERED INDEX IS AVAILABLE ON THE ORIGINAL TABLE.***
-- Just remember the page splits that will occur if you suffer a lot of inserts on the original table.
-----------------------------------------------------------------------------------------------------------------------
Clustered Index is there. When I was preparing the script, I thought, I required to show only my basic issue.
--===== Create and copy the table on the fly
SELECT cit_RowId, cit_CustomerId, cit_OrderId, cdt_OrderDate, cch_OrderType, cde_OrderAmount,
cde_NetAmount = CAST(NULL AS DECIMAL(22,6)) --Just to be sure it's empty to start with.
INTO #CustomerOrder
FROM dbo.tbl_CustomerOrder
I have a doubt here. What cde_NetAmount = CAST(NULL AS DECIMAL(22,6)) will do? What is the difference if it is without CAST like the following? cde_NetAmount = NULL.
-- I didn't follow your naming convention because it uses Hungarian Notation which I believe is a terrible mistake
-- to make. What will you do if a column datatype ever changes? Rework ALL the code that addresses the column?
True Jeff, We did not implemented this yet. Somebody came with a suggestion like this, So I just started using this in my personal scripts.
We are not naming any fields or variables as per its data type. Luckily got the feedback from you before it is implementing 🙂
Thanks Again
_____________________________________________
One ounce of practice is more important than tonnes of dreams
August 5, 2011 at 6:31 pm
C.K.Shaiju (8/4/2011)
I have a doubt here. What cde_NetAmount = CAST(NULL AS DECIMAL(22,6)) will do? What is the difference if it is without CAST like the following? cde_NetAmount = NULL.
It's just making a NULL filled column with the correct datatype. If you're updating the original table directly (usually not a great idea but OK to do), then there's no need for it.
Thank you VERY much for the feedback, C.K. I very much appreciate it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply