Need Help for Query on Summing Prev Row Value and used for calculation [Programming SQL]

Prev: How to get consecutive record number in two select statement
Next: protecting loop in racursive

From: chintu4uin on 3 Aug 2010 04:07

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

From: chintu4uin on 3 Aug 2010 11:33

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

From: chintu4uin on 3 Aug 2010 11:47

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

From: Tom Cooper on 3 Aug 2010 14:11

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

<chintu4uin(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

From: chintu4uin on 3 Aug 2010 23:31

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

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