Auto calculate dates based on PAC-0

[sindhushandilyaa]

R-Date	Target Date (Tentative)	Activity
PAC-4	<DD-MMM-YYYY>	Project Team Review (Phase 2 team approval)
PAC-3	<DD-MMM-YYYY>	Desk Review with Product Operations
PAC-0	<DD-MMM-YYYY>	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_]

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.