Failing to create balance sheet through SQL server

Question

Failing to create balance sheet through SQL server

jewel 1,231

I have a table tbl_Purchase with date, company_name, product_name, purchase_rate, qty, invoice_No. Other tables tbl_Company_Payment, have fields called Date, Company_Name, Payment, Note.
The problem is that the cash is not being adjusted with the previous balance.
The second problem is that I want to write the ‘previous balance’ text in the date column instead of the company name. Is it possible?
I seek the cooperation of experts.
And thanks in advance.

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-11-11')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-11-11')
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-11-11' and '2021-12-13'

)
select * from CTe3
order by Date

EchoLiu-MSFT 14,626 Reputation points

2021-12-21T02:10:34.98+00:00

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.
jewel 1,231 Reputation points

2021-12-21T08:48:11.077+00:00

Sorry, I couldn't write in the comments box, I wrote in the answer box
Sorry, I couldn't write in the comments box, I wrote in the answer box
jewel 1,231 Reputation points

2021-12-21T08:48:29.303+00:00

Sorry, I couldn't write in the comments box, I wrote in the answer box
Sorry, I couldn't write in the comments box, I wrote in the answer box
jewel 1,231 Reputation points

2021-12-21T09:04:16.23+00:00

159293-new-text-document-5.txt
EchoLiu-MSFT 14,626 Reputation points

2021-12-22T02:43:41.28+00:00

It doesn't matter, there is a character limit in the comment box. I saw your update and I am looking for a solution.

Answer accepted by question author

1 additional answer

Your answer

EchoLiu-MSFT 14,626 Reputation points

2021-12-21T02:10:34.98+00:00

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.
jewel 1,231 Reputation points

2021-12-21T08:48:11.077+00:00

Sorry, I couldn't write in the comments box, I wrote in the answer box
Sorry, I couldn't write in the comments box, I wrote in the answer box
jewel 1,231 Reputation points

2021-12-21T08:48:29.303+00:00

Sorry, I couldn't write in the comments box, I wrote in the answer box
Sorry, I couldn't write in the comments box, I wrote in the answer box
jewel 1,231 Reputation points

2021-12-21T09:04:16.23+00:00

159293-new-text-document-5.txt
EchoLiu-MSFT 14,626 Reputation points

2021-12-22T02:43:41.28+00:00

It doesn't matter, there is a character limit in the comment box. I saw your update and I am looking for a solution.

Answer 1

There are several problems with your code.

This should get you started on the right track

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')


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')


;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,
-1 as [id]
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, 'Purchase' + CAST(MIN([SL_NO]) AS VARCHAR(10)) as [id] 
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 , 'Pay' + CAST(MIN([SL_No]) AS VARCHAR(10)) as [id] 
from tbl_Company_Payment
Group by Date,Company_Name,Note



),

CTe3 as (
select 
    '1900-01-01' as Date,
    'Previous Balance' as Company_Name,
    CASE WHEN blance > 0 THEN blance ELSE NULL END AS payement_Amount,
    CASE WHEN blance < 0 THEN blance ELSE NULL END as purchase_amouont,
    null as note,
    'Begin' AS [Id]
from cte
union all
select 
    Date,
    Company_Name,
    payement_Amount,
    purchase_amouont,
    note,
    [id]
    from cte1
where Company_Name='Hemas'
and Date between '2021-08-30' and '2021-12-13'
),
CTe4 as (
select * ,
    sum(isnull(payement_Amount,0)-isnull(purchase_amouont,0)) OVER(ORDER BY [Date],[id]) as Blance
from CTe3
)
SELECT *
FROM CTe4
order by Date,[id]

Answer 2

CREATE TABLE [dbo].[tbl_Purchase](
[SL_NO] [int] IDENTITY(1,1) NOT NULL,
[Date] [date] NULL,
[Company_Name] nvarchar NULL,
[Product_Code] nvarchar NULL,
[Product_Name] nvarchar 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 NULL,
[Qty] [int] NULL,
[Value] [decimal](18, 2) NULL,
[Invoice_NO] varchar 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')

CREATE TABLE [dbo].[tbl_Company_Payment](
[SL_No] [int] IDENTITY(1,1) NOT NULL,
[Date] [date] NULL,
[Company_Name] nvarchar NULL,
[Payment] [int] NULL,
[Bank_Name] varchar NULL,
[Branch] varchar NULL,
[Transfer_Bank] varchar NULL,
[Transfer_Type] varchar NULL,
[Note] varchar 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')

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

EchoLiu-MSFT 14,626 Reputation points

2021-12-22T02:55:15.083+00:00

Can you provide the expected output of your test data?
jewel 1,231 Reputation points

2021-12-22T04:43:56.19+00:00

Unable to reconcile subsequent data with previous balance
EchoLiu-MSFT 14,626 Reputation points

2021-12-22T05:51:53.477+00:00

This is the output of your current code. I need to know the output you expect, because I don't know your business area, so maybe something that is common sense to you, but it is confusing to me.

Share via

Failing to create balance sheet through SQL server

1 additional answer

Your answer