Calculation Help

1

Need to modify this query to taken previous Closing Blance- Next record Opening Balance and result create as new column nam baldiff please help Some one
imageselect top 6500 T.ID,t.Amount, t.Description,
c.TransTypeDesc, t.OpeningCashBalance,
ClosingCashBalance, OpeningBonusBalance,
ClosingBonusBalance,CeatedDate

from [Transaction] t ,TransactionTypes c

where t.TransactionTypeId=c.TransTypeID and

t.CustomerId = 27498 order by id desc;

2

You can use LAG | Microsoft Learn and LEAD | Microsoft Learn function to accomplish the requerements. As you didn't provide any sample data I cannot test it but it would look like something like this:

SELECT top 6500 
	t.id,
	t.Amount, 
	t.[Description],
	c.TransTypeDesc, 
	t.OpeningCashBalance,
	ClosingCashBalance, 
	OpeningBonusBalance,
	ClosingBonusBalance,
	CeatedDate,
	LAG(OpeningCashBalance, 1, 0) OVER (ORDER BY id DESC) AS PrevOpeningCashBalance,
	LEAD(ClosingCashBalance, 1, 0) OVER (ORDER BY id DESC) AS NextClosingCashBalance
FROM [Transaction] t
	INNER JOIN TransactionTypes c
		ON t.TransactionTypeId=c.TransTypeID 
WHERE t.CustomerId = 27498 
ORDER BY t.id DESC;
1 Like
3

Hi sir thank you very much for the reply i need get ouput as like this could you please kindly check this

image
image1064×219 6 KB

4
OpeningCashBalance ClosingCashBalance
14 -7986
54 14
94 54
14 94
54 14
94 54
134 94
56 134
96 56
5

If you provide a working example with sample data I would be able to help you more specific. This is an example I created based on the image you have provided for the first two columns:

DECLARE @OpeningCashbalance TABLE
(
ID INT,
OpeningCashbalance INT
)

INSERT INTO @OpeningCashbalance
SELECT 1,14
UNION
SELECT 2,54
UNION
SELECT 3,94
UNION
SELECT 4,14
UNION
SELECT 5,54
UNION
SELECT 6,94
UNION
SELECT 7,134
UNION
SELECT 8,56
UNION
SELECT 9,96
UNION
SELECT 10,136

SELECT ID, OpeningCashbalance, LAG(OpeningCashbalance,1,-7986) OVER (ORDER BY ID) AS ClosingCashbalance
FROM @OpeningCashbalance
ORDER BY ID