Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

column to calculate the balance stock dynamically Expand / Collapse
Author
Message
Posted Thursday, February 19, 2009 1:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 4:31 AM
Points: 89, Visits: 231
I have a table as shown below and would like to have the balance quantity which should be dynamically calucalted depending upon receipt(as *1)/issued(as *-1)

ID Code Name Txn_Type Date Qty dsiredOutput(BAL)
1 8100 ACTIFED Receipt 1-Jan-04 100 100
2 8100 ACTIFED Issued 28-Mar-04 5 95
3 8100 ACTIFED Issued 1-Apr-04 5 90
4 8100 ACTIFED Issued 5-May-04 5 85
5 8100 ACTIFED Issued 10-May-04 5 80
6 8110 AGIOLAX Receipt 1-Jan-04 50 50
7 8110 AGIOLAX Issued 12-Feb-04 10 40
8 8110 AGIOLAX Receipt 28-Mar-04 50 90
9 8110 AGIOLAX Issued 5-May-04 25 65
10 8112 OTRIVIN Receipt 1-Jan-04 200 200
11 8112 OTRIVIN Receipt 28-Mar-04 50 250
12 8112 OTRIVIN Issued 1-Apr-04 15 235
13 8112 OTRIVIN Issued 5-May-04 10 225

PLEASE NOTE ID COLUMN is an IDENTITY column.
I am attaching a sample script.Please some help please.


  Post Attachments 
script_4_Balance.txt (47 views, 2.34 KB)
Post #660107
Posted Thursday, February 19, 2009 2:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 8, 2014 12:54 AM
Points: 2,552, Visits: 2,598
Another RUNNING TOTAL problem.., See these articles on how to achieve this...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
http://www.sqlteam.com/article/calculating-running-totals


--Ramesh

Post #660126
Posted Tuesday, February 24, 2009 6:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 5:51 AM
Points: 328, Visits: 2,001
The problem with these sorts of calculations is that a row of data in a database is completely independant. It does not know or care about its neighbours and so trying to force a calculation like this is going to force the database engine into a realm it doesn't much care for.

Best solution is to work out the running total in the application that takes the data, be it reporting services, excel or whatever.

Failing that then this is one of those cases where, from a database perspective, a cursor will be the best solution.

There are other solutions which avoid cursor use, however they have their own draw backs and are not always very efficient.
Try running both the set based and cursor solutions with your own data and see which you prefer
(TIP: when using the cursor solution make sure you filter the select statement that forms the cursor declaration, not the select on the temp table used to create the output)
Post #663392
Posted Wednesday, February 25, 2009 4:52 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:41 AM
Points: 916, Visits: 1,597
How about this one?

with cte as
(
select *, Row_Number() OVER(PARTITION BY ID ORDER BY ID) as RowNum
from #TempStk
)
select a.rownum, a.id, a.status, a.date, a.quantity,
CASE WHEN a.Status <> 'Receipt' THEN SUM(case when b.Status = 'Receipt' Then b.quantity Else -b.quantity END) - a.quantity
ELSE ISNULL(SUM(case when b.Status = 'Receipt' Then b.quantity Else -b.quantity END),0) + a.quantity
END as Bal
from cte a
LEFT outer join cte b
on a.id = b.id and a.RowNum > B.RowNum
group by a.rownum, a.id, a.status, a.date, a.quantity
order by a.id, a.rownum
Post #664695
Posted Thursday, February 26, 2009 2:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 5:51 AM
Points: 328, Visits: 2,001
mh, your solution is fine but when you test it with a lot of data against a cursor, the cursor is orders of magnitude faster

I adapted your query to fit some different test data


drop table sales
CREATE TABLE Sales (DayCount smallint, channel varchar(50), Sales money)
CREATE CLUSTERED INDEX ndx_DayCount ON Sales(DayCount)
go


DECLARE @DayCount smallint, @Sales money, @channel varchar(50)
SET @DayCount = 0
SET @Sales = 10
set @channel = 'a'

WHILE @DayCount < 10000
BEGIN
INSERT Sales VALUES (@DayCount,@channel, @Sales)
SET @DayCount = @DayCount + 1
SET @Sales = @Sales + 15
set @channel = case
when right(cast(@daycount as varchar), 1) = 0 then 'a'
when right(cast(@daycount as varchar), 1) = 1 then 'b'
when right(cast(@daycount as varchar), 1) = 2 then 'c'
when right(cast(@daycount as varchar), 1) = 3 then 'd'
when right(cast(@daycount as varchar), 1) = 4 then 'e'
when right(cast(@daycount as varchar), 1) = 5 then 'f'
when right(cast(@daycount as varchar), 1) = 6 then 'g'
when right(cast(@daycount as varchar), 1) = 7 then 'h'
when right(cast(@daycount as varchar), 1) = 8 then 'i'
when right(cast(@daycount as varchar), 1) = 9 then 'j'
end
END

select * from sales

with cte as
(
select *, Row_Number() OVER(PARTITION BY channel ORDER BY channel) as RowNum
from sales
)
select a.rownum, a.daycount,a.channel, a.sales,
SUM(b.sales ) - a.sales as Bal
from cte a
LEFT outer join cte b
on a.channel = b.channel and a.RowNum > B.RowNum
group by a.rownum, a.channel, a.daycount, a.sales
order by a.channel, a.daycount

CREATE TABLE #Sales (DayCount smallint, Channel varchar(50), Sales money, RunningTotal money)

DECLARE @RunningTotal money
declare @old_channel varchar(50)
SET @RunningTotal = 0
set @old_channel = ''

DECLARE rt_cursor CURSOR
FOR
SELECT DayCount, Channel, Sales
FROM Sales
order by channel, daycount
DECLARE @DayCount smallint, @Sales money, @channel varchar(50)
OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO @DayCount,@Channel, @Sales

WHILE @@FETCH_STATUS = 0
BEGIN
SET @RunningTotal = @RunningTotal + @Sales
INSERT #Sales VALUES (@DayCount,@channel, @Sales, @RunningTotal)
set @old_channel = @channel
FETCH NEXT FROM rt_cursor INTO @DayCount,@channel, @Sales
if @old_channel <> @channel set @runningtotal = 0

END

CLOSE rt_cursor
DEALLOCATE rt_cursor

SELECT * FROM #Sales ORDER BY channel,DayCount

DROP TABLE #Sales
Post #664851
Posted Tuesday, March 3, 2009 9:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 24, 2012 8:10 AM
Points: 2,042, Visits: 499
Hmmm... there are very few scenarios where I've seen cursors faster than CTE or non-RBAR (row by agonizing row) methods. Just curious how many rows you're running through?

Cheers,
Brian
Post #667417
Posted Tuesday, March 3, 2009 8:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
Samuel Vella (2/24/2009)
The problem with these sorts of calculations is that a row of data in a database is completely independant. It does not know or care about its neighbours and so trying to force a calculation like this is going to force the database engine into a realm it doesn't much care for.


Actually.... totally untrue if you know how updates with pseudo-cursors actually work... provided a correctly defined clustered index scan comes into play, the order of the update will ALWAYS be in the correct column order described by the clustered index no matter how badly fragmented or split it may be. Despite all the expert opinions against it, not one of them has been able to break a correctly formed update on a running total problem and it's worked since SQL Server was known as "Sybase" and it still works in all versions of SQL Server including 2k8. In fact, they even have a special version of update and, yes, that special version is documented in Books Online. Code comin' up for this problem in a minute... ;)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #667806
Posted Tuesday, March 3, 2009 8:32 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:23 PM
Points: 20,860, Visits: 32,883
Samuel Vella (2/26/2009)
mh, your solution is fine but when you test it with a lot of data against a cursor, the cursor is orders of magnitude faster

I adapted your query to fit some different test data


drop table sales
CREATE TABLE Sales (DayCount smallint, channel varchar(50), Sales money)
CREATE CLUSTERED INDEX ndx_DayCount ON Sales(DayCount)
go


DECLARE @DayCount smallint, @Sales money, @channel varchar(50)
SET @DayCount = 0
SET @Sales = 10
set @channel = 'a'

WHILE @DayCount < 10000
BEGIN
INSERT Sales VALUES (@DayCount,@channel, @Sales)
SET @DayCount = @DayCount + 1
SET @Sales = @Sales + 15
set @channel = case
when right(cast(@daycount as varchar), 1) = 0 then 'a'
when right(cast(@daycount as varchar), 1) = 1 then 'b'
when right(cast(@daycount as varchar), 1) = 2 then 'c'
when right(cast(@daycount as varchar), 1) = 3 then 'd'
when right(cast(@daycount as varchar), 1) = 4 then 'e'
when right(cast(@daycount as varchar), 1) = 5 then 'f'
when right(cast(@daycount as varchar), 1) = 6 then 'g'
when right(cast(@daycount as varchar), 1) = 7 then 'h'
when right(cast(@daycount as varchar), 1) = 8 then 'i'
when right(cast(@daycount as varchar), 1) = 9 then 'j'
end
END

select * from sales

with cte as
(
select *, Row_Number() OVER(PARTITION BY channel ORDER BY channel) as RowNum
from sales
)
select a.rownum, a.daycount,a.channel, a.sales,
SUM(b.sales ) - a.sales as Bal
from cte a
LEFT outer join cte b
on a.channel = b.channel and a.RowNum > B.RowNum
group by a.rownum, a.channel, a.daycount, a.sales
order by a.channel, a.daycount

CREATE TABLE #Sales (DayCount smallint, Channel varchar(50), Sales money, RunningTotal money)

DECLARE @RunningTotal money
declare @old_channel varchar(50)
SET @RunningTotal = 0
set @old_channel = ''

DECLARE rt_cursor CURSOR
FOR
SELECT DayCount, Channel, Sales
FROM Sales
order by channel, daycount
DECLARE @DayCount smallint, @Sales money, @channel varchar(50)
OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO @DayCount,@Channel, @Sales

WHILE @@FETCH_STATUS = 0
BEGIN
SET @RunningTotal = @RunningTotal + @Sales
INSERT #Sales VALUES (@DayCount,@channel, @Sales, @RunningTotal)
set @old_channel = @channel
FETCH NEXT FROM rt_cursor INTO @DayCount,@channel, @Sales
if @old_channel <> @channel set @runningtotal = 0

END

CLOSE rt_cursor
DEALLOCATE rt_cursor

SELECT * FROM #Sales ORDER BY channel,DayCount

DROP TABLE #Sales


Just an FYI, your cursor routine is going to be blown out of the water shortly. I don't know if Jeff's article has been rewritten yet, but if you want to see the type of code he is putting together I have another article on Running Totals that you may want to check out, as it does contain some of Jeff's code in it as well. You can read my article here.

I have a link to Jeff's article below in my signature block regarding Running Totals.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #667810
Posted Tuesday, March 3, 2009 8:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:23 PM
Points: 20,860, Visits: 32,883
Oh, wow. Looks like I may have beat Jeff. But of course, he is probably setting up a million row example to demonstrate his code, where I just took what was presented and went from there. Jeff's code will also be heavily commented as well.

--drop table #TempStk  
CREATE TABLE #TempStk
( [SEQ] INT ,
[ID] INT NOT NULL
,[Pricelist] nvarchar(300)
,[Status] NVARCHAR(50) NULL
,[Date] DATETIME
,[Quantity] INT
,[Balance] INT NULL
)

GO

INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(1,8100,'ACTIFED','Receipt','01/01/2004',100);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(2,8100,'ACTIFED','Issued','03/28/2004',5);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(3,8100,'ACTIFED','Issued','04/01/2004',5);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(4,8100,'ACTIFED','Issued','05/05/2004',5);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(5,8100,'ACTIFED','Issued','05/10/2004',5);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(6,8110,'AGIOLAX','Receipt','01/01/2004',50);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(7,8110,'AGIOLAX','Issued','02/12/2004',10);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(8,8110,'AGIOLAX','Receipt','03/28/2004',50);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(9,8110,'AGIOLAX','Issued','05/05/2004',25);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(10,8112,'OTRIVIN CHILD 0.5% NDrops','Receipt','01/01/2004',200);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(11,8112,'OTRIVIN CHILD 0.5% NDrops','Receipt','03/28/2004',50);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(12,8112,'OTRIVIN CHILD 0.5% NDrops','Issued','04/01/2004',15);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(13,8112,'OTRIVIN CHILD 0.5% NDrops','Issued','05/05/2004',10);
GO


select * from #TempStk

create clustered index IX_RunningBal on #TempStk (
ID,
Date);

declare @Balance int,
@ID int;
set @ID = 0;

update #TempStk set
@Balance = [Balance] = case when @ID <> ts.ID
then 0
else @Balance
end + (cast(case when [Status] = 'Receipt'
then 1
when [Status] = 'Issued'
then -1
end as int) * ts.[Quantity]),
@ID = ts.[ID]
from
#TempStk ts with (index = 1)

select * from #TempStk;




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #667813
Posted Tuesday, March 3, 2009 9:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
Lynn Pettis (3/3/2009)
Oh, wow. Looks like I may have beat Jeff. But of course, he is probably setting up a million row example to demonstrate his code, where I just took what was presented and went from there. Jeff's code will also be heavily commented as well.


Heh... you know me all to well, Brother Lynn! :D Although Mathew did a very good job of attaching sample data, the column names are quite a bit different than those posted and I also wanted to show a shorter way to post sample data. And, as you so very well put it, I was playing with my typical million row example and then saw you beat me to answering this. The only thing you forgot was the TABLOCKX to keep people out of the table while the update is occuring. :)

Anyway, here's the short, heavily commented version... do read the comments as they make a point about this method of updating...

--===== Conditional drop the temporary test table (just so we can rerun the test, if we want)
IF OBJECT_ID('TempDB..#TempStk','U') IS NOT NULL
DROP TABLE #TempStk

--===== Creat the temporary test table with a clustered PK on Seq
CREATE TABLE #TempStk
(
ID INT PRIMARY KEY CLUSTERED, --Can be an IDENTITY, as well
Code INT,
Name NVARCHAR(300),
Txn_Type NVARCHAR(50),
Date DATETIME,
Quantity FLOAT,
Balance FLOAT


--===== Populate the test table with data. Notice the data is pretty much out of order so
-- far as the clustered PK is concerned. If the ID can't be maintained in the correct
-- order, not to worry... put the clustered index on Code, Name, and Date. Notice I
-- said nothing about that having to be a PK. ;-)
INSERT INTO #TempStk
(ID, Code, Name, Txn_Type, Date, Quantity)
SELECT '13','8112','OTRIVIN CHILD 0.5% NDrops','Issued','May 5 2004 12:00AM','10' UNION ALL
SELECT '6','8110','AGIOLAX','Receipt','Jan 1 2004 12:00AM','50' UNION ALL
SELECT '1','8100','ACTIFED','Receipt','Jan 1 2004 12:00AM','100' UNION ALL
SELECT '3','8100','ACTIFED','Issued','Apr 1 2004 12:00AM','5' UNION ALL
SELECT '4','8100','ACTIFED','Issued','May 5 2004 12:00AM','5' UNION ALL
SELECT '10','8112','OTRIVIN CHILD 0.5% NDrops','Receipt','Jan 1 2004 12:00AM','200' UNION ALL
SELECT '5','8100','ACTIFED','Issued','May 10 2004 12:00AM','5' UNION ALL
SELECT '8','8110','AGIOLAX','Receipt','Mar 28 2004 12:00AM','50' UNION ALL
SELECT '9','8110','AGIOLAX','Issued','May 5 2004 12:00AM','25' UNION ALL
SELECT '11','8112','OTRIVIN CHILD 0.5% NDrops','Receipt','Mar 28 2004 12:00AM','50' UNION ALL
SELECT '7','8110','AGIOLAX','Issued','Feb 12 2004 12:00AM','10' UNION ALL
SELECT '12','8112','OTRIVIN CHILD 0.5% NDrops','Issued','Apr 1 2004 12:00AM','15' UNION ALL
SELECT '2','8100','ACTIFED','Issued','Mar 28 2004 12:00AM','5'

--===== Declare some obviously named variables
DECLARE @PrevID INT,
@PrevCode INT,
@PrevName NVARCHAR(300),
@PrevBal FLOAT

--===== Do the "quirky" update using a very high speed pseudo-cursor,
-- This is very similar to what you would do in a language like "C" except the
-- "Read a row/Write a row" is built into the update.
UPDATE #TempStk
SET @PrevBal = Balance = CASE
WHEN Code = @PrevCode
AND Name = @PrevName
THEN @PrevBal + (Quantity * CASE WHEN Txn_Type = 'Issued' THEN -1 ELSE 1 END)
ELSE Quantity * CASE WHEN Txn_Type = 'Issued' THEN -1 ELSE 1 END
END,
@PrevCode = Code,
@PrevName = Name,
@PrevID = ID --Just an "anchor", but gotta have it to guarantee things.
FROM #TempStk WITH (INDEX(0), TABLOCKX)

--===== Display the results
SELECT *
FROM #TempStk
ORDER BY ID

Now, just in case anyone gets any smart ideas about using the clustered index in SELECTs to create an "order" without an ORDER BY... don't bloody well do it. Even though it looks like it works, there are times when it doesn't and I'll have the proof in the article I'm rewritting. For now, just take my word for it... the "orderless" ordering ONLY works in the "quirky" UPDATE and ONLY when it's properly formed with all the goodies like the "anchor" and forcing the clustered index scan.

The TABLOCKX is to keep people from pulling off an update or delete of the data we're trying to make the running total for. You might think that's a bad thing, but consider this... this method will update a million rows in less than 7 seconds and because it's gonna do the whole table, guess what it's eventually gonna do during those 7 seconds... you guessed it... it's going to lock the whole table, so just get it out of the way and do it up front.

I've gotta say it one more time... if you want a correct order out of a SELECT, you MUST use an ORDER BY. But, for the "quirky" UPDATE we did, you can quite literally "bank" on it. ;)

If anyone wants to see the million row update to make a running total, you'll just have to wait until I can publish the rewritten article.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #667815
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse