Ignis2000
asked on
Recursive Query using SQL 2005 CTE
Hi there,
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?
Thanks
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?
Thanks
CREATE TABLE REC_Tbl (
[ID] [int] NOT NULL,
[appdt] [smalldatetime] NOT NULL,
[sales] [float] NULL,
[sales_agg] [float] NULL
) ON [PRIMARY]
GO
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'
UNION ALL
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.
momi_sabag
can you post the wanted output of the above sample data? i would give you the query that will return that output
ASKER
Here it is!
id appdt sales sales_agg Calc Sales_Agg
13 2007-01-31 00:00:00 50 NULL 50
13 2007-02-28 00:00:00 50 NULL 100
13 2007-03-31 00:00:00 50 NULL 150
13 2007-04-30 00:00:00 50 NULL 200
13 2007-05-31 00:00:00 50 NULL 250
13 2007-06-30 00:00:00 50 1300 1300
13 2007-07-31 00:00:00 50 NULL 1350
13 2007-08-31 00:00:00 50 1700 1700
13 2007-09-30 00:00:00 50 NULL 1750
13 2007-10-31 00:00:00 50 2700 2700
13 2007-11-30 00:00:00 50 NULL 2750
13 2007-12-31 00:00:00 50 4500 4500
13 2008-01-31 00:00:00 50 NULL 50
13 2008-02-29 00:00:00 50 NULL 100
13 2008-03-31 00:00:00 50 NULL 150
13 2008-04-30 00:00:00 50 2000 2000
13 2008-05-31 00:00:00 50 NULL 2050
13 2008-06-30 00:00:00 50 NULL 2100
Here you go...
problem was the dates - add 1 to month and do not get next end of month...
problem was the dates - add 1 to month and do not get next end of month...
WITH REC_1 (id, appdt ,sales, sales_agg, Calc_Sales_Agg)
as
(
SELECT top 1 c.id,c.appdt,c.sales, c.sales_agg, coalesce(c.sales_agg,c.sales,0) as Calc_sales_agg
from rec_tbl c order by id, appdt asc
union all
SELECT c.id,c.appdt,c.sales, c.sales_agg, coalesce(c.sales_agg,c.sales+p.calc_sales_agg,0) as Calc_sales_agg
from rec_tbl c
inner join rec_1 p on p.id = c.id and convert(varchar(6),dateadd(mm,1,p.appdt),112) = convert(varchar(6),c.appdt,112)
)
select * from rec_1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brill! thanks so much!!