Been struggling trying come up with a query to solve this problem - your expert help is truly appreciated.
I have a table with a list of tasks with due dates and actual complete dates per the snippet below.
The desired result is count of tasks behind schedule per a given date - the part I'm struggling with is if a task has a completed date that is after the due date, then it should be considered late for that date and any date after until the date it was actually completed.
I've been attempting multiple combinations of OLAP functions but can't quite get the desired result - your support is greatly appreciated, thanks so much!
Task Schedule_Complete Actual_Complete task 1 3/10/18 NULL task 2 3/10/18 3/11/18 task 3 3/11/18 3/09/18 task 4 3/11/18 NULL Desired Result: Date Behind_Schedule 3/10/18 2 —task 1 is not complete and task 2 completed late 3/11/18 2 -task 1 and task 4 are not complete, task 3 completed ahead of schedule and task 2 is no longer behind schedule
Instead of an OLAP function, consider using date expansion / simple aggregation, something like this:
SELECT calendar_date, count(*) as Behind_Schedule FROM Sys_Calendar.Calendar c JOIN Task_Dates t ON c.calendar_date between t.Schedule_Complete and COALESCE(t.Actual_Complete - 1, CURRENT_DATE) WHERE t.Schedule_Complete < t.Actual_Complete or t.Actual_Complete is NULL
You can also use Teradata's EXPAND ON feature and avoid a product join to the calendar table.