Hi @Shahab a
For report1, try this:
;WITH CTE AS
(
SELECT ID,Rows,[DESC],Debit,Credit,C.*
FROM VoucheItem CROSS APPLY(VALUES(AdRef1,'AdRef1'),
(AdRef2,'AdRef2'),
(AdRef3,'AdRef3'),
(AdRef4,'AdRef4'))C(AdRef_Value,AdRef_Type)
)
SELECT ADId,ADDetail,[DESC],Debit,Credit,SUM(Debit-Credit)OVER(PARTITION BY ADId,ADDetail ORDER BY [DESC] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS Result
FROM AccountDetail A JOIN CTE C ON A.ADId=C.AdRef_Value
WHERE ADId=4000
For report2, check this:
;WITH CTE AS
(
SELECT ID,Rows,[DESC],Debit,Credit,C.*
FROM VoucheItem CROSS APPLY(VALUES(AdRef1,'AdRef1'),
(AdRef2,'AdRef2'),
(AdRef3,'AdRef3'),
(AdRef4,'AdRef4'))C(AdRef_Value,AdRef_Type)
)
SELECT ADId,ADDetail,SUM(Debit)Debit,SUM(Credit)Credit,SUM(Debit)-SUM(Credit)AS Result
FROM AccountDetail A JOIN CTE C ON A.ADId=C.AdRef_Value
WHERE ADId=4000
GROUP BY ADId,ADDetail
Best regards,
LiHong
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.