SQL Query to get Bank Statement As on Date

DECLARE @Transactions TABLE(

[Date] DateTime,

Remarks VARCHAR(100),

CRAmt DECIMAL(18,3),

DRAmt DECIMAL(18,3),

Balance DECIMAL(18,3),

[BalanceType] CHAR(2))

INSERT INTO @Transactions VALUES(GETDATE()-312,'Remark 1',100, NULL,NULL,NULL)

INSERT INTO @Transactions VALUES(GETDATE()-212,'Remark 2',NULL, 50,NULL,NULL)

INSERT INTO @Transactions VALUES(GETDATE()-12,'Remark 3', 200, NULL,NULL,NULL)

SELECT * FROM @Transactions

DECLARE @DrTotal AS DECIMAL(18,3) = 0

DECLARE @CrTotal AS DECIMAL(18,3) = 0

DECLARE @Balance AS DECIMAL(18,3) = 0

UPDATE @Transactions

SET @DrTotal = @DrTotal + ISNULL(DRAmt,0),

@CrTotal = @CrTotal + ISNULL(CRAmt,0),

@Balance = @Balance + ISNULL(DRAmt,0) - ISNULL(CRAmt,0),

Balance = ABS(@Balance),

[BalanceType] = CASE WHEN @Balance > 0 THEN 'Dr' ELSE 'Cr' END



SELECT [Date],Remarks, CRAmt , DRAmt , Balance , [BalanceType] FROM @Transactions

UNION ALL

SELECT GETDATE() [Date],'Total' Remarks

, @CrTotal CRAmt , @DrTotal DRAmt

, ABS(@Balance) Balance , CASE WHEN @Balance > 0 THEN 'Dr' ELSE 'Cr' END [BalanceType]