
CREATE VIEW [dbo].[ViwAcclistValue]
AS

Select TotalID,ControlID,DetailID1,DetailID2,
	FullCode as Code,Title,Credit,Debit,
	Credit as SumCredit,Debit as SumDebit,
	CASE WHEN Debit > Credit then Debit-Credit ELSE 0 END as RemSumCredit, CASE WHEN Credit>Debit THEN Credit-Debit ELSE 0 END 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, Credit as SumCredit2, Debit as SumDebit2,
	CASE WHEN Debit>Credit THEN Debit-Credit ELSE 0 END as RemSumCredit2, 
	CASE WHEN Credit>Debit THEN Credit-Debit ELSE 0 END 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 ViwAcclist 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 



--select Acc.Committee, Acc.FullCode, Acc.FullTitle, Acc.FullTitleNoSpace, Acc.Code, Acc.Title, Acc.TitleNoSpace, Acc.Type, Acc.Active, Acc.TotalID, Acc.ControlID, Acc.DetailID1, Acc.DetailID2
--	,Item.*
--	from ViwAcclist 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'


--select * from AccDocItem where TotalID=10 and ControlID=418 and DetailID1=0