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 Exp30,
SUM(Case When DaysOS > 30 and DaysOS <= 60 Then Amount else 0 end) as Exp60,
SUM(Case When DaysOS > 60 and DaysOS <= 90 Then Amount else 0 end) as Exp90,
SUM(Case When DaysOS > 90 and DaysOS <= 120 Then Amount else 0 end) as Exp120,
SUM(Case When DaysOS > 120 Then Amount else 0 end) as Exp121
From
(
Select UEMatter, DATEDIFF(day, UEDate, @year0) as DaysOS, SUM(UEAmount) as Amount
From UnbilledExpense where UEDate <= @year0
Group by UEMatter, DATEDIFF(day, UEDate, @year0)
Union All Select BEMatter, DATEDIFF(day, BEDate, @year0) as DaysOS, SUM(BEAmount) as Amount
From BilledExpenses INNER JOIN ARBill on BilledExpenses.BEBillNbr = ARBill.ARBillNbr
WHERE BEDate <= @year0 and ARBill.ARBillDate > @year0
GROUP BY BEMatter, DATEDIFF(day, BEDate, @year0)
) as Year0Query
Union All Select YEAR(@year1) as Year,
SUM(case when DaysOS <= 30 then Amount else 0 end) as Exp30,
SUM(Case When DaysOS > 30 and DaysOS <= 60 Then Amount else 0 end) as Exp60,
SUM(Case When DaysOS > 60 and DaysOS <= 90 Then Amount else 0 end) as Exp90,
SUM(Case When DaysOS > 90 and DaysOS <= 120 Then Amount else 0 end) as Exp120,
SUM(Case When DaysOS > 120 Then Amount else 0 end) as Exp121
From
(
Select UEMatter, DATEDIFF(day, UEDate, @year1) as DaysOS, SUM(UEAmount) as Amount
From UnbilledExpense where UEDate <= @year1
Group by UEMatter, DATEDIFF(day, UEDate, @year1)
Union All Select BEMatter, DATEDIFF(day, BEDate, @year1) as DaysOS, SUM(BEAmount) as Amount
From BilledExpenses INNER JOIN ARBill on BilledExpenses.BEBillNbr = ARBill.ARBillNbr
WHERE BEDate <= @year1 and ARBill.ARBillDate > @year1
GROUP BY BEMatter, DATEDIFF(day, BEDate, @year1)
) as Year1Query
Union All Select YEAR(@year2) as Year,
SUM(case when DaysOS <= 30 then Amount else 0 end) as Exp30,
SUM(Case When DaysOS > 30 and DaysOS <= 60 Then Amount else 0 end) as Exp60,
SUM(Case When DaysOS > 60 and DaysOS <= 90 Then Amount else 0 end) as Exp90,
SUM(Case When DaysOS > 90 and DaysOS <= 120 Then Amount else 0 end) as Exp120,
SUM(Case When DaysOS > 120 Then Amount else 0 end) as Exp121
From
(
Select UEMatter, DATEDIFF(day, UEDate, @year2) as DaysOS, SUM(UEAmount) as Amount
From UnbilledExpense where UEDate <= @year2
Group by UEMatter, DATEDIFF(day, UEDate, @year2)
Union All Select BEMatter, DATEDIFF(day, BEDate, @year2) as DaysOS, SUM(BEAmount) as Amount
From BilledExpenses INNER JOIN ARBill on BilledExpenses.BEBillNbr = ARBill.ARBillNbr
WHERE BEDate <= @year2 and ARBill.ARBillDate > @year2
GROUP BY BEMatter, DATEDIFF(day, BEDate, @year2)
) as Year1Query