Determining Delta Between Dates From One Record to Next Record

[Kaempfe]

I have the data set example in the table below.



I would like to create a Function that allows me to use the function in an Access Query that will determine the leadtime in WORKING DAYS between the DATE_COMPLETED in one record vs the CDATE_COMPLETED of the next record. I would need to order the tasks by the TASK_RECORD_ORDER within a grouping of the MODEL_SKU_NUMBER.

Example....
For MODEL_SKU_NUMBER ABC123, order the tasks by the TASK_RECORD_ORDER, find the DATE_COMPLETED of Step 1 and the CompletionDate of Step 2, log the DELTA IN WORKING DAYS (M-F) between the two dates as the LEADTIME for Step 2. Step 1 will not have a Leadtime and any Tasks that have a blank for either DATE_COMPLETED will not have Leadtime.

There are instances where the tasks could be completed out of order so you might get a negative Leadtime. That would be OK as the negative would indicate our of order and I can figure out if I exclude them or not, later.

I'm thinking this is a function where I place the Function into a Query Field vs pulling a data set and having it run through the data set and store the data in another table. But I'm open to suggestions.

Before anyone wonders....yes I could do this in excel but I was hoping to create a dynamic query that will run daily and constantly provide updated data as steps are completed or models added.

Thanks!

MODEL_SKU_NUMBER TASK_RECORD_ORDER DATE_COMPLETED LEADTIME XYZ-123 0 7/11/2017 XYZ-123 1 7/24/2017 10 XYZ-123 2 8/3/2017 9 XYZ-123 3 7/31/2017 -4 XYZ-123 4 10/24/2017 62 XYZ-123 5 8/3/2017 -59 XYZ-123 6 8/3/2017 1 XYZ-123 7 XYZ-123 8 8/25/2017 XYZ-123 9 8/16/2017 -8 XYZ-123 10 8/17/2017 2 XYZ-123 11 8/25/2017 7 XYZ-123 12 8/24/2017 -2

[June7]

Pulling value from previous or next record accomplished with subquery. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

[Kaempfe]

Pulling value from previous or next record accomplished with subquery. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

Thanks! I'm not 100% clear how I would use it. Would you suggest making this a Function where I can call the function within a query to populate a new table with the Model, Task and Leadtime?

[June7]

I just realized you want to exclude weekend days. So yes, this will require VBA. There are many examples of function to calculate workdays within a date range. Here is one https://docs.microsoft.com/en-us/off...ween-two-dates. This will still require pulling value from another record. Following query does that and calls function to calculate lead time:

SELECT *, Work_Days((SELECT TOP 1 Dupe.Date_Completed FROM Table2 AS Dupe WHERE Dupe.Model_SKU_Number = Table2.Model_SKU_Number AND Dupe.Task_Record_Order<Table2.Task_Record_Order ORDER BY Dupe.Task_Record_Order DESC),Date_Completed) AS LeadTime FROM Table2;

[Kaempfe]

Pulling value from previous or next record accomplished with subquery. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

The query is taking a long time. Sometimes it crashes. I could work to pare down the data set. Partially bc I would like to process the data set as is and partially because I would like to learn a new skill....
Could you help me convert this into VBA code so I could pull a data set and export the summary data to another table? I have found that using this method is much faster than running a function within a query.

Thanks!

[Kaempfe]

I'm using this code to get the prior dates and then a public Function to measure working days between the two dates.

SELECT NPI_DATE_TRACKER_MODEL_AND_SKU_HEADER_AND_TASK_VIE W.RELEASE_CLASS, NPI_DATE_TRACKER_MODEL_AND_SKU_HEADER_AND_TASK_VIE W.MODEL_SKU_NUMBER, NPI_DATE_TRACKER_MODEL_AND_SKU_HEADER_AND_TASK_VIE W.TASK_RECORD_ORDER, NPI_DATE_TRACKER_MODEL_AND_SKU_HEADER_AND_TASK_VIE W.DATE_COMPLETED, (SELECT TOP 1 Dupe.DATE_COMPLETED
FROM NPI_DATE_TRACKER_MODEL_AND_SKU_HEADER_AND_TASK_VIE W AS Dupe
WHERE Dupe.MODEL_SKU_NUMBER = NPI_DATE_TRACKER_MODEL_AND_SKU_HEADER_AND_TASK_VIE W.MODEL_SKU_NUMBER
AND Dupe.TASK_RECORD_ORDER < NPI_DATE_TRACKER_MODEL_AND_SKU_HEADER_AND_TASK_VIE W.TASK_RECORD_ORDER
ORDER BY Dupe.TASK_RECORD_ORDER DESC, Dupe.MODEL_SKU_NUMBER) AS PriorValue
FROM NPI_DATE_TRACKER_MODEL_AND_SKU_HEADER_AND_TASK_VIE W
WHERE (((NPI_DATE_TRACKER_MODEL_AND_SKU_HEADER_AND_TASK_ VIEW.RELEASE_CLASS) Like "19*"));

[June7]

Yes, subqueries and function calls can slow performance. This particular calculation is not one I have ever needed. Not really sure how to improve.

Suggestion for future - don't use all caps in naming convention - harder to read.

[Kaempfe]

I agree on the all caps. It is not my database. I'm just querying it. But suggestion noted for my own work for sure.

So is there no way to pull the data using DAO data set and lop through the data and then export the summary line to a new table?

[June7]

Not seeing the function called in that query and it still has performance issue?

An INSERT SELECT action SQL should be able to write records to table - but if the query is crashing now this will likely fail as well.

Certainly could use VBA and write data to 'temp' table with looping code that reads each record, saves values to variables, calculates workdays, writes record to table. Again, gets complicated because still involves pulling data from a pair of records. Code would have to look for change in SKU as well.