Solved

Recursive Query using SQL 2005 CTE

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

747 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

13 Experts available now in Live!

Get 1:1 Help Now