Recursive CTE -- 2nd shot - 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 -- 2nd shot

Author

Topic

MusselmanC
Starting Member

4 Posts

Posted - 2009-08-27 : 11:27:49

I may have been oversimplistic with my 'RECURSIVE CTE' post yesterday as I'm not fully able to get what I need..

ORIGINAL POST
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
8/29/09 1012 9
9/3/09 1012 14

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

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

Here's what I need...

FUTURE_INVEN
DATE ___SKU _QTY
8/24/09 1012 42
8/25/09 1012 43
8/26/09 1012 40
8/27/09 1012 40
8/28/09 1012 37
8/29/09 1012 46
8/30/09 1012 46
8/31/09 1012 46
9/1/09 1012 40
9/2/09 1012 40
9/3/09 1012 54

CLARIFICATION (where I oversimplifed)
See additional data lines in read above -- having an issue with the inner joins as I will not always have a record for a given Date/SKU in the PROD and SALES tables

=== ORIGINAL REPLY TO MY POST ===
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

--------------------------------------------------------------------------------
Edited by - TG on 08/26/2009 13:37:31

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-27 : 11:38:17

Two things, MusselmanC

1) You should have continued with your original topic as it is still the same issue.

2) Why should we have to add all your new data to the executable code I provided yesterday. Now that you have an example you can see how to add more rows to the sameple data - you're the one that needs the help - you should at least do that work.

So add the sample data yourself and post the expected results based on your data.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 12:33:54

[code]SELECT DATE ,SKU,QTY INTO #temp
FROM
(
SELECT DATE ,SKU ,QTY
FROM PROD

UNION ALL

SELECT DATE ,SKU ,QTY
FROM SALES

UNION ALL

SELECT DATE ,SKU ,QTY
FROM INVEN
)t

DECLARE @Start datetime,@End datetime

SELECT @Start=MIN(DATE),@End=MAX(DATE)
FROM #temp

;With Date_CTE(DateVal)
AS
(SELECT @Start
UNION ALL
SELECT DATEADD(dd,1,DateVal)
FROM Date_CTE
WHERE DATEADD(dd,1,DateVal)<=@End
)

SELECT d.DATE,d.SKU,d1.QtySum
FROM Date_CTE d
OUTER APPLY(SELECT SKU,SUM(QTY) AS QtySum
FROM #temp
WHERE Date<=d.Date
GROUP BY SKU
)d1
[/code]

MusselmanC
Starting Member

4 Posts

Posted - 2009-08-27 : 16:22:43

Here's revised executable code for my scenario:
============
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 union all
select '8/29/09', 1012, 9 union all
select '9/3/09', 1012, 14

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 union all
select '8/28/09', 1012, -12 union all
select '9/1/09', 1012, -6

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

============expected results are:
DATE SKU QTY
8/24/09 1012 42
8/25/09 1012 43
8/26/09 1012 40
8/27/09 1012 40
8/28/09 1012 37
8/29/09 1012 46
8/30/09 1012 46
8/31/09 1012 46
9/1/09 1012 40
9/2/09 1012 40
9/3/09 1012 54

Thanks in advance for any help you can provide.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-27 : 18:19:00

Thanks for adding the data and posting expected results. This doesn't exactly match your results. Not sure if it is your sample of if I'm missing something but this should be close.


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 union all
select '8/29/09', 1012, 9 union all
select '9/3/09', 1012, 14

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 union all
select '8/28/09', 1012, -12 union all
select '9/1/09', 1012, -6

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

declare @last int, @firstDay datetime
select @last = datediff(day, min(date), max(date)), @firstDay  = min(date)
from   (select date from @prod union select date from @sales) d

;with 
n (number) as
(
       select 0
       union all
       select n.number + 1
       from   n
       where  n.number+1 <=  @last
)
,s (sku) as
(
       select sku from @inven group by sku
)
select dateadd(day, n.number, @firstDay) Date
       ,s.sku
       ,oa.qty
from n
cross join s 
outer apply (
       select sum(qty) qty
       from   (
                     select qty from @sales where sku = s.sku and date <= dateadd(day, n.number, @firstDay)
                     union all
                     select qty from @prod where sku = s.sku and date <= dateadd(day, n.number, @firstDay)
                     union all
                     select qty from @inven where sku = s.sku and date <= dateadd(day, n.number, @firstDay)
               ) u
       ) oa

OUTPUT:

Date                    sku         qty
----------------------- ----------- -----------
2009-08-24 00:00:00.000 1012        42
2009-08-25 00:00:00.000 1012        43
2009-08-26 00:00:00.000 1012        40
2009-08-27 00:00:00.000 1012        40
2009-08-28 00:00:00.000 1012        28
2009-08-29 00:00:00.000 1012        37
2009-08-30 00:00:00.000 1012        37
2009-08-31 00:00:00.000 1012        37
2009-09-01 00:00:00.000 1012        31
2009-09-02 00:00:00.000 1012        31
2009-09-03 00:00:00.000 1012        45

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources