Tell me if this is what you're looking for:
1) *BinD Records For a Transaction, most recent BinD first:*
SELECT BinD.BinD_ID,
BinD.Bin_ID,
Bin.Bin_Name,
BinD.Serial_ID,
Serial.Serial_Name,
BinD.Bin_Quantity
FROM BinD
INNER JOIN Bin ON BinD.Bin_ID = Bin.Bin_ID
INNER JOIN Serial ON BinD.Serial_ID = Serial.Serial_ID
WHERE BinD.Transaction_ID = 63
ORDER BY BinD.BinD_ID DESC
2) *BindD Records For a Serial, most recent transactions first, the sorted
by Bin:*
SELECT BinD.Transaction_ID,
BinD.Bin_ID,
Bin.Bin_Name,
BinD.Serial_ID,
Serial.Serial_Name,
BinD.Bin_Quantity
FROM BinD
INNER JOIN Bin ON BinD.Bin_ID = Bin.Bin_ID
INNER JOIN Serial ON BinD.Serial_ID = Serial.Serial_ID
WHERE BinD.Serial_ID = 2
ORDER BY BinD.Transaction_ID DESC
On Jan 9, 2008 2:25 AM, Gonzo Rock <[EMAIL PROTECTED]> wrote:
> I know there are a few SQL savants on this list so I wanted to see if I
> might learn something from one of you. I believe this is a very
> sophisticated query... at least for me it is .... A real head scratcher
> ;-) And I really need to figure this out!
>
> So here goes...
> There is some Lot of parts being manufactured ... so we have the Lot
> Table.
>
> Each part in the Lot has a unique Serial ID to identify it in the Lot it
> belongs to... so we have the Serial Table.
>
> Each unique serialized part is broken up into multiple pieces and each
> piece receives a Bin designation for it's Grade so we have a Bin Table.
>
> The pieces are grades several time... and every time the Lot is Graded the
> results for each Serialized part in the Lot are stored in the BinD (D for
> Detail), table and they all share the same incrementing Transaction_ID for
> when the Grading was done.
>
> Now the task is two fold.
>
> 1st task - Given a Lot_ID, query the most recent BinD_Quantities recorded
> for each of the Serial_ID's in the Lot.
>
> I've been trying to do this by creating loops inside of loops inside of
> loops, each one running a query at the center of each respective loop.
>
> IE Query Lots,
> Loop through Lots and query Serials,
> Loop through Serials and query BinD records.
>
> But heres the rub(at least for me ;-).... How to query the most recent set
> of BinD records? Or better yet a single query that pulls the results of
> all the serials for a Trans_ID broken out by Bin_ID, Summed Bin Quantity.
>
> 2nd task - Given a Serial_ID, generate a table listing showing the various
> values the Bins held in the past up to the most recent Grading...(ie, the
> serial with the highest Transaction_ID)
>
> Lot Table
> Lot_ID, Dev_Name
> ==============
> 1, 2001
> 2, 2001-A
> 3, 20012
>
> Serial Table
> Serial_ID, Serial_Name, Lot_ID (relates to the Lot Table)
> ==============
> 1, 1003204, 2
> 2, 1003205, 2
> 3, 1003250, 2
> 4, 1001231, 1
> 5, 100121, 1
>
> Bin Table
> Bin_ID, Bin_Name
> ==============
> 1, Grade A
> 2, Grade B
> 3, Grade C
> 4, Grade D
>
> BinD Detail Table
> BinD_ID, Bin_ID, Serial_ID, Bin_Quantity, Transaction_ID(of the test that
> generated Group results)
> ==============
> 1,1,1,23,31
> 2,2,1,15,31
> 3,3,1,14,31
> 4,4,1,20,31
> 5,1,2,13,31
> 6,2,2,13,31
> 7,3,2,20,31
> 8,4,2,13,31
> 9,1,3,23,31
> 10,2,3,191
> 11,3,3,20,31
> 12,4,3,13,31
> 13,1,1,23,63
> 14,2,1,15,63
> 15,3,1,14,63
> 16,4,1,20,63
> 17,1,2,13,63
> 18,2,2,13,63
> 19,3,2,20,63
> 20,4,2,13,63
> 21,1,3,23,63
> 22,2,3,19,63
> 23,3,3,20,63
> 24,4,3,13,63
> 25,1,4,12,67
> 26,2,4,0,67
> 27,3,4,14,67
> 28,4,4,17,67
> 29,1,5,9,67
> 30,2,5,10,67
> 31,3,5,24,67
> 31,4,5,18,67
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:296417
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
