Hello,
I have an SQL table that's essentially just products being consumed in the fabrication process. I would like to (use what I think is the LAG function) return the last "consume" of each product type for each unit produced. There is also a timestamp attached to every Action (build or consume) if that helped.
I can start with a simple select statement to get something like this in Oracle:
| Action | ProductName | ProductSerial |
|---|---|---|
| Build | Unit 9 | 1009 |
| Consume | Product 1 | E567 |
| Build | Unit 8 | 1008 |
| Build | Unit 7 | 1007 |
| Build | Unit 6 | 1006 |
| Build | Unit 5 | 1005 |
| Consume | Product 3 | D001 |
| Build | Unit 4 | 1004 |
| Build | Unit 3 | 1003 |
| Build | Unit 2 | 1002 |
| Build | Unit 1 | 1001 |
| Consume | Product 3 | C789 |
| Consume | Product 2 | B456 |
| Consume | Product 1 | A123 |
I want to be able to get this:
| Action | ProductName | ProductSerial | Product1 | Product2 | Product3 |
|---|---|---|---|---|---|
| Build | Unit 9 | 1009 | Product 1 - E567 | Product 2 -B456 | Product 3 - D001 |
| Consume | Product 1 | E567 | |||
| Build | Unit 8 | 1008 | Product 1 - A123 | Product 2 -B456 | Product 3 - D001 |
| Build | Unit 7 | 1007 | Product 1 - A123 | Product 2 -B456 | Product 3 - D001 |
| Build | Unit 6 | 1006 | Product 1 - A123 | Product 2 -B456 | Product 3 - D001 |
| Build | Unit 5 | 1005 | Product 1 - A123 | Product 2 -B456 | Product 3 - D001 |
| Consume | Product 3 | D001 | |||
| Build | Unit 4 | 1004 | Product 1 - A123 | Product 2 -B456 | Product 3 - C789 |
| Build | Unit 3 | 1003 | Product 1 - A123 | Product 2 -B456 | Product 3 - C789 |
| Build | Unit 2 | 1002 | Product 1 - A123 | Product 2 -B456 | Product 3 - C789 |
| Build | Unit 1 | 1001 | Product 1 - A123 | Product 2 -B456 | Product 3 - C789 |
| Consume | Product 3 | C789 | |||
| Consume | Product 2 | B456 | |||
| Consume | Product 1 | A123 |
So every time there is a new "Build" action, I would like to return the last "Consume" for each of the products in the bill of materials. I’ve tried iterations of CASE statements but am unable to link the build to all of it’s components. Unfortunately, I can only reproduce the top table with a simple select statement:
Select
Action as Build
,Product_Name as ProductName
,Product_Serial as ProductSerial
,Concat(Concat(Product_Name, ' - '), Product_Serial) as Barcode --(Not in Table 1 above)
Where Action in ('Build', 'Consume')
And Time_Stamp > sysdate - 90
From TraceDB
I know there has to be some sort of link to between Build timestamp and the Consume timestamp but I am drawing a blank and am 100% stumped.
Any help is greatly appreciated.