Create VIEW [dbo].[ViwAcclistRevValue]
AS

Select TotalID,ControlID,DetailID1,DetailID2,
	FullCode as Code,Title,Credit,Debit,
	0 as SumCredit,0 as SumDebit,0 as RemSumCredit, 0 as RemSumDebit,
	CASE WHEN Debit>Credit THEN Debit-Credit ELSE 0 END as RemDebit, 
	CASE WHEN Credit>Debit THEN Credit-Debit ELSE 0 END as RemCredit,
	CommittteeCode,CommittteeTitle,CommitteeType,
	--sw,
	Null as BackColor,
	Null as ForeColor,
	0 as Selected,
	Type,TitleNoSpace
	,'' as Code2, 0 as SumCredit2, 0 as SumDebit2, 0 as RemSumCredit2, 0 as RemSumDebit2
	,CreditType, 
	FullTitle, FullTitleNoSpace, Active
	from (

select 	acc.CommittteeCode,acc.CommittteeTitle,acc.CommitteeType, Acc.FullCode, Acc.FullTitle, Acc.FullTitleNoSpace, Acc.Code, Acc.Title, Acc.TitleNoSpace, Acc.Type, Acc.Active, Acc.TotalID, Acc.ControlID, Acc.DetailID1, Acc.DetailID2, Acc.CreditType
	,COALESCE(Sum(Item.Debit),0) as Debit, COALESCE(SUM(Item.Credit),0) as Credit
	from ViwAcclistRev as Acc Left Outer Join
	AccDocItem AS Item WITH (NOLOCK) On (Acc.TotalID=Item.TotalID) and (Acc.ControlID=Item.ControlID) and (Acc.DetailID1=Item.DetailID1) and (Acc.DetailID2=Item.DetailID2) 
--where FullCode=N'99‪-‫9990'
group by acc.CommittteeCode,acc.CommittteeTitle,acc.CommitteeType, Acc.FullCode, Acc.FullTitle, Acc.FullTitleNoSpace, Acc.Code, Acc.Title, Acc.TitleNoSpace, Acc.Type, Acc.Active, Acc.TotalID, Acc.ControlID, Acc.DetailID1, Acc.DetailID2,Acc.CreditType) t 


