CREATE TABLE [dbo].[tbl_Company_Payment](
	[SL_No] [int] IDENTITY(1,1) NOT NULL,
	[Date] [date] NULL,
	[Company_Name] [nvarchar](50) NULL,
	[Payment] [int] NULL,
	[Bank_Name] [varchar](50) NULL,
	[Branch] [varchar](50) NULL,
	[Transfer_Bank] [varchar](50) NULL,
	[Transfer_Type] [varchar](50) NULL,
	[Note] [varchar](50) NULL
) ON [PRIMARY]
GO

insert into [dbo].[tbl_Company_Payment] values ('8/29/2021'	,'Hemas',	100000,	'BracK Bank',	'Banani-11,Dhaka',	'BCCB',	'BEFTN',	'n/a')
insert into [dbo].[tbl_Company_Payment] values ('8/31/2021',	'Hemas',	100000,	'BracK Bank',	'Gulsahn,Dahaka',	'BCCB','	BEFTN','	n/a')
insert into [dbo].[tbl_Company_Payment] values ('9/4/2021',	'Hemas'	,33620,	'Clm july 21',	'Clm july 21',	'Clm july 21',	'Clm july 21',	'Clm july 21')
insert into [dbo].[tbl_Company_Payment] values ('9/7/2021',	'Hemas',	100000	,'BracK Bank',	'Gulsahn,Dahaka',	'BCCB',	'BEFTN',	'n/a')
insert into [dbo].[tbl_Company_Payment] values ('9/12/2021',	'Hemas',	100000,	'BracK Bank',	'Gulsahn,Dahaka',	'BCCB',	'BEFTN',	'n/a')


CREATE TABLE [dbo].[tbl_Purchase](
	[SL_NO] [int] IDENTITY(1,1) NOT NULL,
	[Date] [date] NULL,
	[Company_Name] [nvarchar](50) NULL,
	[Product_Code] [nvarchar](50) NULL,
	[Product_Name] [nvarchar](50) NULL,
	[Purchase_Rate] [decimal](18, 2) NULL,
	[Sale_Rate] [decimal](18, 2) NULL,
	[MRP] [decimal](18, 2) NULL,
	[Production_Date] [date] NULL,
	[Expiry_Date] [date] NULL,
	[Batch_NO] [varchar](50) NULL,
	[Qty] [int] NULL,
	[Value] [decimal](18, 2) NULL,
	[Invoice_NO] [varchar](50) NULL
) ON [PRIMARY]
GO
insert into [dbo].[tbl_Purchase] values ('8/26/2021','Hemas',	'KHFO0100',	'HairFall_Kumarika 100Ml',	72.38,	76.54,	0,	'1/1/2021',	'1/1/2021',	'n/a',	192,	13896.96,	'2021-2022-000914')
insert into [dbo].[tbl_Purchase] values ('8/26/2021',	'Hemas',	'KADO200',	'AntiDanfruf_200Ml',	146.51,	155,	0,	'1/1/2021','	1/1/2021',	'n/a',	60,	8790.6,	'2021-2022-000914')
insert into [dbo].[tbl_Purchase] values ('8/31/2021',	'Hemas',	'KHBS100',	'Soap_Kumarika 100Gm',	32.28,	34.1,	0,	'1/1/2021',	'1/1/2021',	'n/a',	216,	6972.48,	'2021-2022-001050')
insert into [dbo].[tbl_Purchase] values ('8/31/2021',	'Hemas','	KHFO0100','	HairFall_Kumarika 100Ml',	72.38,	76.54,	0,'	1/1/2021',	'1/1/2021','	n/a',	192,	13896.96,	'2021-2022-001050')
insert into [dbo].[tbl_Purchase] values (	'9/2/2021',	'Hemas',	'EHO0100',	'Eva_Hair Oil-100ml'	,64.29,	68,	0,	'1/1/2021',	'1/1/2021',	'n/a',	180,	11572.2,	'2021-2022-001195')
insert into [dbo].[tbl_Purchase] values (	'9/2/2021',	'Hemas',	'EHO0200',	'Eva_Hair Oil-200ml'	,117.14,	124,	0,	'1/1/2021',	'1/1/2021',	'n/a',	108,	12651.12,	'2021-2022-001195')
insert into [dbo].[tbl_Purchase] values (	'9/2/2021',	'Hemas',	'KHFO0200',	'HairFall_Kumarika 200Ml(NP)',	146.66,	155,	0,	'1/1/2021',	'1/1/2021',	'n/a',	60,	8799.6,	'2021-2022-001195')



with Cte as (
select
null as Date,
null as Company_Name,
null as payement_Amount,
null as purchase_amouont,
 

blance=b.payement_Amount-a.purchase_amouont

from
(
select  Company_Name, sum(Value) As purchase_amouont from tbl_Purchase 
where Company_Name='Hemas'
and Date between '2011-03-13' and Dateadd(dd,-1,'2021-08-30')Group by Company_Name)a
full Join
(select Company_Name, sum(Payment ) as payement_Amount from tbl_Company_Payment
where Company_Name='hemas'
and Date between '2011-03-13' and Dateadd(dd,-1,'2021-08-30')
Group by Company_Name) b
on a.Company_Name=b.Company_Name
),

cte1 as
(
select date,Company_Name,Invoice_NO as note,sum(Value)as purchase_amouont,null As payement_Amount from tbl_Purchase
Group by date,Company_Name,Invoice_NO
union all
select Date,Company_Name,Note,null as purchase_amouont,sum(Payment)As payement_Amount  from tbl_Company_Payment
Group by Date,Company_Name,Note 



),

CTe3 as (select null as Date,'Previous Balance' as Company_Name, payement_Amount,null as purchase_amouont,blance,null as note from cte
union all
select Date,Company_Name,payement_Amount,purchase_amouont,sum(isnull(payement_Amount,0)-isnull(purchase_amouont,0)) OVER( ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Blance,note from cte1
where Company_Name='Hemas'
and Date between '2021-08-30' and '2021-12-13'

)
select * from CTe3
order by Date