My assumption is that there is a credit value of 50 for another invoice that has an ID value less than 8 and greater than 4.
I added
AND t.Invno = s.Invno
in the subquery's
where
clause to ensure only rows for the selected invoice are included in the subquery result.
I added
order by id
to ensure proper ordering of the result set.
select
s.id,s.date as Date,
s.invno as Invoice_No,
s.Debit as Db,
s.Credit as Cr,
(
select sum(Debit)-sum(Credit) from GL_Table t
where t.ID <= s.ID AND t.Invno = s.Invno
) AS Balance
from GL_Table s where invno = 123456 order by id
My test data:
CREATE TABLE [dbo].[GL_Table](
[ID] [int] NOT NULL,
[Date] [date] NOT NULL,
[InvNo] [int] NOT NULL,
[Debit] [int] NOT NULL,
[Credit] [int] NOT NULL
) ON [PRIMARY]
Go
insert into gl_table values (1,'2014-03-06',123456,0,400);
insert into gl_table values (2,'2014-03-06',123456,100,0);
insert into gl_table values (3,'2014-03-06',123456,50,0);
insert into gl_table values (4,'2014-03-17',123456,20,0);
insert into gl_table values (8,'2014-03-18',123456,30,0);
insert into gl_table values (7,'2014-03-18',123457,0,50);
Alternate Solution using Inner Join:
select
s.date as Date,
s.invno as Invoice_No,
s.Debit as Db,
s.Credit as Cr,
(sum(t.Debit)-sum(t.Credit)) as Balance
From GL_Table s inner join GL_Table t on s.invno=t.invno and t.id<=s.id
Where s.invno = 123456
Group by s.id,s.date,s.invno,s.debit,s.credit