Solved

Recursive Query using SQL 2005 CTE

Posted on 2008-10-23
5
477 Views
Last Modified: 2013-12-07
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

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.

Open in new window

0
Comment
Question by:Ignis2000
  • 2
  • 2
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22784838
can you post the wanted output of the above sample data? i would give you the query that will return that output
0
 

Author Comment

by:Ignis2000
ID: 22784859
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

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22785187
Here you go...
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

Open in new window

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 22785231
Sorry, forgot to reset at end of year...


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, case when year(p.appdt)=year(c.appdt) then coalesce(c.sales_agg,c.sales+p.calc_sales_agg,0) else coalesce(c.sales_agg,c.sales,0) end 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
0
 

Author Closing Comment

by:Ignis2000
ID: 31509139
Brill! thanks so much!!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
clob to char in oracle 3 39
dbms_crypto.decrypt   errors out 6 31
DB migration from Mssql to 12c oracle , data not loading. 3 29
sql how to count case when 4 16
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question