Calculating Last Month's Figure (SQL)

[cotton31]

Hi,

I'm trying to use an indicator to illustrate a trend in a Reporting Services report.

At the moment I have (for example) Month and AvgSales. For each month, I think I need to pull through last month's average sales in the next column - I can then use this to show my trend.

So, at the moment I have this:

WITH Sales AS (
SELECT Month
, AVG(SalesAmount) AS AvgSales

FROM tblSales)

SELECT ThisMonth.Month
, ThisMonth.AvgSales
, LastMonth.AvgSales

FROM Sales AS ThisMonth INNER JOIN
Sales AS LastMonth ON LastMonth.Month + 1 = ThisMonth.Month

This works and gives me the correct figures, but takes quite a while to run (1min+).

Is there any way I can make this more efficient, or am I barking up the wrong tree completely?

Edit: SQL Server 2008 R2 btw - I always forget that!

Cheers!

 

[Confused]

Use SQL Server Profiler to generate a trace, run the report, then run the results of the trace through the analyser.

It will then suggest improvements, such as creating indexes, which could greatly improve the speed of retrieval of this data.

 

[Haircut]

You can do this as analytic query - think this should work in SQL Server.

select distinct avg(SalesAmount) Over (Partition by Month) AvgSalesPerMonth, Month
from tblSales
where Month in (Month1, Month2)

Where obviously the where clause filters the months you wish to check.
Not sure if it will be quicker, but it's worth looking at.

 

[cotton31]

You can do this as analytic query - think this should work in SQL Server.

select distinct avg(SalesAmount) Over (Partition by Month) AvgSalesPerMonth, Month
from tblSales
where Month in (Month1, Month2)

Where obviously the where clause filters the months you wish to check.
Not sure if it will be quicker, but it's worth looking at.

Not sure whether this does what I need? How does this give me last months figure along with this months? So for example I'd need:

Month This Month Last Month
November 2011 100 150
December 2011 76 100
January 2012 109 76

Cheers!

 

[Haircut]

Not sure whether this does what I need? How does this give me last months figure along with this months? So for example I'd need:

Month This Month Last Month
November 2011 100 150
December 2011 76 100
January 2012 109 76

Cheers!

That query will partition about the average sales amounts per month, and then you can use something similar to the query in your OP to format things into the format you want.