declare @year0 datetime
declare @year1 datetime
declare @year2 datetime
set @year0 = '12/31/2015'
set @year1 = '12/31/2014'
set @year2 = '12/31/2013'
Select YEAR(@year0) as Year,
SUM(case when DaysOS <= 30 then Amount else 0 end) as Fee30,
SUM(Case When DaysOS > 30 and DaysOS <= 60 Then Amount else 0 end) as Fee60,
SUM(Case When DaysOS > 60 and DaysOS <= 90 Then Amount else 0 end) as Fee90,
SUM(Case When DaysOS > 90 and DaysOS <= 120 Then Amount else 0 end) as Fee120,
SUM(Case When DaysOS > 120 Then Amount else 0 end) as Fee121
From
(
Select UTMatter, DATEDIFF(day, UTDate, @year0) as DaysOS, SUM(UTAmount) as Amount
From UnbilledTime where UTDate <= @year0
Group by UTMatter, DATEDIFF(day, UTDate, @year0)
Union All Select BTMatter, DATEDIFF(day, BTDate, @year0) as DaysOS, SUM(BTAmount) as Amount
From BilledTime INNER JOIN ARBill on BilledTime.BTBillNbr = ARBill.ARBillNbr
WHERE BTDate <= @year0 and ARBill.ARBillDate > @year0
GROUP BY BTMatter, DATEDIFF(day, BTDate, @year0)
) as Year0Query
Union All Select YEAR(@year1) as Year,
SUM(case when DaysOS <= 30 then Amount else 0 end) as Fee30,
SUM(Case When DaysOS > 30 and DaysOS <= 60 Then Amount else 0 end) as Fee60,
SUM(Case When DaysOS > 60 and DaysOS <= 90 Then Amount else 0 end) as Fee90,
SUM(Case When DaysOS > 90 and DaysOS <= 120 Then Amount else 0 end) as Fee120,
SUM(Case When DaysOS > 120 Then Amount else 0 end) as Fee121
From
(
Select UTMatter, DATEDIFF(day, UTDate, @year1) as DaysOS, SUM(UTAmount) as Amount
From UnbilledTime where UTDate <= @year1
Group by UTMatter, DATEDIFF(day, UTDate, @year1)
Union All Select BTMatter, DATEDIFF(day, BTDate, @year1) as DaysOS, SUM(BTAmount) as Amount
From BilledTime INNER JOIN ARBill on BilledTime.BTBillNbr = ARBill.ARBillNbr
WHERE BTDate <= @year1 and ARBill.ARBillDate > @year1
GROUP BY BTMatter, DATEDIFF(day, BTDate, @year1)
) as Year1Query
Union All Select YEAR(@year2) as Year,
SUM(case when DaysOS <= 30 then Amount else 0 end) as Fee30,
SUM(Case When DaysOS > 30 and DaysOS <= 60 Then Amount else 0 end) as Fee60,
SUM(Case When DaysOS > 60 and DaysOS <= 90 Then Amount else 0 end) as Fee90,
SUM(Case When DaysOS > 90 and DaysOS <= 120 Then Amount else 0 end) as Fee120,
SUM(Case When DaysOS > 120 Then Amount else 0 end) as Fee121
From
(
Select UTMatter, DATEDIFF(day, UTDate, @year2) as DaysOS, SUM(UTAmount) as Amount
From UnbilledTime where UTDate <= @year2
Group by UTMatter, DATEDIFF(day, UTDate, @year2)
Union All Select BTMatter, DATEDIFF(day, BTDate, @year2) as DaysOS, SUM(BTAmount) as Amount
From BilledTime INNER JOIN ARBill on BilledTime.BTBillNbr = ARBill.ARBillNbr
WHERE BTDate <= @year2 and ARBill.ARBillDate > @year2
GROUP BY BTMatter, DATEDIFF(day, BTDate, @year2)
) as Year1Query