account statement previously was a little bit wrong .. using identity instead of date in logic . this is correct

 USE [ForwardLive]

GO

/****** Object:  StoredProcedure [dbo].[s_GetStatement]    Script Date: 12/12/2021 10:17:31 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

alter PROCEDURE [dbo].[s_GetStatement2]  

@id NVARCHAR(100)  

AS  

BEGIN 

 

SET NOCOUNT ON;  

DECLARE

@EO_date NVARCHAR(50);  

CREATE TABLE #Temp  

(

sn int identity,  

id int,  

created_at DATEtime,  

channel NVARCHAR(50),  

dr money,  

cr money 

)  

CREATE CLUSTERED INDEX CLS_STATEMENT ON #Temp(id)  

CREATE TABLE #TEMP2  

(  

   sn int,  

id int,  

created_at DATEtime,  

channel NVARCHAR(50),  

dr money,  

cr money,  

BALANCE money

)  

   INSERT INTO #Temp 

   (

 

id ,  

created_at,  

channel,  

dr,  

cr  

   )   select S.id,S.created_at,channel,dr,cr from [Syn_Forward].mfin_forward.saving_account_details S WITH(NOLOCK)   

   JOIN [Syn_Forward].mfin_forward.account_opening ao WITH(NOLOCK) on S.account_opening_id=ao.id  

WHERE ao.id=@id 

ORDER BY S.created_at  

 

select top 1 @EO_date=created_at from #temp 

order by created_at desc  

select @EO_date= NepaliDate from tbl_NepaliDate where 

                     EnglishDate= 

CASE WHEN

@EO_date=EOMONTH(

                  (SELECT TOP 1 created_at from #temp order by created_at desc)

  )

THEN FORMAT(

            (DATEADD(DAY,1,@EO_date))

,'yyyy-MM-dd')

ELSE

FORMAT (

         (EOMONTH(@EO_date)

),'yyyy-MM-dd')

END 

 

----------------------<<STATEMENT AS PER DATE LESS THEN AND EQUALS TO DATES >>-------------------------

INSERT INTO #TEMP2 

(sn,

id,  

created_at ,  

channel ,  

dr ,  

cr ,  

BALANCE    

)

SELECT sn,id, created_at,channel,dr,cr  --ND.NepaliDate as

        ,(  

        ISNULL((SELECT SUM(dr+cr)  

                FROM #Temp a  

                WHERE a.sn<= AB.sn AND dr = 0),0) -  

   ISNULL((SELECT SUM(dr+cr)  

                FROM #Temp a  

                WHERE a.sn<= AB.sn AND cr = 0),0)  

      ) BALANCE     

FROM #Temp AB  

ORDER BY created_at 

--SELECT * FROM #TEMP2 ORDER BY created_at

-- ----------------------------<<Final Data with BALANCE FORWARD included>>----------------------------------------------

-- SELECT id,ND.NepaliDate AS created_at,ab.created_at AS ENGLISH,channel,dr,cr,BALANCE INTO #TEMP3 FROM #TEMP2 AB

-- INNER JOIN tbl_NepaliDate ND on ND.EnglishDate=AB.created_at--FORMAT(AB.created_at,'yyyy-MM-dd')  

-- ORDER BY ab.created_at DESC 

-- SELECT * FROM #TEMP3

-- UNION ALL  

-- SELECT NULL,@EO_date,(select top 1 created_at from #temp order by created_at desc),'BALANCE FORWARD',

-- ISNULL((SELECT SUM(dr) from #temp),0),

--   ISNULL((SELECT SUM(cr) from #temp),0),  

-- ((SELECT top 1 BALANCE from #TEMP2 order by created_at asc))   

--     --ORDER BY ab.created_at 

  

-- DROP TABLE #Temp 

-- DROP TABLE #TEMP2  

SELECT sn,id,ND.NepaliDate AS created_at,channel,dr,cr,BALANCE FROM #TEMP2 ab 

INNER JOIN tbl_NepaliDate ND on ND.EnglishDate=ab.created_at

UNION ALL  

SELECT ((select max(sn) from #TEMP )+1),NULL,@EO_date,--(select top 1 created_at from #temp order by created_at desc),

'BALANCE FORWARD',

ISNULL((SELECT SUM(dr) from #temp),0),

  ISNULL((SELECT SUM(cr) from #temp),0),  

((SELECT top 1 BALANCE from #TEMP2 order by created_at asc))

ORDER BY sn

END