Auto calculate dates based on PAC-0

Question

R-Date Target Date (Tentative) Activity PAC-4Project Team Review (Phase 2 team approval)PAC-3 Desk Review with Product OperationsPAC-0 Pre-PAC Review Hey guys, Here is my usecase: I want to use the table transformer macro in confluence to automatically calculate dates for PAC-3 and PAC-4 based on PAC-0 date that I would manually provide. Ex: I'll mention PAC-0 date as 20-May-22 and I want PAC-3 to automatically calculate 17-May-22 and PAC-4 date as 16-May-22. I'm getting an error in the SQL query. Can someone help pls?

Katerina Kovriga _Stiltsoft_ · Answer

Hi @sindhushandilyaa ,

We assumed that the number after your PAC- is a number of days to subtract from the PAC-0. So our developers came up with the following solution:

Don't forget to set the correspondent date format:

And use the following SQL query:

SELECT T1.*, FORMATDATE(ADDDATE(TT1.'Base', INTERVAL T1.'R-Date'.split("PAC")->1 DAY)) AS 'Target Date (Tentative)' FROM T1, (SELECT 'Target Date (Tentative)' AS 'Base' FROM T1 WHERE 'R-Date'="PAC-0") AS TT1

Hope this helps your case.