JS activity or Query Activity

[rvnth]

Hi there,

I have a requirement where I need to determine whether the DateOnly(column1 + 11 days) is greater than the DateOnly(column2 + 21 days). If this condition is met, I'll return column1 and check if it matches today's current date. Otherwise, I'll return column2 and check if it matches today's current date.

I've set up a query activity to retrieve a group of audiences, and now I need to filter out the audience further based on the above conditions and qualified users need to be processed further in the workflow. I'm wondering if this filtering can be done directly through the query activity itself, or if I need to use a JavaScript (JS) activity instead.

If it needs to be done through a JS activity, I would appreciate it if you could provide some sample code references.

I understand that this can be achieved using a CASE statement, as shown below, but I'm uncertain if it's possible through the JS activity:

SELECT
CASE
WHEN DATE_ADD(column1, INTERVAL 11 DAY) > DATE_ADD(column2, INTERVAL 21 DAY) THEN DATE_ADD(column1, INTERVAL 11 DAY)
WHEN DATE_ADD(column2, INTERVAL 21 DAY) > DATE_ADD(column1, INTERVAL 11 DAY) THEN DATE_ADD(column2, INTERVAL 21 DAY)
END AS final_result
FROM
your_table_name
WHERE
CASE
WHEN DATE_ADD(column1, INTERVAL 11 DAY) > DATE_ADD(column2, INTERVAL 21 DAY) THEN DATE_ADD(column1, INTERVAL 11 DAY)
WHEN DATE_ADD(column2, INTERVAL 21 DAY) > DATE_ADD(column1, INTERVAL 11 DAY) THEN DATE_ADD(column2, INTERVAL 21 DAY)
END = CURRENT_DATE;

[Amine_Abedour · Accepted solution]

Hello @rvnth,

it's feasible via a query activity, bellow an exemple.

Content of the expression is :

Iif(AddDays(DateOnly(@created) , 11) > AddDays(DateOnly(@lastModified) , 21) , AddDays(DateOnly(@created) , 11) , AddDays(DateOnly(@lastModified) , 21))

I took @creates as your column1 and @lastModified as your column2.

The sql query generated by this is :

SELECT R0.iRecipientId
FROM NmsRecipient R0
WHERE (
		CASE 
			WHEN AddDays(DateOnly(R0.tsCreated, 'Europe/Paris'), 11) > AddDays(DateOnly(R0.tsLastModified, 'Europe/Paris'), 21)
				THEN AddDays(DateOnly(R0.tsCreated, 'Europe/Paris'), 11)
			ELSE AddDays(DateOnly(R0.tsLastModified, 'Europe/Paris'), 21)
		END = DateOnly(GetDate(), 'Europe/Paris')
		)

Br,

[rvnth]

Hi @Amine_Abedour ,

Thank you so much for your help. This is actually giving me the column2+21 days = getDate(currentDate) values. Here is the attached screenshot:

I want to get the end result whichever(column1 or column2) date is bigger that should check if it is equal to currentDate then provide those in result.

please suggest

[rvnth]

I am trying to construct something as below
MAX(AddDays(DateOnly(@column2) , 21),AddDays(DateOnly(@column1) , 11)) equal to currentDate

but it is giving me an error. Please suggest

[rvnth]

Hi @Amine_Abedour , 

I'd like to provide an update. After implementing the solution you suggested, the below now yields the desired results. However, I would appreciate it if someone could validate this and confirm it.

Iif(AddDays(DateOnly(@created) , 11) > AddDays(DateOnly(@lastModified) , 21) , AddDays(DateOnly(@created) , 11) , AddDays(DateOnly(@lastModified) , 21)) equal to currentDate

 Thank you in advance