365 Days of Daily Coding: Day 148

It’s been raining heaviliy since yesterday here in KL. There are few places where its flooded. I feel blessed that I am living in a place where I feel safe and I am warm. Such are blessings that is easily missed in our everyday lives.

Puzzle #33: Deadlines

I tried the solution in the oracle environment here.

WITH temp1 AS (
SELECT 'Ord893456' AS OrderId, 'Widget' AS Product, 7 AS DeliveryDate FROM DUAL UNION ALL
SELECT 'Ord923654' AS OrderId, 'Gizmo' AS Product,  3 AS DeliveryDate FROM DUAL UNION ALL
SELECT 'Ord187239' AS OrderId, 'Doodad' AS Product, 9 AS DeliveryDate FROM DUAL 
), temp2 AS (
SELECT 'AA-111' AS Part, 'Widget' AS Product, 7 AS DaysToManufacture FROM DUAL UNION ALL
SELECT 'BB-222' AS Part, 'Widget' AS Product, 2 AS DaysToManufacture FROM DUAL UNION ALL
SELECT 'CC-333' AS Part, 'Widget' AS Product, 3 AS DaysToManufacture FROM DUAL UNION ALL
SELECT 'DD-444' AS Part, 'Widget' AS Product, 1 AS DaysToManufacture FROM DUAL UNION ALL
SELECT 'AA-111' AS Part, 'Gizmo' AS Product,  7 AS DaysToManufacture FROM DUAL UNION ALL
SELECT 'BB-222' AS Part, 'Gizmo' AS Product,  2 AS DaysToManufacture FROM DUAL UNION ALL
SELECT 'DD-444' AS Part, 'Doodad' AS Product, 7 AS DaysToManufacture FROM DUAL UNION ALL
SELECT 'BB-222' AS Part, 'Doodad' AS Product, 1 AS DaysToManufacture FROM DUAL 
), temp3 AS (
Select temp1.Product
from temp1 LEFT JOIN temp2
ON temp1.Product = temp2.Product
WHERE DeliveryDate < DaysToManufacture)

SELECT OrderId, Product FROM
temp1 WHERE Product NOT IN (SELECT * FROM temp3);