Critical Path SQL Traversing trees of hierarchal data

Critical Path SQL Traversing trees of hierarchal data structures, solving cumulative minimum/maximum problems for critical paths in SQL Thane Schaffer: Database Administrator/Architect, Transcore, Albuquerque NM http: //sqlambassador. blogspot. com, email: thaneschaffer@gmail. com

Critical Path SQL § Thane Schaffer § Current employment DBA with Transcore, an Engineer To Order (ETO) § R&D, manufacturingof RFID tolling equipment, i. e. tags & readers, bidding software projects to governmental agencies § Support MS SQL Server 2005, 2008 R 2, 2014, Oracle offsite/onsite clients, Postgres Open GIS, my. SQL, and Visibility ETO ERP Administration § Previously consulted/employed § Daimler Trucks North America (Freightliner): data modeler for a global Teamcenter PLM reporting (Oracle OLAP) § Gallo Wines (Oracle data migration/interfaces); Beverage industry § Precision Castparts (Delphi & VB app dev/Oracle design OLTP); Aerospace § Clackamas County Tax Assessors office (Informix Y 2 K conversion); Local government § Mitsubishi Silicon America (DEC VAX & VB app dev/Oracle OLTP); Silicon wafer manufacturer § HMO – Oregon (HP 3000 Image hierarchal DB); Healthcare § SEH America (DEC VAX/VMS app dev/DEC r. DB); Silicon wafer manufacturer § ITI Technologies (HP 3000/9000 Image hierarchal DBMS); Motherboards § Electro Scientific Industries (DEC VAX app dev/RMS/Datatrieve/operations); Laser trimmers – Silicon wafers § § § linkedin: thane schaffer blog: http: //sqlambassador. blogspot. com email: thane. schaffer@transcore / thaneschaffer@gmail. com 2 | 2/20/2016 SQLSaturday #478 – Albuquerque 2016

Critical Path SQL § Critical path definition: § An algorithm that examines a constraint and all possible outcomes for that constraint so as to understand the maximum/minimum duration of that constraint § Examples: § § Project management examining manpower or time constraints Travel: salesman, transportation and logistics Designing or building complex products: motherboards, cars, trucks War/Military actions and objectives: seizing territories § Answer for shortest path? 1) ADF 2) AE 3) BC 3 | 2/20/2016 SQLSaturday #478 – Albuquerque 2016

Critical Path SQL §Another example: §Additional variables add to complexity §Direct labor hours, machine capacity/time, machine setup time/costs, material costs, acquisition/lead times, queue/wait times, move times (DRAG), and many variations in labor, machines compound issues 4 | 2/20/2016 SQLSaturday #478 – Albuquerque 2016

Critical Path SQL OLTP Hierarchal data can be found in many places in the business 5 | 2/20/2016 SQLSaturday #478 – Albuquerque 2016

Critical Path SQL OLTP Hierarchal data structures in your databases Employee table 6 | 2/20/2016 SQLSaturday #478 – Albuquerque 2016 Bill of Materials table

Critical Path SQL § Traversing hierarchal data structures in proper order that’s meaningful for the business § Computer science data structure courses teach about pre-order, inorder, post-order traversals. Inverted tree structure requires pre-order for most business needs § Pre-order starts at the root and travels down and left, working right / up § Produces an ordered set § {F, B, A, D, C, E, G, I, H} 7 | 2/20/2016 SQLSaturday #478 – Albuquerque 2016

Critical Path SQL Syntax for recursive common table expressions: WITH CTEbom (column 1, column 2, …column. N) as ( select column 1, column 2, …column. N from table 1 (anchor query) UNION ALL select column 1, column 2, …column. N from table 2 t 2 inner join CTEbom ct on ct. column 1 to t 2. column 1 (recursive query & join back on itself) ) select column 1, column 2, …column. N from CTEbom option (maxrecursion 6) Basic example: Solution: SQLSaturday 478_Demo. Project. ssmssln, Open: BTree. BOMExample. sql 8 | 2/20/2016 SQLSaturday #478 – Albuquerque 2016

Critical Path SQL Adventure. Works example: Solution: SQLSaturday 478_Demo. Project. ssmssln, Open: Adventure. Works. BOMCTE. sql 9 | 2/20/2016 SQLSaturday #478 – Albuquerque 2016

Critical Path SQL §Transcore examples: §Solution: SQLSaturday 478_Demo. Project. ssmssln, Open: Transcore. Examples. sql §Proc finds maximum cumulative lead time: AMTC_FETCH_BOM_CUM_LT §Function returns result set of BOM structure: AMTC_FIND_BOM_LT §Vis. Maintenance (Bart) application §How messy can this all get, see old Oracle example: Oracle. BOMExample. sql 10 | 2/20/2016 SQLSaturday #478 – Albuquerque 2016

Thanks to our Sponsors!

Join us at our Networking happy hour immediately following the closing remarks today Fox & Hound 4301 The Lane at 25 NE, Albuquerque, NM 87109 Drinks, Light appetizers, Raffle drawings, Pool tables, dart boards, and more networking with speakers, organizers, and attendees. See you there!