Recursive CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Recursive CTE

Author

Topic

MusselmanC
Starting Member

4 Posts

Posted - 2009-08-26 : 13:00:43

I need to be able to calculate a 'future inventory' qty derived table or CTE (I'll refer to it as FUTURE_INVEN) for an upcoming set of dates. Have 3 tables: PROD(Forecast Production), SALES(Open Sales Orders) and INVEN(Current Inventory) with qty's for future dates in PROD and SALES tables.

Thought is that this can be done relatively easily via a recursive CTE but I can't seem to figure it out.

In example data below,

8/25/09 FUTURE_INVEN = 8/24/09 INVEN + 8/25/09 PROD + 8/25/09 SALES
8/26/09 FUTURE_INVEN = 8/25/09 FUTURE_INVEN + 8/26/09 PROD + 8/26/09 SALES
Etc...

PROD table
DATE ___SKU _QTY
8/24/09 1012 7
8/25/09 1012 12
8/26/09 1012 6

SALES table
DATE ___SKU _QTY
8/24/09 1012 -15
8/25/09 1012 -11
8/26/09 1012 -9

INVEN table
DATE ___SKU _QTY
8/24/09 1012 50

Here's what I need...

FUTURE_INVEN
DATE ___SKU _QTY
8/25/09 1012 42
8/26/09 1012 43
8/27/09 1012 40

Any help is appreciated!!

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-08-26 : 13:20:42

declare @MaxDAte datetime
declare @MinDate datetime
set @MaxDate = '8/27/2009'
set @MinDate = (select top 1 date from Inven)
;with cte as
(
select @MinDate as Date,Qty,SKU from Inven where Date = @MinDate union all
select c.Date + 1,C.Qty + P.Qty + S.Qty,C.SKU from cte c
inner join Prod P on C.Date + 1= P.Date
inner join @Sales S on S.Date = C.Date + 1
where C.Date <= MaxDate
)
select * from cte

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-26 : 13:27:37

I got nearly the same thing but it will work with multiple SKUs. Did we get it wrong or is your expected output out of sync with your sample data?


declare @PROD table (DATE datetime, SKU int, QTY int)
insert @prod
select '8/24/09', 1012, 7 union all
select '8/25/09', 1012, 12 union all
select '8/26/09', 1012, 6

declare @SALES table (DATE datetime, SKU int, QTY int)
insert @sales
select '8/24/09', 1012, -15 union all
select '8/25/09', 1012, -11 union all
select '8/26/09', 1012, -9

declare @INVEN table (DATE datetime, SKU int, QTY int)
insert @inven
select '8/24/09', 1012, 50

;with cte as
(
       select ca.Date
              ,i.sku
              ,ca.qty
       from   (
              select sku
              from   @inven i
              group by sku
              ) i
       cross apply
              (
              select top 1 Date, Qty
              from   @inven
              where  sku = i.sku
              order by date desc
              ) ca
       union all
       select p.date
              ,i.sku
              ,i.qty + isNull(p.qty, 0) + isNull(s.qty,0)
       from   cte i
       inner join   @prod p
              on p.sku = i.sku
              and p.date = dateadd(day, 1, i.date)
       inner join   @sales s
              on s.sku = i.sku
              and s.date = p.date
)
select date, sku, qty from cte

output:

date                    sku         qty
----------------------- ----------- -----------
2009-08-24 00:00:00.000 1012        50
2009-08-25 00:00:00.000 1012        51
2009-08-26 00:00:00.000 1012        48

Be One with the Optimizer
TG

MusselmanC
Starting Member

4 Posts

Posted - 2009-08-26 : 20:04:37

Sorry, my expected output was out of sync. Thanks to both of you for the assistance -- got me exactly what I needed!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources