Solved

Recursive Query using SQL 2005 CTE

Posted on 2008-10-23
5
482 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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

726 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