March 7, 2012 at 8:45 am
What I’m looking for is running total on Quantity for each product. So that I can find difference between onhand and quantity. I copied the sample code, so that it is easy to understand.
CREATE TABLE #Final (Product int, RDate smalldatetime, Quantity int, OnHand int)
INSERT INTO #Final Values (1011,'2012/02/15',35,56)
INSERT INTO #Final Values (1011,'2012/01/11',31,56)
INSERT INTO #Final Values (1011,'2012/01/01',23,56)
INSERT INTO #Final Values (1011,'2011/12/26',104,56)
INSERT INTO #Final Values (2134,'2012/02/10',27,26)
INSERT INTO #Final Values (2134,'2012/01/26',54,26)
SELECT
Product,RDate,Quantity,OnHand
FROM #Final
DROP TABLE #Final
March 7, 2012 at 8:54 am
Thanks for posting DDL and data samples, only one thing is missing:
expected results. What exactly do you want to see based on your setup
March 7, 2012 at 9:02 am
Product RDate Quantity OnHand RunningTotal
1011 2/15/2012 0:00 35 56 35
1011 1/11/2012 0:00 31 56 66
1011 1/1/2012 0:00 23 56 89
1011 12/26/2011 0:00 104 56 193
2134 2/10/2012 0:00 27 26 27
2134 1/26/2012 0:00 54 26 81
This is how i want to see. Can we do that without using cursor?
March 7, 2012 at 9:24 am
Well done!
--1. you need some thing to uniquely identify each record and enforce the order
-- in which running total can be calculatedm based on your example, the
-- order can not be done by RDate therefore I've introduced RowNo per Product
-- into your setup (you may get away with having IDENTITY for this):
CREATE TABLE #Final (Product int, RDate smalldatetime, Quantity int, OnHand int, RowNo INT)
INSERT INTO #Final Values (1011,'2012/02/15',35,56,1)
INSERT INTO #Final Values (1011,'2012/01/11',31,56,2)
INSERT INTO #Final Values (1011,'2012/01/01',23,56,3)
INSERT INTO #Final Values (1011,'2011/12/26',104,56,4)
INSERT INTO #Final Values (2134,'2012/02/10',27,26,1)
INSERT INTO #Final Values (2134,'2012/01/26',54,26,2)
-- we need a column for RunningTotal
ALTER TABLE #Final ADD RunningTotal INT
-- we need unique clustered index to inforce update order
CREATE UNIQUE CLUSTERED INDEX ix_#Final ON #Final (Product ASC, RowNo ASC)
-- Now a running total business
-- 1. we nit vars to track the product change and calc running total
DECLARE @RunningTotal INT, @Product INT
-- 2. running total update:
UPDATE F
SET @RunningTotal = CASE WHEN @Product IS NULL OR Product != @Product
THEN Quantity
ELSE @RunningTotal + Quantity
END
,RunningTotal = @RunningTotal
,@Product = Product
FROM #Final AS F WITH (TABLOCKX)
OPTION (MAXDOP 1)
select * from #Final
Now, the most important part: read this in order to understand why and how the above is working:
http://qa.sqlservercentral.com/articles/T-SQL/68467/
It was quite a time since I've read it myself, therefore you may find some other suggestions of how to improve the above.
It is "MUST READ" article for your issue
March 7, 2012 at 9:44 am
I have used a different way, it runs fine but it displays same vale if the date is same for two rows. Below is the example. Please see row 2 and 3. So how can i create rownumber separate for this?
CREATE TABLE #Final (Product int, RDate smalldatetime, Quantity int, OnHand int)
INSERT INTO #Final Values (1011,'2012/02/15',35,56)
INSERT INTO #Final Values (1011,'2012/01/11',31,56)
INSERT INTO #Final Values (1011,'2012/01/11',4,56)
INSERT INTO #Final Values (1011,'2012/01/01',23,56)
INSERT INTO #Final Values (1011,'2011/12/26',104,56)
INSERT INTO #Final Values (2134,'2012/02/10',27,26)
INSERT INTO #Final Values (2134,'2012/01/26',54,26)
SELECT
F.Product,F.RDate,F.Quantity,F.OnHand,
(SELECT SUM(Quantity)FROM #Final F1 WHERE F1.Product = F.Product and F.RDate <= F1.Rdate)
FROM #Final F
ORDER BY 1,2 desc
DROP TABLE #Final
March 7, 2012 at 10:00 am
What your solution does is create a triangular join, which will not scale well in a real world situation with more records than your sample. The article Eugene posted the URL to is a very good reference written by a highly regarded person (Jeff Moden won a DBA of the year award last year)
Since it looks like you're already using a temp table for this, adding the clustered index and using the special form of the UPDATE statement would be the best option.
March 7, 2012 at 10:02 am
Shree-903371 (3/7/2012)
I have used a different way, it runs fine but it displays same vale if the date is same for two rows. Below is the example. Please see row 2 and 3. So how can i create rownumber separate for this?CREATE TABLE #Final (Product int, RDate smalldatetime, Quantity int, OnHand int)
INSERT INTO #Final Values (1011,'2012/02/15',35,56)
INSERT INTO #Final Values (1011,'2012/01/11',31,56)
INSERT INTO #Final Values (1011,'2012/01/11',4,56)
INSERT INTO #Final Values (1011,'2012/01/01',23,56)
INSERT INTO #Final Values (1011,'2011/12/26',104,56)
INSERT INTO #Final Values (2134,'2012/02/10',27,26)
INSERT INTO #Final Values (2134,'2012/01/26',54,26)
SELECT
F.Product,F.RDate,F.Quantity,F.OnHand,
(SELECT SUM(Quantity)FROM #Final F1 WHERE F1.Product = F.Product and F.RDate <= F1.Rdate)
FROM #Final F
ORDER BY 1,2 desc
DROP TABLE #Final
To create a ROW_NUMBER per product:
SELECT *, ROW_NUMBER() OVER (PARTITION BY Product ORDER BY (SELECT NULL)) RowNumber
FROM #Final
However:
1. There is still a problem of ORDER? Your dates don't appear to be in Ascending order. So, ORDER BY (SELECT NULL) is used, which can not guarantee proper order.
2. Your method is much-much slower than "quirky update" why would you want to use it? Have you read an article?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply