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
Downtime Table
Resulting Schedule
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
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.
The order table looks generated according to the ProductionHours column. If you were looking for a scripted solution:
##############################################################################
# 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:
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.
The orders table is populated via a form and insert query.