Help with Rolling Data View

[craigK]

I'm trying to create a view that will display a single day as well as last months data based on the date of the data. Rolling on each day.

Data is like this:

Date Current
5/15/2008 30
6/1/2008 100
6/2/2008 50
6/29/2008 25
6/30/2008 10

The view needs to be:

Date Current Rolling 1 Month
6/1/2008 100 30
6/2/2008 50 130
6/29/2008 25 150
6/30/2008 10 175


I think i need some kind of nested select?

 

[RiverGuy]

I don't understand your expected resultset. Is the Rolling 1 Month supposed to be Month to Date? Month to Date Last Month? Last 30 days inclusive of date displayed? Or what? And yes, you can do running total's in T-SQL, although if you are using a reporting tool, it's much easier to do it in the reporting software.

 

[craigK]

Last 30 days exclusive of the date displayed. I am using Crystal reports, but because i need the current data grouped by day, i found impossible to do without subreports, which killed the performance.

 

[RiverGuy]

Here's one way off the top of my head. Copy and paste it into Query Analyzer/Management Studio. Modify as needed:

DECLARE @Temp1 TABLE 
(TransactionDate DATETIME,
TransactionAmount INT )

DECLARE @d DATETIME
SELECT @d = '20080101'
WHILE @d < '20080601'
BEGIN
	INSERT INTO @Temp1 (TransactionDate, TransactionAmount)
	SELECT @d, ((100 - 10 -1) * RAND() + 10)
	SELECT @d = DATEADD(day, 1, @d)
	
END

SELECT c.TransactionDate, MIN(c.TransactionAmount) AS CurrentAmount,
SUM(c.PastThirty) AS RollingThirty
FROM
	(SELECT a.*, ISNULL(b.TransactionAmount, 0) AS PastThirty 
	FROM
		(SELECT TransactionDate, TransactionAmount, 
		DATEADD(day, -30, TransactionDate) AS StartDate, DATEADD(day, -1, TransactionDate) AS EndDate
		FROM @Temp1) a
	LEFT OUTER JOIN @Temp1 b ON b.TransactionDate BETWEEN a.StartDate AND a.EndDate) c
GROUP BY c.TransactionDate
		
SELECT * FROM @Temp1

 

[craigK]

i'm having trouble understanding how to incorporate the date and amount from my data into this.

Sorry, i haven't done a lot of sql. will this work in a view?

 

[RiverGuy]

The top half is creating a dummy table with random data to test with. The bottom half is where the actual logic is. First thing I am doing is adding a StartDate and EndDate column to your records based on minus 30 days to minus 1 day. Then, taking that and joining your same table to it based on the date range. So you then end up with up to 30 records for each record in your table. Then I'm taking that and grouping by the actual date, getting the actual value, and summing the values which were joined based on the date range.

To run it on your table, take the bottom half starting with the SELECT c.TransactionDate and replace the word @Temp1 with the name of your table. Also, replace TransactionDate and TransactionAmount with the actual name of your columns.

You don't want this in a view because you are probably not going to be showing years and years worth of history on one report. You'll probably have data ranges and will be working with a smaller dataset. A stored procedure would work to encapsulate that.

 

[craigK]

Thanks, i understand now. I tried to run it in Query Analyzer against about 10,000 sample rows of data and it's been running for 10 minutes.. I'm not sure why that would be. i'll set up crystal to use this query and see if data is returned faster when there is a WHERE condition.

 

[RiverGuy]

10,000 rows would be 27 years, if you have one row per date as I assumed. But still, with 10,000 rows, it only took 29 seconds to run on my laptop.

 

[craigK]

it took 15 minutes, but it did complete and it does look like what i wanted, so i'll do some testing and see if i can figure out why its running so long on my system. i really appreciate your help with this.

thanks