Greetings,  
  
I need to write a SQL stored procedure to accomplish the following and I'm  
  
not having any luck. So that I don't confuse anyone too much, I'll leave out  
  
the code I'm trying and just explain what I'm trying to do.  
  
Start with  
  
Table: sequenceOrder  
  
orderNumber    totalUnits  
  
101                    26  
  
102                    11  
  
103                    9  
  
104                    8  
  
105                    16  
  
I have a VB.NET program that will be passing in two parameters, one is  
  
workDays (number of work days during a given week) and the other is  
  
dailyUnits (maximum number units to be made during each weekday).  
  
I need a running total, by record line, for the totalUnits. If the running  
  
total is less than dailyUnits, then I need to add the current value for that  
  
work day. If the running total is greater than dailyUnits, then I need to  
  
add 1 to the current value for that work day. Also, I need to number the  
  
orders within that workDay.  
  
Using the sample table above and having workDays = 3 and dailyUnits = 30 the  
  
output table would like this:  
  
Finish with  
  
Table: sequenceOrder  
  
orderNumber    totalUnits    workDay    sequenceOrder  
  
101                    26                1                1  
  
102                    11                2                1  
  
103                    9                   2                2  
  
104                    8                   2                3  
  
105                    16                3                1  
  
106                    12                3                2  
  
Is this possible, if so how do I get this done?  
  
Thanks for all the help,  
  
James Walker, Jr.

"James Walker" <walker@modernfold.com> wrote in message  
  
news:%232sOjRBgGHA.2188@TK2MSFTNGP04.phx.gbl...  
  
Ok, this is a fun one.  Here's a simple solution, using intiger division of  
  
the running total by the number of hours per day.  But I worry that the  
  
assignment of orders to days is too "dumb".  I have a suspicion that there's  
  
a better solution somewhere that would sequence the work to fill up the days  
  
and minimize idle time.  
  
create table Orders  
  
(  
  
OrderNumber int primary key,  
  
TotalUnits int not null  
  
)  
  
insert into Orders(orderNumber,TotalUnits)  
  
select 101,26  
  
union all select 102,11  
  
union all select 103,9  
  
union all select 104,8  
  
union all select 105,16  
  
go  
  
declare @days int,  
  
@dailyUnits int  
  
set @days = 3  
  
set @DailyUnits = 30;  
  
with UpcomingOrders(OrderNumber,TotalUnits,RunningTotal) as  
  
(  
  
select OrderNumber, TotalUnits,  
  
(select sum(TotalUnits) from Orders where OrderNumber <= o.OrderNumber)  
  
RunningTotal  
  
from Orders o  
  
)  
  
select  
  
Ordernumber,  
  
TotalUnits,  
  
1+RunningTotal/@DailyUnits WorkDay,  
  
row_number() over (partition by RunningTotal/@DailyUnits order by  
  
OrderNumber) SequenceOrder  
  
from UpcomingOrders  
  
where 1+RunningTotal/@DailyUnits <= @days  
  
David

David,  
  
Thanks for the reply. It works great. Also, "filling up idle time" is in the  
  
next phase. As in my example, the first day has 4 more units it could make,  
  
so I will need to search down the records where the totalUnits is <= 4 and  
  
then move that order up for the week.  
  
James Walker, Jr.  
  
"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in  
  
message news:%23JMsfHCgGHA.452@TK2MSFTNGP02.phx.gbl...