Please Help ... Can't Figure This One Out

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 w) and the other is
dailyUnits (maximum number units to be made during each wday).
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...
> 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 w) and the other is
> dailyUnits (maximum number units to be made during each wday).
> 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?
>
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,Ru
nningTotal) 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 w.
James Walker, Jr.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23JMsfHCgGHA.452@.TK2MSFTNGP02.phx.gbl...
> "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,Ru
nningTotal) 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
>