I have two tables. One purchase and the other payment. I want to create a ledger table using SQL Query. I mentioned below what I did.
The problem is - I don't know how to add or subtract the current balance with the previous balance.
I would have benefited if an expert had wasted his precious time and collaborated on the solution. Thanks
CREATE TABLE [dbo].[tbl_Company_Payment](
[SL_No] [int] IDENTITY(1,1) NOT NULL,
[Date] [date] NULL,
[Company_Name] [nvarchar](150) NULL,
[Payment] [int] NULL,
[Bank_Name] [varchar](150) NULL,
[Branch] [varchar](150) NULL,
[Transfer_Bank] [varchar](150) NULL,
[Transfer_Type] [varchar](50) NULL,
[Note] [varchar](150) NULL
) ON [PRIMARY]
GO
insert into [dbo].[tbl_Company_Payment] values ('5/2/21','Emami',1200,'NCC','Dhaka','ABC','Online','n/a')
insert into [dbo].[tbl_Company_Payment] values ('5/3/21','Godrej',1100,'NCC','Dhaka','ABC','Online','n/a')
insert into [dbo].[tbl_Company_Payment] values ('5/4/21','Emami',1800,'NCC','Dhaka','ABC','Online','n/a')
insert into [dbo].[tbl_Company_Payment] values ('5/5/21','Emami',1400,'NCC','Dhaka','ABC','Online','n/a')
insert into [dbo].[tbl_Company_Payment] values ('5/6/21','Emami',1300,'NCC','Dhaka','ABC','Online','n/a')
insert into [dbo].[tbl_Company_Payment] values ('5/7/21','Emami',1900,'NCC','Dhaka','ABC','Online','n/a')
insert into [dbo].[tbl_Company_Payment] values ('5/8/21','Emami',2100,'NCC','Dhaka','ABC','Online','n/a')
insert into [dbo].[tbl_Company_Payment] values ('5/9/21','Godrej',800,'NCC','Dhaka','ABC','Online','n/a')
insert into [dbo].[tbl_Company_Payment] values ('5/10/21','Emami',500,'NCC','Dhaka','ABC','Online','n/a')
insert into [dbo].[tbl_Company_Payment] values ('5/11/21','Godrej',200,'NCC','Dhaka','ABC','Online','n/a')
insert into [dbo].[tbl_Company_Payment] values ('5/12/21','Emami',3200,'NCC','Dhaka','ABC','Online','n/a')
insert into [dbo].[tbl_Company_Payment] values ('5/13/21','Godrej',900,'NCC','Dhaka','ABC','Online','n/a')
CREATE TABLE [dbo].[tbl_purchase](
[Sl_NO] [int] IDENTITY(1,1) NOT NULL,
[Date] [date] NULL,
[Company_Name] [nvarchar](150) NULL,
[Product_Name] [nvarchar](150) NULL,
[Purchase_Rate] [decimal](18, 2) NULL,
[Qty] [int] NULL,
[Value] [decimal](18, 2) NULL,
[Invoice_NO] [varchar](50) NULL
) ON [PRIMARY]
GO
insert into [dbo].[tbl_purchase] values ('5/2/21','Emami','x1',25,20,500,'e-123')
insert into [dbo].[tbl_purchase] values ('5/2/21','Emami','x2',20,20,400,'e-123')
insert into [dbo].[tbl_purchase] values ('5/2/21','Emami','x3',10,30,300,'e-123')
insert into [dbo].[tbl_purchase] values ('5/2/21','Emami','x4',5,10,50,'e-123')
insert into [dbo].[tbl_purchase] values ('5/2/21','Emami','x5',15,20,300,'e-123')
insert into [dbo].[tbl_purchase] values ('5/5/21','Emami','x1',25,20,500,'e-124')
insert into [dbo].[tbl_purchase] values ('5/5/21','Emami','x2',20,20,400,'e-124')
insert into [dbo].[tbl_purchase] values ('5/5/21','Emami','x3',10,30,300,'e-124')
insert into [dbo].[tbl_purchase] values ('5/6/21','Emami','x4',5,10,50,'e-129')
insert into [dbo].[tbl_purchase] values ('5/6/21','Emami','x5',15,20,300,'e-129')
insert into [dbo].[tbl_purchase] values ('5/6/21','Godrej','y1',8,20,160,'g-001')
insert into [dbo].[tbl_purchase] values ('5/6/21','Godrej','y2',10,20,200,'g-001')
insert into [dbo].[tbl_purchase] values ('5/6/21','Godrej','y3',20,20,400,'g-001')
insert into [dbo].[tbl_purchase] values ('5/8/21','Godrej','y1',8,10,80,'g-009')
insert into [dbo].[tbl_purchase] values ('5/8/21','Godrej','y2',10,2,20,'g-009')
insert into [dbo].[tbl_purchase] values ('5/8/21','Godrej','y3',20,10,200,'g-009')
What I have tried:
With CTE As
(SELECT Date,
Company_Name,
sum(isnull(Payment,0)) as payment,
null as purchase_value,
Note as Description
FROM tbl_Company_Payment
group by Date,Company_Name, Note
UNION ALL
SELECT Date,
Company_Name,
null as payment,
sum(isnull(Value,0)) as purchase_value,
Invoice_NO as Description
FROM tbl_purChase
group by Date,Company_Name, Invoice_NO),
CTE1 AS(
SELECT Date,
Company_Name,
sum(isnull(Payment,0)) as payment,
null as purchase_value,
Note as Description
FROM tbl_Company_Payment
group by Date,Company_Name, Note
UNION ALL
SELECT Date,
Company_Name,
null as payment,
sum(isnull(Value,0)) as purchase_value,
Invoice_NO as Description
FROM tbl_purChase
group by Date,Company_Name, Invoice_NO)
SELECT null as Date,
'Last Balance' As Company_Name,
null as payment,
null as purchase_value,
sum(isnull(payment,0)-isnull(purchase_value,0)) AS Balance,
null as description
FROM CTE
where Company_Name ='emami'
and date between '2021-05-02' and '2021-05-03'
union all
SELECT Date,
Company_Name,
payment,
purchase_value,
SUM(isnull(payment,0) - isnull(purchase_value,0)) OVER (PARTITION BY [Company_Name] ORDER BY [Date]) AS Balance,
description
FROM CTE1
where Company_Name ='emami'
and date between '2021-05-03' and '2021-05-13'
ORDER BY Date