# Padding dates of one table based off a different table **URL:** \ **Category:** Ignition\ **Tags:** ignition79, database, vision\ **Created:** [December 14, 2023, 6:13pm UTC](https://forum.inductiveautomation.com/t/padding-dates-of-one-table-based-off-a-different-table/83157 "2023-12-14T18:13:51Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Micky.Franks](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Micky.Franks](https://forum.inductiveautomation.com/u/Micky.Franks)\ **Post date:** [December 14, 2023, 6:13pm UTC](https://forum.inductiveautomation.com/t/padding-dates-of-one-table-based-off-a-different-table/83157/1 "2023-12-14T18:13:52Z")
I have two MySQL tables. One table has an "order" with a start date, another table has scheduled downtime. I know how many hours will be needed to fulfill the order but also need to account for downtime to calculate a delivery date. What's the best way to go about this? Order Table ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/f/7fb8432ac08f6d886de3ed02c24273564683fefd.png) Downtime Table ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/8/787be1b774dab591d8ee203319d1ddbe35a0a8e2.png) Resulting Schedule ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/5/a5f5872f6073c4f9002b042981ec435b85d14c08.png) ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [December 14, 2023, 6:23pm UTC](https://forum.inductiveautomation.com/t/padding-dates-of-one-table-based-off-a-different-table/83157/2 "2023-12-14T18:23:08Z")
The formula is `Delivery date = start date + down time + expected + production hours`? If this is not it please specify what it is. If you can create or calculate a StartDateTime in your Contracts table (like you have in your DownDay table) You can start like ```auto SELECT ContractId, StartDateTime as startTime, ADDDATE(StartDateTime, INTERVAL ProductionHours HOUR) as initialDeliveryTime FROM Contract ``` This will tell you the delivery date sans any downtime. You could then check if the start/end dates of any dowtime are between the startTime/initialDeliveryTime and if so add to it appropriately. This is a bit complex and you may want to include scripting in calculating this as opposed to strictly SQL. I don't doubt it can be down strictly in SQL but it may get hairy fast. ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [December 14, 2023, 8:17pm UTC](https://forum.inductiveautomation.com/t/padding-dates-of-one-table-based-off-a-different-table/83157/3 "2023-12-14T20:17:01Z")
The order table looks generated according to the ProductionHours column. If you were looking for a scripted solution: ```python ############################################################################## # Sample datasets downHeaders = ['StartDateTime', 'EndDateTime'] downList = [['2023-12-24 07:00:00', '2023-12-26 07:00:00'], ['2024-01-01 07:00:00', '2024-01-02 07:00:00'], ['2024-03-29 07:00:00', '2024-03-30 07:00:00'] ] # parse all the dates I was too lazy to do individually . ;) downList = [[system.date.parse(item) for item in row] for row in downList] # I reduced the contracts down to the contract number and production hours. contractHeaders = ['contract', 'ProductionHours'] contractList = [[1, 0], [2, 8], [3, 10], [4, 24], [5, 88], [6, 3], [7, 12], [8, 24], [9, 80], [10, 8] ] # Create the sample datasets downData = system.dataset.toPyDataSet(system.dataset.toDataSet(downHeaders, downList)) contractData = system.dataset.toPyDataSet(system.dataset.toDataSet(contractHeaders, contractList)) ############################################################################# def overlap(start1, end1, start2, end2): ''' check if two date ranges (start1, end1), start2, end2) overlap ''' return system.date.isBetween(start1, start2, end2) \ or system.date.isBetween( end1, start2, end2) \ or system.date.isBetween(start2, start1, end1) \ or system.date.isBetween( end2, start1, end1) # Set the production start date. startDateTime = productionStart = system.date.parse('2023-12-22 07:00:00') productionHeaders = ['contract', 'ProductionHours', 'DownTimeHours', 'StartDateTime', 'EndDateTime'] productionList = [] for row in contractData: downHours = 0 # Set the planned production end date endDateTime = system.date.addHours(startDateTime, row['ProductionHours']) for downDay in downData: # check for holiday overlap, and add the extra downtime, if needed if overlap(startDateTime, endDateTime, downDay['StartDateTime'], downDay['EndDateTime']): downHours = system.date.hoursBetween(downDay['StartDateTime'], downDay['EndDateTime']) endDateTime = system.date.addHours(endDateTime, downHours) break productionList.append([row['contract'], row['ProductionHours'], downHours, startDateTime, endDateTime]) startDateTime = endDateTime productionData = system.dataset.toDataSet(productionHeaders, productionList) ``` Dataset out: ```plaintext row | contract | ProductionHours | DownTimeHours | StartDateTime | EndDateTime -------------------------------------------------------------------------------------------------------------- 0 | 1 | 0 | 0 | Fri Dec 22 07:00:00 EST 2023 | Fri Dec 22 07:00:00 EST 2023 1 | 2 | 8 | 0 | Fri Dec 22 07:00:00 EST 2023 | Fri Dec 22 15:00:00 EST 2023 2 | 3 | 10 | 0 | Fri Dec 22 15:00:00 EST 2023 | Sat Dec 23 01:00:00 EST 2023 3 | 4 | 24 | 0 | Sat Dec 23 01:00:00 EST 2023 | Sun Dec 24 01:00:00 EST 2023 4 | 5 | 88 | 48 | Sun Dec 24 01:00:00 EST 2023 | Fri Dec 29 17:00:00 EST 2023 5 | 6 | 3 | 0 | Fri Dec 29 17:00:00 EST 2023 | Fri Dec 29 20:00:00 EST 2023 6 | 7 | 12 | 0 | Fri Dec 29 20:00:00 EST 2023 | Sat Dec 30 08:00:00 EST 2023 7 | 8 | 24 | 0 | Sat Dec 30 08:00:00 EST 2023 | Sun Dec 31 08:00:00 EST 2023 8 | 9 | 80 | 24 | Sun Dec 31 08:00:00 EST 2023 | Thu Jan 04 16:00:00 EST 2024 9 | 10 | 8 | 0 | Thu Jan 04 16:00:00 EST 2024 | Fri Jan 05 00:00:00 EST 2024 ``` That said, since we don't know how the orders table is populated, it makes it a bit more difficult. ---
**Author:** ![Micky.Franks](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Micky.Franks](https://forum.inductiveautomation.com/u/Micky.Franks)\ **Post date:** [December 14, 2023, 8:33pm UTC](https://forum.inductiveautomation.com/t/padding-dates-of-one-table-based-off-a-different-table/83157/4 "2023-12-14T20:33:20Z")
The orders table is populated via a form and insert query.