sql - 30/60/90 Day Sumation Query -

i have following query. pulls invoice , time entries, , calculates ehr (effective hourly rate) each client, on per month basis. need is:

    company ,agreement ,lastmonthehr,60dayehr,90dayehr,6moehr,12moehr,lifeehr     companya,agreementb,       30.45,   27.76,   55.22, 30.75,  30.00,  25.00  

edit:

i apologize format. i'll see if can format better. following query returns monthly invoices, ehr calculated.

    select a.agr_name, agr_type.agr_type_desc, c.company_name, ap.invoicedate,ap.revenue,ap.hours,ap.ehr     agr_header inner join company c on a.company_recid = c.company_recid     left join agr_type on a.agr_type_recid = agr_type.agr_type_recid     left join (       select ar.agr_header_recid,ar.revenue,ac.invoicedate,isnull (ac.hours, 0) hours,       case            when ac.hours null (ar.revenue)            when ac.hours <= 1 (ar.revenue)            else cast (ar.revenue / nullif (ac.hours,0) numeric (9,2))       end 'ehr'       (         select ah.agr_header_recid,         dateadd(month,ai.month_nbr-1,dateadd(year,ai.year_nbr-2000,'2000-01-01')) invoicedate,         cast (ai.monthly_inv_amt numeric (9, 2)) revenue                 dbo.agr_header ah inner join         dbo.agr_invoice_amt ai on ah.agr_header_recid = ai.agr_header_recid         group ah.agr_header_recid, ai.month_nbr, ai.year_nbr) ar       left join (         select ah.agr_header_recid,sum(te.hours_actual) hours, dateadd(month, datediff(month,0,te.date_start),0) invoicedate                 dbo.time_entry te inner join          dbo.agr_header ah on te.agr_header_recid = ah.agr_header_recid         (te.agr_header_recid not null) , (te.agr_hours not null)        group ah.agr_header_recid, dateadd(month, datediff(month,0,te.date_start),0)) ac on ar.agr_header_recid = ac.agr_header_recid     , ar.invoicedate = ac.invoicedate) ap on ap.agr_header_recid = a.agr_header_recid     order company, agreement, invoicedate 

the sql posted pretty complicated, think can simplified. think key invoice data format similar following:

declare @invoice table(     [id] int,      [companyid] int,     [invoicedate] date,      [hours] decimal(9,2),      [revenue] decimal(9,2)) 

from there, calculations pretty simple, , can done using case when statements minimal subselects (i used 1 clarity, 1 eliminated). here's full working example sql server:

--setup table , dummy data declare @invoice table(     [id] int,      [companyid] int,     [invoicedate] date,      [hours] decimal(9,2),      [revenue] decimal(9,2)) insert @invoice values(1, 1, '2013-01-01', 5, 100) insert @invoice values(2, 1, '2013-02-01', 6, 100) insert @invoice values(3, 1, '2013-03-01', 7, 100) insert @invoice values(4, 1, '2013-04-01', 8, 100) insert @invoice values(5, 1, '2013-05-01', 9, 100) insert @invoice values(6, 1, '2013-06-01', 10, 100) insert @invoice values(7, 1, '2013-07-01', 11, 100) insert @invoice values(8, 1, '2013-08-01', 12, 100) insert @invoice values(9, 2, '2013-04-01', 5, 100) insert @invoice values(10, 2, '2013-05-01', 6, 100) insert @invoice values(11, 2, '2013-06-01', 7, 100) insert @invoice values(12, 2, '2013-07-01', 8, 100)  --calculate last month start , end dates --hardcoded here brevity declare @lastmonthstartdate datetime declare @lastmonthenddate datetime set @lastmonthstartdate = '2013-08-01' set @lastmonthenddate = '2013-09-01'  --calculate ehrs different time periods select     a.companyid,     case when a.lastmonthhours = 0 0 else a.lastmonthrevenue / a.lastmonthhours end [lastmonthehr],     case when a.last60dayshours = 0 0 else a.last60daysrevenue / a.last60dayshours end [last60daysehr],     case when a.last90dayshours = 0 0 else a.last90daysrevenue / a.last90dayshours end [last90daysehr] ( select      [companyid],     sum(case when [invoicedate] >= @lastmonthstartdate , [invoicedate] < @lastmonthenddate [hours] else 0 end) [lastmonthhours],     sum(case when [invoicedate] >= @lastmonthstartdate , [invoicedate] < @lastmonthenddate [revenue] else 0 end) [lastmonthrevenue],     sum(case when [invoicedate] >= dateadd(d, -60, getdate()) [hours] else 0 end) [last60dayshours],     sum(case when [invoicedate] >= dateadd(d, -60, getdate()) [revenue] else 0 end) [last60daysrevenue],     sum(case when [invoicedate] >= dateadd(d, -90, getdate()) [hours] else 0 end) [last90dayshours],     sum(case when [invoicedate] >= dateadd(d, -90, getdate()) [revenue] else 0 end) [last90daysrevenue] @invoice group [companyid] ) 

i think approach should work you. know had problem illustrate way approach query in answer, if need me expand on please let me know.