Query to compare execution performance metrics for the three most recent months

Question

I've been working on a semi-awkward query in that it uses a very high number of functions given its relatively small size and scope. I was hoping to get some feedback on any ways I could format or re-factor this better?

select  Name ,
        avg(TimeProcessing / 1000 + TimeRendering / 1000 + TimeDataRetrieval / 1000) as 'Current Month' ,
        isnull(count(TimeProcessing), 0) as 'Sample' ,
        min(l2.[Avg Exec Previous Month]) as 'Previous Month' ,
        isnull(min(l2.[Sample Previous Month]), 0) as 'Sample' ,
        min(l3.[Avg Exec Two Months Ago]) as 'Two Months ago' ,
        isnull(min(l3.[Sample Two Months Ago]), 0) as 'Sample'
from    marlin.report_execution_log l
        inner join      marlin.report_catalog c on l.ReportID = c.ItemID
        left outer join ( select    l2.ReportID ,
                                    avg(TimeProcessing / 1000 + TimeRendering
                                        / 1000 + TimeDataRetrieval / 1000) as 'Avg Exec Previous Month' ,
                                    count(TimeProcessing) as 'Sample Previous Month'
                          from      marlin.report_execution_log l2
                          where     TimeEnd between dateadd(MONTH, -2,
                                                            getdate())
                                            and     dateadd(MONTH, -1,
                                                            getdate())
                          group by  l2.ReportID
                        ) l2 on l.ReportID = l2.ReportID
        left outer join ( select    l3.ReportID ,
                                    avg(TimeProcessing / 1000 + TimeRendering
                                        / 1000 + TimeDataRetrieval / 1000) as 'Avg Exec Two Months Ago' ,
                                    count(TimeProcessing) as 'Sample Two Months Ago'
                          from      marlin.report_execution_log l3
                          where     TimeEnd between dateadd(MONTH, -3,
                                                            getdate())
                                            and     dateadd(MONTH, -2,
                                                            getdate())
                          group by  l3.ReportID
                        ) l3 on l.ReportID = l3.ReportID
group by l.ReportID, Name
order by Name

Answer 1

You might want to use Common Table Expression or Should use meaningful table alias in Join.

You might also want to use indexes on your date column with <= and >= operator instead of between.

Surround your column names in [] instead of single quotes.

Answer 2

I agree with Nil; it seems like this might be a good situation to use a Common Table Expression.

I haven't tested this out, but below is my attempt to rewrite this query using a CTE:

with ReportByMonth (ReportID, [Year], [Month], [Avg Exec], [Sample]) as
(
    select  ReportID ,
            avg(TimeProcessing / 1000 + TimeRendering / 1000 + TimeDataRetrieval / 1000) as [Avg Exec] ,
            datepart(YEAR, TimeEnd) as [Year] ,
            datepart(MONTH, TimeEnd) as [Month] ,
            count(TimeProcessing) as [Sample]
    from Marlin.report_execution_log
    group by
        ReportID,
        datepart(YEAR, TimeEnd),
        datepart(MONTH, TimeEnd)
)
select  Name ,
        CurrentMonthReport.[Avg Exec] as [Current Month Avg Exec] ,
        isnull(CurrentMonthReport.[Sample], 0) as [Current Month Sample] ,
        PreviousMonthReport.[Avg Exec] as [Previous Month Avg Exec] ,
        isnull(PreviousMonthReport.[Sample], 0) as [Previous Month Sample] ,
        TwoMonthAgoReport.[Avg Exec] as [Two Months Ago Avg Exec] ,
        isnull(TwoMonthAgoReport.[Sample], 0) as [Two Months Ago Sample]
from marlin.report_catalog c
inner join ReportByMonth CurrentMonthReport on
    CurrentMonthReport.ReportID = c.ItemID
    and CurrentMonthReport.Year = datepart(YEAR, getdate())
    and CurrentMonthReport.Month = datepart(MONTH, getdate())
left outer join ReportByMonth PreviousMonthReport on
    PreviousMonthReport.ReportID = c.ItemID
    and PreviousMonthReport.Year = datepart(YEAR, dateadd(MONTH, -1, getdate()))
    and PreviousMonthReport.Month = datepart(MONTH, dateadd(MONTH, -1, getdate()))
left outer join ReportByMonth TwoMonthAgoReport on
    TwoMonthAgoReport.ReportID = c.ItemID
    and TwoMonthAgoReport.Year = datepart(YEAR, dateadd(MONTH, -2, getdate()))
    and TwoMonthAgoReport.Month = datepart(MONTH, dateadd(MONTH, -2, getdate()))
order by
    Name
;

EDIT: I changed my query to try to match the behavior OP's original query. I changed the join for CurrentMonthReport from "left outer join" to "inner join". This means that if a particular report has not been run during this month, then it won't show up in the result.

I also included use of the isnull function and added an order by clause.