You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
I am wanting to calculate probation and pay increase dates in the below table, can you please assist me with the correct code?
Start Date + 6 months = probation end date
Start Date + 12 months = Year 1: Award increment increase
Start Date + 24 months = Year 2: Award increment increase
Start Date + 36 months = Year 3: Award increment increase
Start Date + 48 months = Year 4: Award increment increase
I have attempted to complete this, however, it returns an error.
Hi @Darcy Godden,
As I see, your periods are not constant: the first one is 6 month, others are one year long. So, we can’t take the first date and add the same date range to it and then to the previous ones, for example, date 1 = start date + 1 year, date 2 = date 1 + 1 year, date 3 = date 2 + 1 year.
Thus, I can suggest using your own description: we take the start date and get other dates by adding different time periods to this first date.
For this purpose, we need to add another column to your source table:
Here is the SQL query for the Table Transformer macro:
SELECT T1.'Start Date', T1.'Description',
FORMATDATE(DATEADD(MONTH, T1.'Period', TT1.'Base'))
AS 'Date' FROM T1,
(SELECT 'Start Date'
AS 'Base' FROM T1 WHERE 'Description'="Probation end date") AS TT1
Then go to the Settings tab of the macro and set the date format as “d M yy” for the case.
Here is your result:
Hope this helps your case.
And if your periods are the same for all the incremented values (for example, 1 year for all the multiple values), you may use a more complex query to get rid of all the manual work.
Here is how your source table will look:
SET @cnt = 0;
SET @date = (SELECT T1.'Start Date' FROM T1);
SET @Des = (SELECT T1.'Description' FROM T1);
SET T1.'Date'=FORMATDATE(DATEADD(month,6,'Start Date')) WHERE 'Description'="Probation end date";
WHILE @cnt < 8
SET @cnt = @cnt + 1;
SET @Des = "Year " + @cnt +": Award increment increase";
SET @date = FORMATDATE(DATEADD(month,12,@date));
INSERT INTO T1 (T1.'Description', T1.'Date') VALUES (@des, @date);
SELECT * FROM T1