SQL Question:

ord_line (order details table): Primary key for ord_line: wh_id ord_id line_id wh_id order_id line_id item_id qty created_date WMD1 ORD01 1 ITMO1 50 2018-05-25 WMD1 ORD01 2 ITMO2 48 2018-05-25 WMD1 ORD01 3 ITM03 50 2018-05-25 WMD1 ORDO2 1 ITMO1 40 2018-07-27 WMD1 ORDO2 2 ITMO3 200 2018-07-27 item_ftp (item footprint information): The primary key for thi table is: wh_id item_id footprint_id case_weight pallet_qty 100 pallet_weight 100 10 wh_id item_id WMD1 ITM01 WMD1 ITM01 WMD1 ITM01 footprint_date 2017-09-01 2018-01-01 1000 50 1000 footprint_id case_qty ITM01_1x10x10 10 ITMO1_1x50x20 50 ITM01_1X20X10 20 ITMO2_1X24X10 24 ITM03_1X50X10 50 200 25 250 2018-06-01 WMD1 ITMO2 240 50 500 2018-01-01 WMD1 ITM03 500 100 1000 2018-01-01 Question 2.1 Write a query to calculate the number of cases ordered (order line quantity divided by case quantity defined in the footprint table) based on the first footprint of the item: 5 order_id line_id item_id qty created_date case_qty ordered_cases ORD01 1 ITM01 50 2018-05-25 10 5 ORD01 2 ITMO2 48 2018-05-25 24 2 ORD01 3 ITM03 50 2018-05-25 50 1 ORDO2 1 ITM01 40 2018-07-27 10 4 ORDO2 2 ITM03 200 2018-07-27 50 4 Question 2.2 A similar question as in 2.1, however, this time you need to use the latest footprint for the item that existed at the time the order line was created. For example, for order ORD01, line_id 1 was created on 2018-05-25. The latest footprint for ITM01 that was created before this date is, ITM01_1X50X20, as it was created on 2018-01-01. Thus, the case qty for this footprint was used in the calculation. Given the sample data, the query should return the following data: order_id line_id item_id qty created_date case_qty ordered_cases ORD01 1 ITM01 50 2018-05-25 50 1 ORD01 2 ITM02 48 2018-05-25 24 2 ORD01 3 ITM03 50 2018-05-25 50 1 ORDO2 1 ITM01 40 2018-07-27 20 2 2 ORDO2 2 ITM03 200 2018-07-27 50 4 ord_line (order details table): Primary key for ord_line: wh_id ord_id line_id wh_id order_id line_id item_id qty created_date WMD1 ORD01 1 ITMO1 50 2018-05-25 WMD1 ORD01 2 ITMO2 48 2018-05-25 WMD1 ORD01 3 ITM03 50 2018-05-25 WMD1 ORDO2 1 ITMO1 40 2018-07-27 WMD1 ORDO2 2 ITMO3 200 2018-07-27 item_ftp (item footprint information): The primary key for thi table is: wh_id item_id footprint_id case_weight pallet_qty 100 pallet_weight 100 10 wh_id item_id WMD1 ITM01 WMD1 ITM01 WMD1 ITM01 footprint_date 2017-09-01 2018-01-01 1000 50 1000 footprint_id case_qty ITM01_1x10x10 10 ITMO1_1x50x20 50 ITM01_1X20X10 20 ITMO2_1X24X10 24 ITM03_1X50X10 50 200 25 250 2018-06-01 WMD1 ITMO2 240 50 500 2018-01-01 WMD1 ITM03 500 100 1000 2018-01-01 Question 2.1 Write a query to calculate the number of cases ordered (order line quantity divided by case quantity defined in the footprint table) based on the first footprint of the item: 5 order_id line_id item_id qty created_date case_qty ordered_cases ORD01 1 ITM01 50 2018-05-25 10 5 ORD01 2 ITMO2 48 2018-05-25 24 2 ORD01 3 ITM03 50 2018-05-25 50 1 ORDO2 1 ITM01 40 2018-07-27 10 4 ORDO2 2 ITM03 200 2018-07-27 50 4 Question 2.2 A similar question as in 2.1, however, this time you need to use the latest footprint for the item that existed at the time the order line was created. For example, for order ORD01, line_id 1 was created on 2018-05-25. The latest footprint for ITM01 that was created before this date is, ITM01_1X50X20, as it was created on 2018-01-01. Thus, the case qty for this footprint was used in the calculation. Given the sample data, the query should return the following data: order_id line_id item_id qty created_date case_qty ordered_cases ORD01 1 ITM01 50 2018-05-25 50 1 ORD01 2 ITM02 48 2018-05-25 24 2 ORD01 3 ITM03 50 2018-05-25 50 1 ORDO2 1 ITM01 40 2018-07-27 20 2 2 ORDO2 2 ITM03 200 2018-07-27 50 4