Friday, March 23, 2012

Running Sum In View (sql server 2000)

Please can anyone help me ....

how to do running sum in view in sql server 2000

Please see the example below on how to do this.
/*

This table tracks the quantity bought for a particular item.

The problem is to calculate the moving average of sales over the last 3 months

including the current month. The logic can be extended to any moving average problem.

*/

CREATE TABLE #ItemSales (

ItemID int ,

DateBought datetime ,

Quantity int ,

CONSTRAINT PK_ItemSales_ID_Bought PRIMARY KEY CLUSTERED( ItemID , DateBought )

);

INSERT INTO #ItemSales

SELECT 1 , '2000-10-20' , 62

UNION ALL

SELECT 1 , '2000-09-01' , 13

UNION ALL

SELECT 1 , '2000-11-01' , 45

UNION ALL

SELECT 1 , '2000-06-01' , 89

UNION ALL

SELECT 2 , '2000-06-01' , 37

UNION ALL

SELECT 2 , '2000-10-24' , 81

UNION ALL

SELECT 2 , '2000-10-12' , 56;

-- How the data looks, from the most recent sale for eacn item.

SELECT * FROM #ItemSales ORDER BY ItemID , DateBought DESC;

/*

ItemID DateBought Quantity

-- --

1 2000-11-01 00:00:00.000 45

1 2000-10-20 00:00:00.000 62

1 2000-09-01 00:00:00.000 13

1 2000-06-01 00:00:00.000 89

2 2000-10-24 00:00:00.000 81

2 2000-10-12 00:00:00.000 56

2 2000-06-01 00:00:00.000 37

*/

GO

/*

Corelated query for calculating the moving average of each item's sale over the

last 3 months including the current date.

*/

SELECT i1.ItemID , i1.DateBought ,

( SELECT AVG( i2.Quantity )

FROM #ItemSales AS i2

WHERE i2.ItemID = i1.ItemID And

DATEDIFF( mm , i2.DateBought , i1.DateBought ) Between 0 And 3

) AS MovingAverageOver3Months

FROM #ItemSales AS i1

ORDER BY i1.ItemID , i1.DateBought DESC;

-- Expected Output:

/*

ItemID DateBought MovingAverageOver3Months

--

1 2000-11-01 00:00:00.000 40

1 2000-10-20 00:00:00.000 37

1 2000-09-01 00:00:00.000 51

1 2000-06-01 00:00:00.000 89

2 2000-10-24 00:00:00.000 68

2 2000-10-12 00:00:00.000 68

2 2000-06-01 00:00:00.000 37

*/

DROP TABLE #ItemSales;

GO

No comments:

Post a Comment