Get the Query Result
-
Dear All:
Please tell me how to solve below problem? Please help, thanks!
-- Table Data
create table podetail(podate smalldatetime, ponum char(4), parts char(10),
poqnty int)
create table wip(proccode tinyint, parts char(10), wipqnty int)
insert into podetail values('10/01/1997', 'p001', 'A001', 100)
insert into podetail values('10/02/1997', 'p002', 'A001', 150)
insert into podetail values('10/03/1997', 'p003', 'A001', 120)
insert into podetail values('10/03/1997', 'p004', 'A002', 320)
insert into podetail values('10/10/1997', 'p005', 'A002', 120)
insert into podetail values('10/10/1997', 'p006', 'A003', 720)
insert into podetail values('10/10/1997', 'p007', 'A003', 100)
insert into wip values(1, 'A001', 330)
insert into wip values(2, 'A001', 70)
insert into wip values(3, 'A001', 15)
insert into wip values(4, 'A001', 75)
insert into wip values(5, 'A001', 35)
insert into wip values(1, 'A002', 230)
insert into wip values(2, 'A002', 230)
insert into wip values(1, 'A003', 430)
insert into wip values(2, 'A003', 250)
-- And I want to get below result ...
parts podate ponum poqnty proccode wipqnty mapqnty accwip accpo
---------- -------- ----- ----------- -------- ----------- ----------- ----------- -----------
A001 10/01/97 p001 100 5 35 35 35 100
A001 10/01/97 p001 100 4 75 65 110 100
A001 10/02/97 p002 150 4 75 10 110 250
A001 10/02/97 p002 150 3 15 15 125 250
A001 10/02/97 p002 150 2 70 70 195 250
A001 10/02/97 p002 150 1 330 55 525 250
A001 10/03/97 p003 120 1 330 120 525 370
A002 10/03/97 p004 320 2 230 230 230 320
A002 10/03/97 p004 320 1 230 90 460 320
A002 10/10/97 p005 120 1 230 120 460 440
A003 10/10/97 p006 720 2 250 250 250 720
A003 10/10/97 p006 720 1 430 430 680 720
-
You're kidding, right?
-
No!
It will have some way to get it!
Just on "Query String"!
-
Without knowing how some of the results are calculated, we'll be taking stabs in the dark and hoping we're right. Can you post a bit about how the columns which aren't part of the two tables are supposed to be calculated? Not as T-SQL, but in ordinary language.
K. Brian Kelley
http://www.truthsolutions.com/
K. Brian Kelley
@kbriankelley -
And it would be nice to see what you've attempted in TSQL even if it doesnt work.
Andy
-
I see that accwip is short for accumulated wip. And that accwip is the sum of the wipqnty's for a specific ponum (ie. P001). But what I can't figure out is what is mapqnty (how do you come up with that number?) And how do you determine which proccodes you retrieve (for ponum P001 you only retrieve proccodes 5 and 4, and ignore 3,2, and 1)?
-SQLBill
-
I think MAPQNTY is the quantity from the WIP that is allocated to the podetail line.
E.g. in the second line, you will be using 65 items from the 75 in the WIP to satisfy the remaining quantity of the first podetail line (35 out of a 100 were allocated from WIP nr 5).
This looks to be some sort of resource allocation problem. Books have been written about this sort of problem...
IMHI, impossible to solve this in a few simple SQL statements. If you don't need a complicated 'optimisation' routine, you will have to work with two cursors, one for each table, inserting the allocated resources in a third table and maintaining some variables for the running totals.
If I just had the time ... and my boss was paying me for solving problems on SQLServerCentral
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply