Must have semi-colon on last statement before the "WITH" keyword that begins the Common Table Expression (see sample below on "select @Dummy = 0;" line). Multiple CTE's must be comma delimited under the single "WITH" keyword (i.e. only the first CTE begins with the "WITH" statement, the subsequent ones begin with a comma).
declare
@BegDate datetime
, @EndDate datetime
, @Dummy int
select
@BegDate ='2/1/2009'
, @EndDate ='2/28/2009 11:59PM'
select @Dummy = 0;-- Prepare for the WITH CTE statement
with HistoryMax(ModificationID, HistoryDate)as
(
select ModificationID,max(HistoryDate) HistoryDate
from LMM.TermsHistory
where HistoryDate <= @EndDate
groupby ModificationID
)
, TermSnap(HistoryID)as
(
select HistoryID
from LMM.TermsHistory h
innerjoin HistoryMax hm on h.ModificationID = hm.ModificationID
and h.HistoryDate = hm.HistoryDate
)
, FilterSet(ModificationID, ModificationStatusCodeDate)as
(
select ModificationID,max(ModificationStatusCodeDate) ModificationStatusCodeDate
from LMM.TermsHistory
where ModificationstatusCode = 600
groupby ModificationID
)
select
datename(month, h.ModificationStatusCodeDate) ClosedMonth
,sum(isnull(fc1.FeeAmount, 0)+isnull(fc2.FeeAmount, 0)) FeeCollected
,sum(isnull(fp1.FeeAmount, 0)+isnull(fp2.FeeAmount, 0)) FeePending
from
LMM.TermsHistory t
innerjoin TermSnap ts
on t.HistoryID = ts.HistoryID
innerjoin FilterSet h
on t.ModificationID = h.ModificationID
leftouterjoin LMM.ModCalcFee fc1
on fc1.RecordTypeCode = 100
and fc1.FeeCollectedFlag = 1
and t.ModificationID = fc1.ModificationID
leftouterjoin LMM.ModCalcFee fc2
on fc2.RecordTypeCode = 200
and fc2.FeeCollectedFlag = 1
and t.ModificationID = fc2.ModificationID
leftouterjoin LMM.ModCalcFee fp1
on fp1.RecordTypeCode = 100
and fp1.FeeCollectedFlag = 0
and t.ModificationID = fp1.ModificationID
leftouterjoin LMM.ModCalcFee fp2
on fp2.RecordTypeCode = 200
and fp2.FeeCollectedFlag = 0
and t.ModificationID = fp2.ModificationID
where
h.ModificationStatusCodeDate between @BegDate and @EndDate
and t.ModificationStatusCode between 600 and 899
groupby
datename(month, h.ModificationStatusCodeDate)