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.


Comments

Popular posts from this blog

How to remove text and logo OR add Overflow on Android ActionBar using AppCompat on API 8? -

html - How to style widget with post count different than without post count -

url rewriting - How to redirect a http POST with urlrewritefilter -