چیزی به نظرم نمبرسه که بگم جز اینکه فیلد تاریخ برای هر رکورد بایست متفاوت باشه.
اسکریپت زیر رو در sqlserver management studio تست کنید
USE [master];
IF EXISTS(SELECT * FROM sys.databases WHERE name=N'TestDB2')
BEGIN
ALTER DATABASE [TestDB2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
DROP DATABASE [TestDB2];
END
CREATE DATABASE [TestDB2];
USE [TestDB2];
CREATE TABLE [tbl_Kalaei](
[Id_Kalaei] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[NumSanad] [nvarchar](100) NULL,
[Kod_Moshtari] [int] NULL,
[Sharh] [nvarchar](400) NULL,
[Tedad] [int] NULL,
[Takhfif] [decimal](18, 0) NULL,
[PriceVahed] [decimal](18, 0) NULL,
[PasAzTakhfif] [decimal](18, 0) NULL,
[Bedehkar] [decimal](18, 0) NULL,
[Bestankar] [decimal](18, 0) NULL,
[Tarikh] [datetime2](7) NULL,
);
INSERT INTO [tbl_Kalaei] ([NumSanad], [Kod_Moshtari], [Sharh], [Tedad], [Takhfif], [PriceVahed], [PasAzTakhfif], [Bedehkar], [Bestankar], [Tarikh])
VALUES ('a','1','aaa',10,0,100,1000,1000,0,SYSDATETIME()) ;
INSERT INTO [tbl_Kalaei] ([NumSanad], [Kod_Moshtari], [Sharh], [Tedad], [Takhfif], [PriceVahed], [PasAzTakhfif], [Bedehkar], [Bestankar], [Tarikh])
VALUES ('b','1','bbb',1,0,15,15,0,10,SYSDATETIME());
INSERT INTO [tbl_Kalaei] ([NumSanad], [Kod_Moshtari], [Sharh], [Tedad], [Takhfif], [PriceVahed], [PasAzTakhfif], [Bedehkar], [Bestankar], [Tarikh])
VALUES ('c','1',N'کالای : ccc',5,0,20,100,70,0,SYSDATETIME());
INSERT INTO [tbl_Kalaei] ([NumSanad], [Kod_Moshtari], [Sharh], [Tedad], [Takhfif], [PriceVahed], [PasAzTakhfif], [Bedehkar], [Bestankar], [Tarikh])
VALUES ('d','1','ddd',3,0,70,210,60,0,SYSDATETIME());
INSERT INTO [tbl_Kalaei] ([NumSanad], [Kod_Moshtari], [Sharh], [Tedad], [Takhfif], [PriceVahed], [PasAzTakhfif], [Bedehkar], [Bestankar], [Tarikh])
VALUES ('e','2','eee',8,0,30,240,200,0,SYSDATETIME());
INSERT INTO [tbl_Kalaei] ([NumSanad], [Kod_Moshtari], [Sharh], [Tedad], [Takhfif], [PriceVahed], [PasAzTakhfif], [Bedehkar], [Bestankar], [Tarikh])
VALUES ('f','2','fff',5,0,100,1000,0,200,SYSDATETIME());
INSERT INTO [tbl_Kalaei] ([NumSanad], [Kod_Moshtari], [Sharh], [Tedad], [Takhfif], [PriceVahed], [PasAzTakhfif], [Bedehkar], [Bestankar], [Tarikh])
VALUES ('g','2',N'کالای : ggg',10,0,100,1000,1000,0,SYSDATETIME());
select
C.* ,
(CASE WHEN Mande>0 THEN N'بدهکار'
WHEN Mande<0 THEN N'طلبکار'
ELSE N'تسویه'
END) as Vaziat
from
(
select
B.* ,
(B.RunningTotalBedehkari - B.RunningTotalBestankari) as Mande
From
(
SELECT
A.*,
RunningTotalBedehkari = (SELECT SUM(Bedehkar)
FROM dbo.tbl_Kalaei
WHERE tbl_Kalaei.Kod_Moshtari = A.Kod_Moshtari
AND tbl_Kalaei.Tarikh <= A.Tarikh
AND Sharh not like N'کالای :%'),
RunningTotalBestankari = (SELECT SUM(Bestankar)
FROM dbo.tbl_Kalaei
WHERE tbl_Kalaei.Kod_Moshtari = A.Kod_Moshtari
AND tbl_Kalaei.Tarikh <= A.Tarikh
AND Sharh not like N'کالای :%')
FROM tbl_Kalaei AS A
WHERE Sharh not like N'کالای :%'
) AS B
) C
union
select
* ,
RunningTotalBedehkari = null ,
RunningTotalBestankari = null ,
Mande = null ,
Vaziat = null
from tbl_Kalaei
where Sharh like N'کالای :%'
ORDER BY Kod_Moshtari , Tarikh