Solved

Recursive Query using SQL 2005 CTE

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

930 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now