Solved

Recursive Query using SQL 2005 CTE

Posted on 2008-10-23
5
483 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

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 …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

707 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