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

Answered in .programming, please do not multipost.
Tom

<chintu4uin (AT) gmail (DOT) com> wrote