USE [Pizza_Financial]
GO
/****** Object:  UserDefinedFunction [dbo].[get_accntbalance]    Script Date: 07/04/2012 11:24:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[get_accntbalance](@accntid as bigint)
RETURNS Money
WITH EXECUTE AS CALLER

AS
BEGIN
	declare @debitbalance as money
	declare @creditbalance as money
	declare @remainingbalance as money
	
	set @debitbalance=(select sum(debit_amt)
	from       tbl_account_transactions_detail
	where debit_accnt_ID=@accntid)

	set @creditbalance=(select sum(credit_amt)
	from       tbl_account_transactions_detail
	where credit_accnt_ID=@accntid)
	
	set @remainingbalance=@debitbalance-@creditbalance
	     RETURN(@remainingbalance);
END;

USE [Pizza_Financial]
GO
/****** Object:  StoredProcedure [dbo].[Show_All_Accnts_byType]    Script Date: 07/04/2012 11:23:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============================================
-- Author:	<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Show_All_Accnts_byType]
@accntcategoryID as int,
@accntypeid as bigint,
@isaccnt_current as bit

AS
  BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  if(@accntypeid<>'') and (@accntcategoryID<>'')
	begin
	SELECT * FROM tbl_accountlist al
		
	INNER JOIN tbl_accnt_type at
	ON at.accnttypename=(select at.accnttypename where at.accnttypeid=al.AccountType)
		
	WHERE (al.Accntstatus=1 and al.AccountCategoryID=@accntcategoryID and al.AccountType= @accntypeid and al.IsCurrent=@isaccnt_current and al.IsSubAccnt=0)
ORDER BY AccntNo ASC
 end
END

USE Pizza_Financial
GO
SELECT *

FROM tbl_accountlist al

INNER JOIN tbl_accnt_type at
ON at.accnttypename=(select at.accnttypename where at.accnttypeid=al.AccountType)

outer apply
(
select sum(credit_amt) as Credit
from tbl_account_transactions_detail
where credit_accnt_ID=al.AccountID
)CREDIT

outer apply
(
select sum(debit_amt) as Debit
from tbl_account_transactions_detail
where debit_accnt_ID=al.AccountID
)DEBIT

quote:

---

Originally posted by stepson

yes you can add

select *,
coalesce(DEBIT.Debit,0)- coalesce(CREDIT.Credit,0) as currentbalance

from .... rest of select

S

---