Prev: How to get consecutive record number in two select statement
Next: protecting loop in racursive
From: Tom Cooper on 4 Aug 2010 01:01
Calculating running totals is not something that SQL Server does very
efficiently. I would suggest doing it with a CLR routine, or in your front
end code, or in a cursor. One of the first two is probably perferable.
Eveen though the cursor will be faster than a set based query, for the
number of rows you would have for an inventory transaction history, the
cursor will probably be too slow.

Tom

<chintu4uin(a)gmail.com> wrote in message
news:2f19113f-bad1-4851-ab3e-8ef359444be6(a)t5g2000prd.googlegroups.com...
Hi Tom Cooper thnx for the reply I used the following query to
generate my desired output
--------------------
select
RowId, item, trntype,
(Select ISNULL(SUM(i.OpnBal + i.QtyMfg - i.QtyUsed),o.OpnBal) from
#RPT_RG1 i where i.RowId<=o.RowId-1 and i.Item = o.Item) opnBal,
qtyMfg, qtyUsed,
(Select SUM(i.OpnBal + i.QtyMfg - i.QtyUsed) from #RPT_RG1 i where
i.RowId<=o.RowId and i.Item = o.Item) CloseBal
FROM #RPT_RG1 o
Order by Item,RowId
--------------------
But the thing as you said it is fine if the data is in small no but it
take time if huge data is there.
Actually this is the dummy scenario which I created my actuall query
consist of multiple joins and union and I m using derived tables and
on that output I used above query to get my desired output so in that
case to fetch just 8250 dummy rows it is taking 18-20sec.Then when
actuall my live Inventory data comes then will this affect
performance?
So what you suggest to use this or to use cursor or some other
alternative.

I am using the below subquery as mention above three time in select to
calculate 3 different column value so this is also a time consuming or
what?
---------------------------------------------------------------------------------------
(Select ISNULL(SUM(i.OpnBal + i.QtyMfg - i.QtyUsed),o.OpnBal) from
#RPT_RG1 i where i.RowId<=o.RowId-1 and i.Item = o.Item)
---------------------------------------------------------------------------------------

Please frnds give your suggestion to optimize this...thanx
On Aug 3, 11:11 pm, "Tom Cooper" <tomcoo...(a)comcast.net> wrote:
> A nice simple direct way is
>
> Select r1.RowId, r1.Item, r1.TrnType, r1.OpnBal, r1.QtyMfg, r1.QtyUsed,
> (Select Sum(r2.OpnBal + r2.QtyMfg - r2.QtyUsed) As CloseBal
> From RPT_RG1 r2
> Where r1.Item = r2.Item And r2.RowId <= r1.RowId)
> From RPT_RG1 r1
> Order By r1.Item, r1.RowId;
>
> That should work fine if you have a small number of rows. But it will be
> very slow if you have a large number of rows. If you have a large number
> of
> rows, either do running totals like this in the front end or use a cursor.
> Cursor's are usually slower than set based solutions, but not in this
> case.
> So something like
>
> Declare @Result Table(
> [RowId] [numeric](18, 0) NOT NULL,
> [Item] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [TrnType] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [OpnBal] [numeric](18, 0) NOT NULL,
> [QtyMfg] [numeric](18, 0) NOT NULL,
> [QtyUsed] [numeric](18, 0) NOT NULL,
> [CloseBal] [numeric](18, 0) NOT NULL,
> Primary Key (Item, RowId));
>
> Declare InventoryCursor Cursor Local Fast_Forward
> For Select r1.RowId, r1.Item, r1.TrnType, r1.OpnBal, r1.QtyMfg, r1.QtyUsed
> From RPT_RG1 r1
> Order By r1.Item, r1.RowId;
>
> Declare @RowId numeric(18, 0),
> @Item varchar(6),
> @TrnType varchar(1),
> @OpnBal numeric(18, 0),
> @QtyMfg numeric(18, 0),
> @QtyUsed numeric(18, 0),
> @CloseBal numeric(18,0),
> @OldItem varchar(6);
>
> Open InventoryCursor;
>
> Set @OldItem = Null;
> Set @CloseBal = 0;
> Fetch InventoryCursor Into @RowId, @Item, @TrnType, @OpnBal, @QtyMfg,
> @QtyUsed;
> Set @OldItem = @Item;
> Set @CloseBal = 0;
> While @@FETCH_STATUS = 0
> Begin
> If @OldItem <> @Item
> Begin
> Set @OldItem = @Item;
> Set @CloseBal = 0;
> End
> Set @CloseBal = @CloseBal + @OpnBal + @QtyMfg - @QtyUsed
> Insert @Result (RowId, Item, TrnType, OpnBal, QtyMfg, QtyUsed, CloseBal)
> Values (@RowId, @Item, @TrnType, @OpnBal, @QtyMfg, @QtyUsed,
> @CloseBal);
> Fetch InventoryCursor Into @RowId, @Item, @TrnType, @OpnBal, @QtyMfg,
> @QtyUsed;
> End
>
> Close InventoryCursor;
> Deallocate InventoryCursor;
>
> Select r1.RowId, r1.Item, r1.TrnType, r1.OpnBal, r1.QtyMfg, r1.QtyUsed,
> r1.CloseBal
> From @Result r1
> Order By r1.Item, r1.RowId;
>
> Tom
>
> <chintu4...(a)gmail.com> wrote in message
>
> news:e52caf22-c7f1-494b-8a64-097bd2b8448c(a)c10g2000yqi.googlegroups.com...
> I tried with the below Query but from 3rd row onwards it is not
> working properly please frnds help me.
> ****************************************************************************************************
> ;WITH CTE AS (
> SELECT
> RowId,
> Item,
> TrnType,
> OpnBal as OpnBal,
> QtyMfg,
> QtyUsed,
> (OpnBal+QtyMfg-QtyUsed) as CloseBal
> FROM RPT_RG1
> )
>
> --SELECT * FROM CTE
>
> Select
> Cur.RowId,
> Cur.Item,
> Cur.TrnType,
> (isnull(Cur.OpnBal,0)+isnull(Prv.CloseBal,0)) as OpnBal,
> ((isnull(Cur.OpnBal,0)+isnull(Prv.CloseBal,0))+Cur.QtyMfg-Cur.QtyUsed)
> as CloseBal
> from
> CTE Cur Left Join CTE Prv On
> Cur.RowId =Prv.RowId+1 and
> Cur.Item=Prv.Item
> Order by Cur.Item,Cur.RowId
> ****************************************************************************************************
>
> On Aug 3, 8:33 pm, "chintu4...(a)gmail.com" <chintu4...(a)gmail.com>
> wrote:
>
> > Consider this Table Structure
> > ****************************************************************************************************
> > CREATE TABLE [dbo].[RPT_RG1](
> > [RowId] [numeric](18, 0) NOT NULL,
> > [Item] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> > [TrnType] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> > [OpnBal] [numeric](18, 0) NOT NULL CONSTRAINT [DF_RPT_RG1_OpnBal]
> > DEFAULT ((0)),
> > [QtyMfg] [numeric](18, 0) NOT NULL CONSTRAINT [DF_RPT_RG1_QtyMfg]
> > DEFAULT ((0)),
> > [QtyUsed] [numeric](18, 0) NOT NULL CONSTRAINT [DF_RPT_RG1_QtyUsed]
> > DEFAULT ((0)),
> > [CloseBal] [numeric](18, 0) NOT NULL CONSTRAINT
> > [DF_RPT_RG1_CloseBal] DEFAULT ((0)),
> > CONSTRAINT [PK_RPT_RG1] PRIMARY KEY CLUSTERED
> > (
> > [RowId] ASC,
> > [Item] ASC
> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> > = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> > ) ON [PRIMARY]
>
> > insert into RPT_RG1 values(0,'A','',10,0,0,0)
> > insert into RPT_RG1 values(1,'A','R',0,2,0,0)
> > insert into RPT_RG1 values(2,'A','R',0,8,0,0)
> > insert into RPT_RG1 values(3,'A','I',0,0,7,0)
> > insert into RPT_RG1 values(4,'A','I',0,0,5,0)
>
> > insert into RPT_RG1 values(0,'B','',50,0,0,50)
> > insert into RPT_RG1 values(1,'B','I',0,0,15,0)
> > insert into RPT_RG1 values(2,'B','R',0,8,0,0)
> > insert into RPT_RG1 values(3,'B','I',0,0,7,0)
> > insert into RPT_RG1 values(4,'B','I',0,0,5,0)
>
> > select * from RPT_RG1 order by Item,RowID
>
> > Final Output
> > ------------------
> > RowID Item TrnType OpnBal QtyMfg QtyUsed CloseBal
> > 0 A 10 0 0 10
> > 1 A R 10 2 0 12
> > 2 A R 12 8 0 20
> > 3 A I 20 0 7 13
> > 4 A I 13 0 5 8
> > 0 B 50 0 0 50
> > 1 B I 50 0 15 35
> > 2 B R 35 8 0 43
> > 3 B I 43 0 7 36
> > 4 B I 36 0 5 31
> > ****************************************************************************************************
>
> > On Aug 3, 1:07 pm, "chintu4...(a)gmail.com" <chintu4...(a)gmail.com>
> > wrote:
>
> > > Hello frnds need a help for the query where in I want to add /
> > > subtract QtyMfg or QtyUsed as
> > > as per TrnType "R" or "I" starting with OpnBal which is in first row
> > > with RowId=0 for a Item
> > > then in CloseBal for the RowId=0 it will same then.CloseBal of RowId
> > > will become OpnBal for
> > > (RowId+1) the CloseBal of previous Row will become the Opnbal of the
> > > Next Row for same Item.
> > > Reset ablove process when ItemChanges.
>
> > > The below is Table structure.Please frnd can anybody help me for the
> > > same.
>
> > > CREATE TABLE [dbo].[RPT_RG1](
> > > [RowId] [numeric](18, 0) NOT NULL,
> > > [Item] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> > > [TrnType] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> > > [OpnBal] [numeric](18, 0) NOT NULL CONSTRAINT [DF_RPT_RG1_OpnBal]
> > > DEFAULT ((0)),
> > > [QtyMfg] [numeric](18, 0) NOT NULL CONSTRAINT [DF_RPT_RG1_QtyMfg]
> > > DEFAULT ((0)),
> > > [QtyUsed] [numeric](18, 0) NOT NULL CONSTRAINT [DF_RPT_RG1_QtyUsed]
> > > DEFAULT ((0)),
> > > [CloseBal] [numeric](18, 0) NOT NULL CONSTRAINT
> > > [DF_RPT_RG1_CloseBal] DEFAULT
>
> > > ((0)),
> > > CONSTRAINT [PK_RPT_RG1] PRIMARY KEY CLUSTERED
> > > (
> > > [RowId] ASC,
> > > [Item] ASC
> > > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> > > = OFF,
>
> > > ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> > > ) ON [PRIMARY]
>
> > > insert into RPT_RG1 values(0,'A','',10,0,0,10)
> > > insert into RPT_RG1 values(1,'A','R',10,2,0,12)
> > > insert into RPT_RG1 values(2,'A','R',12,8,0,20)
> > > insert into RPT_RG1 values(3,'A','I',20,0,7,13)
> > > insert into RPT_RG1 values(4,'A','I',13,0,5,8)
>
> > > insert into RPT_RG1 values(0,'B','',50,0,0,50)
> > > insert into RPT_RG1 values(1,'B','I',50,0,15,35)
> > > insert into RPT_RG1 values(2,'B','R',35,8,0,43)
> > > insert into RPT_RG1 values(3,'B','I',43,0,7,36)
> > > insert into RPT_RG1 values(4,'B','I',36,0,5,31)
>
> > > select * from RPT_RG1 order by Item,RowID
>
> > > Final Output
> > > -------------------
> > > RowID Item TrnType OpnBal QtyMfg QtyUsed CloseBal
> > > 0 A 10 0 0 10
> > > 1 A R 10 2 0 12
> > > 2 A R 12 8 0 20
> > > 3 A I 20 0 7 13
> > > 4 A I 13 0 5 8
> > > 0 B 50 0 0 50
> > > 1 B I 50 0 15 35
> > > 2 B R 35 8 0 43
> > > 3 B I 43 0 7 36
> > > 4 B I 36 0 5 31

First  |  Prev  | 
Pages: 1 2
Prev: How to get consecutive record number in two select statement
Next: protecting loop in racursive