Forum Discussion
How calculate a column based on
I need to calculate the Number of days since either 1. Start of a Job 2. Since the last Type of Event There is a relationship between the Job Table and Event table. There may be multiple...
Here is an example of the data:
| UMC Status | Date of Incident | Project Number |
| 10/26/14 | 6262 | |
| UMC First Aid | 01/21/15 | 6262 |
| Recordable | 01/27/15 | 6262 |
| UMC First Aid | 06/18/15 | 6262 |
| UMC First Aid | 09/03/15 | 6262 |
| 10/08/15 | 6262 | |
| Recordable | 01/14/15 | 6265 |
| Recordable | 07/19/16 | 6623 |
| Notification | 08/09/16 | 6623 |
| Notification | 10/03/16 | 6623 |
| Notification | 10/07/16 | 6623 |
| 12/01/16 | 6623 | |
| Notification | 05/15/17 | 6623 |
| Recordable | 06/16/17 | 6623 |
| UMC First Aid | 06/29/17 | 6623 |
| UMC First Aid | 03/02/17 | 6881 |
| Recordable | 04/21/17 | 6881 |
| UMC First Aid | 05/10/17 | 6881 |
| UMC First Aid | 06/08/17 | 7000 |
Any further ideas? If you could help me with the syntax for a function for determining the set of UMC Safety Incident Log rows that are related to the udJob row that is being evaluated, I think I could take it from there. If there was a "Recordable" event, I am setting a Custom Column to 1. That is working. So I could perform a sum on the set of related rows and if > 0, then use the Max Incident Date for that related data set, otherwise set the value to the StartDate in udJob.
One twist to this is that there may be a NULL set of related rows if a Recordable event has not occurred.