Adding column values recursively

I have a table Calendar which has only BusinessDates and excludes Sat and Sun and Holidays.
I have another table Products which has the Code, BusDate, ID, ItemSold

Need to create a join such that if the Products BusDate does not exist in the Calendar table then
add the ItemsSold to the next   BusDate row for a given ProductCode
So the items sold on a Sat, will be added to a  Sun, which will then be added to a Monday,
assuming is not a holiday.
If Monday was a holiday then it would be added to the Tuesdays bucket, and the addition would stop

Calendar
BusDate datetime

Products

ProductCode
BusDate datetime
ItemsSold int

Example
ProductCode, BusDate,    ItemSold
ABCD         06/14/2013   5
ABCD         06/15/2013   3      
ABCD         06/16/2013   2  
ABCD         06/17/2013   2  
ABCD         06/18/2013   1

The final result should be
ABCD         06/14/2013   5
ABCD         06/17/2013   7  
ABCD         06/18/2013   1
LVL 1
countrymeisterAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
results:
PRODUCTCODE	BUSDATE	ITEMSOLD
ABCD	June, 14 2013 		5
ABCD	June, 17 2013 		7
ABCD	June, 18 2013 		1

-- from this code

select
    p.ProductCode
  , ca.cdate        as BusDate
  , sum(p.ItemSold) as ItemSold
from products as p
cross apply (
              select top 1 busdate
              from calendar as c 
              where c.busdate >= p.busdate
              order by c.busdate ASC
            ) as ca (cdate)
group by
    p.ProductCode
  , ca.cdate
;

Open in new window

http://sqlfiddle.com/#!3/41cc1/7
0
 
SharathData EngineerCommented:
try this query.
;WITH CTE 
     AS (SELECT p.ProductCode, 
                p.BusDate, 
                MIN(c.BusDate) adj_busdate 
           FROM Products p 
                LEFT JOIN Calendar c 
                       ON p.BusDate <= c.BusDate 
          GROUP BY p.ProductCode, 
                   p.BusDate) 
SELECT c.ProductCode, 
       c.Adj_BusDate, 
       SUM(p.ItemSold) 
  FROM CTE c 
       JOIN Products p 
         ON c.ProductCode = p.ProductCode 
            AND c.BusDate = p.BusDate 
 GROUP BY c.ProductCode, 
          c.Adj_BusDate 

Open in new window

see the sample here: http://sqlfiddle.com/#!3/9652d/12
0
 
countrymeisterAuthor Commented:
Awesome, thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.