UFN_MKTMARKETINGPLANITEMBUDGET

CREATE function dbo.[UFN_MKTMARKETINGPLANITEMBUDGET]
(
  @MARKETINGPLANITEMID uniqueidentifier=null
)
/*
Returns a table with the budget and allocation amounts for a given marketing plan item
*/
returns table
as
  return
  (
    select
      M.[ID],
      M.[BUDGETAMOUNT] as [SPECIFIED],
      (coalesce((select SUM([BUDGETAMOUNT])
                 from dbo.[MKTMARKETINGPLANITEM] iM
                 where iM.[PARENTMARKETINGPLANITEMID] = M.[ID]),0) +
       coalesce((select SUM([BUDGETAMOUNT])
                 from dbo.[MKTMARKETINGPLANBRIEF] iM
                 where iM.[MARKETINGPLANITEMID] = M.[ID]),0)) as [ALLOCATED]
    from dbo.[MKTMARKETINGPLANITEM] M
    where M.[ID]=@MARKETINGPLANITEMID or @MARKETINGPLANITEMID is null
  );