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 ;-) It's 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 Table (D for 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,19,31 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:296223 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
