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
>

Pivot Tables

Greetings, can anyone tell me how to create a pivot table view? (If at all) I want to manipluate data to a view to use in Crystal reports. Current table has producta "A", "B" & "C" with three options "i", "ii" & "iii" each with amounts per product & options:

A i 10
A ii 20
A iii 30
B i 5
B ii 7
B iii 9
C i 2
C ii 4
C iii 6

I want to create a view that lists the product "A" and Options "i" in the rows, with Colums "i", "ii" and "iii" and their corresponding amounts listed in the relevant colums. Thanx!there's probably a better solution out there, but this will get you started:

declare @.tbl table (
product char(1) not null, options char(3) not null, price money not null)
insert @.tbl values ('A', 'i', 10)
insert @.tbl values ('A', 'ii', 20)
insert @.tbl values ('A', 'iii', 30)
insert @.tbl values ('B', 'i', 5)
insert @.tbl values ('B', 'ii', 7)
insert @.tbl values ('B', 'iii', 9)
insert @.tbl values ('C', 'i', 2)
insert @.tbl values ('C', 'ii', 4)
insert @.tbl values ('C', 'iii', 6)

select p.product, [i]=t1.price, [ii]=t2.price, [iii]=t3.price
from (select distinct product from @.tbl) p
inner join @.tbl t1
on p.product = t1.product and t1.options = 'i'
inner join @.tbl t2
on p.product = t2.product and t2.options = 'ii'
inner join @.tbl t3
on p.product = t3.product and t3.options = 'iii'|||It sounds like you are trying to create a CROSSTAB query, and you can look up how to do it using CASE statements by searching Books Online for keyword "crosstab".

However, this will NOT give you a try pivot table report, which is dynamically configurable by the user. I'm not sure that Crystal can even do this. As a matter of fact, for a pivot table you don't want oto have your data in crosstab format. If you truly want pivot functionality and the ability to slice, dice, and summarize your data dynamically, create a pivot table in Excel or in an ASP page that links to the data in your table, (through a view, preferably), and leave your data in its current columnar format.

blindman|||actually crystal was able to do it even when it was part of vb4.0

but i still think that having this static approach is better than allowing users to build pivots dynamically, because if they transpose columns and rows (intentionally or not) the whole thing will croke (or may croke).|||Would this not be equivilent to a cube? It seems that A, B, and C would be one dimension, and i, ii, and iii would be a second dimension, and the values would be the intersection points of the the two dimensions...

| A | B | C
------
i | 10 | 5 | 2
------
ii | 20 | 7 | 4
------
iii | 30 | 9 | 6
------|||this is exactly what i was talking about in the previous post!|||Originally posted by ms_sql_dba
this is exactly what i was talking about in the previous post!

Bah.. sorry.. I started typing, and got side tracked for a little while and didn't get to finish my post until there had been 3 other replies!

SQL supports the creation of cubes if you didn't want to do a bunch of joins.. that might get costly if you had lots of dimensions...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agcubesintro_80qb.asp|||Thanx for the advice! Appreciate your time!