OLAP Running Count

Database
Enthusiast

OLAP Running Count

Hi All,

 

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
1 REPLY
Teradata Employee

Re: OLAP Running Count

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.