This is my problem. I've got a table which holds the following values - date, current_sales, ytd_sales.
Now, the users are free to enter either ytd sales or current sales or both and for the months they have data.
I need to produce a report which will detail (to the closest possible value acc to the data aavailble) the variance against budget, annual figures, ytd figuers and so on. The most accurate way of doing this is for any given date get the closest ytd sales figure and add all the current month sales after that to the month to get the cumulative total.
Now, this is quite easily done in a stored proc. but instead of calculating all values on the fly each time, I want to have an indexed view which will give me for each month, the calculated YTD value.
As I mentioned, this has to (for each month) look to the closest entered YTD value and add all months after that.
The following table and sample data help illustrate the problems:
I have on purpose added YTD values which dont tally so that I know if it's being used properly.
I hope the problem is clear. Any ideas?
CREATE TABLE REC_Tbl (
[ID] [int] NOT NULL,
[appdt] [smalldatetime] NOT NULL,
[sales] [float] NULL,
[sales_agg] [float] NULL
) ON [PRIMARY]
and sample data
id appdt sales sales_agg
13 2007-01-31 00:00:00 50 NULL
13 2007-02-28 00:00:00 50 NULL
13 2007-03-31 00:00:00 50 NULL
13 2007-04-30 00:00:00 50 NULL
13 2007-05-31 00:00:00 50 NULL
13 2007-06-30 00:00:00 50 1300
13 2007-07-31 00:00:00 50 NULL
13 2007-08-31 00:00:00 50 1700
13 2007-09-30 00:00:00 50 NULL
13 2007-10-31 00:00:00 50 2700
13 2007-11-30 00:00:00 50 NULL
13 2007-12-31 00:00:00 50 4500
13 2008-01-31 00:00:00 50 NULL
13 2008-02-29 00:00:00 50 NULL
13 2008-03-31 00:00:00 50 NULL
13 2008-04-30 00:00:00 50 2000
13 2008-05-31 00:00:00 50 NULL
13 2008-06-30 00:00:00 50 NULL
to get the values added in one query, I tried using a recursive CTE as follows:
;WITH REC_1 as
SELECT id,appdt,sales,coalesce(sales_agg,sales,0) as sales_agg
from rec_tbl where appdt = '20070131'
SELECT c.id,c.appdt,c.sales,coalesce(c.sales_agg,c.sales+p.sales_agg,0) as sales_agg
from rec_tbl c inner join rec_1 p on dateadd(mm,-1,c.appdt) = p.appdt
select * from rec_1
For the above, all it returns is the first line! If I remove the filter (where appdt = '20070131') I get the whole set but it does not add the sales to the closest YTD value.