Gonzo, I've got to say I've had a time trying to wrap my head around
your data structure without really knowing how it is populated. I have
generated some SQL here, but I don't really know if I have interpreted
the data correctly. For starters are there only 4 bins total, or does
EACH serial have its own set of four bins?
For task 1, the fact that there are multiple entries for each bin in
every grading, leads me to believe there are a separate set of bins for
each serial. I accomplished this with an intermediate temp table to be
easier to understand.
Of course, task 1.5 confuses me a bit though since you seem to want to
sum bin quantities with the same bin_id together across multiple
serials.
Task 2 seems odd, since given your example data the quantity of a given
bin NEVER changes between gradings for a given serial. This may just be
bad test data though.
One key piece of information you left out was what DBMS you are using.
My example below works on MS SQL Server 2000 and 2005. I hope the line
breaks don't get too messed up.
~Brad
-- declare tables to match data structure
declare @lot table
(lot_id int,
dev_name varchar(50))
declare @serial table
(serial_id int,
serial_name varchar(50),
lot_id int)
declare @bin table
(bin_id int,
bin_name varchar(50))
declare @bind table
(bind_id int,
bin_id int,
serial_id int,
bin_quantity int,
transaction_id int)
-- populate them with supplied test data.
insert into @lot
select 1, '2001'
union select 2, '2001-a'
union select 3, '20012'
insert into @serial
select 1, '1003204', 2
union select 2, '1003205', 2
union select 3, '1003250', 2
union select 4, '1001231', 1
union select 5, '100121', 1
insert into @bin
select 1, 'Grade A'
union select 2, 'Grade B'
union select 3, 'Grade C'
union select 4, 'Grade D'
insert into @bind
select 1,1,1,23,31
union select 2,2,1,15,31
union select 3,3,1,14,31
union select 4,4,1,20,31
union select 5,1,2,13,31
union select 6,2,2,13,31
union select 7,3,2,20,31
union select 8,4,2,13,31
union select 9,1,3,23,31
union select 10,2,3,19,31
union select 11,3,3,20,31
union select 12,4,3,13,31
union select 13,1,1,23,63
union select 14,2,1,15,63
union select 15,3,1,14,63
union select 16,4,1,20,63
union select 17,1,2,13,63
union select 18,2,2,13,63
union select 19,3,2,20,63
union select 20,4,2,13,63
union select 21,1,3,23,63
union select 22,2,3,19,63
union select 23,3,3,20,63
union select 24,4,3,13,63
union select 25,1,4,12,67
union select 26,2,4,0,67
union select 27,3,4,14,67
union select 28,4,4,17,67
union select 29,1,5,9,67
union select 30,2,5,10,67
union select 31,3,5,24,67
union select 31,4,5,18,67
-- declare temp table to hold intermediate values.
declare @tmp_latest_tran_per_serial table
(serial_id int,
latest_transaction_id int)
-- task 1 Given a Lot_ID, query the most recent BinD_Quantities
recorded for each of the Serial_ID's in the Lot.
-- First, find the last transaction_id for each serial. (They all
appear to be the same in this case)
insert into @tmp_latest_tran_per_serial (serial_id,
latest_transaction_id)
select s.serial_id, max(bd.transaction_id)
from @serial s
inner join @bind bd on s.serial_id = bd.serial_id
where s.lot_id = 2
group by s.serial_id
-- Then select out the quantities for that serial and transaction.
select l.dev_name, s.serial_name, b.bin_name, bd.bin_quantity as
latest_bin_quantity
from @tmp_latest_tran_per_serial t
inner join @bind bd on t.latest_transaction_id = bd.transaction_id
and t.serial_id = bd.serial_id
inner join @bin b on bd.bin_id = b.bin_id
inner join @serial s on t.serial_id = s.serial_id
inner join @lot l on s.lot_id = l.lot_id
order by s.serial_name, b.bin_name
-- end task 1
-- task 1.5 a single query that pulls the results of all the serials
for a Trans_ID broken out by Bin_ID, Summed Bin Quantity
select bd.transaction_id, b.bin_name, sum(bd.bin_quantity) as
sum_of_bin_quantity
from @lot l
inner join @serial s on l.lot_id = s.lot_id
inner join @bind bd on s.serial_id = bd.serial_id
inner join @bin b on bd.bin_id = b.bin_id
where bd.transaction_id = 63
group by bd.transaction_id, b.bin_id, b.bin_name
-- end task 1.5
-- task 2 Given a Serial_ID, generate a table listing showing the
various values the Bins held in the past up to the most recent Grading
select s.serial_name, b.bin_name, bd.bin_quantity
from @lot l
inner join @serial s on l.lot_id = s.lot_id
inner join @bind bd on s.serial_id = bd.serial_id
inner join @bin b on bd.bin_id = b.bin_id
where s.serial_id = 2
order by b.bin_name, bd.transaction_id
-- end task 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296281
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
