【SQL开源代码栏目提醒】:网学会员SQL开源代码为您提供GetAccItemBalance.sql参考,解决您在GetAccItemBalance.sql学习中工作中的难题,参考学习。
CREATE FUNCTION GetAccItemBalance
(
@EndVoucDate Char(8)
)
RETURNS @VoucherReport TABLE
(
AccItemID Char( 9) NOT NULL,
AccItemName Char(30) NOT NULL,
DRAmount int NULL,
CRAmount int NULL,
DRBalance int NULL,
CRalance int NULL
)
AS
BEGIN
INSERT INTO @VoucherReport
SELECT D.AccItemID, A.AccItemName,
SUM(DRAmount), SUM(CRAmount),
'DRBalance' =
CASE
WHEN SUM(DRAmount) > SUM(CRAmount) THEN SUM(DRAmount - CRAmount) ELSE 0 END,
'CRBalance' =
CASE
WHEN SUM(CRAmount) > SUM(DRAmount) THEN SUM(CRAmount - DRAmount) ELSE 0 END
FROM VoucMaster M, VoucDetail D, AccItem A
WHERE M.VoucDate <= @EndVoucDate
AND A.AccItemID <> '3229.0000'
AND M.VoucID = D.VoucID
AND D.AccItemID = A.AccItemID
GROUP BY D.AccItemID, A.AccItemName
ORDER BY D.AccItemID
RETURN
END