I have established a connection using SQL Loader to one of the authoritative sources in SailPoint ISC. I’m trying to write a query where I need to import employees whose onboard date is current date+14days and offboarded date is current date less than 30 days. What is the query and where to write the query.
Hi @KottiSravya ,
You can configure the SQL queries on Account query settings page to retrieve account data . Please follow here: Account Query Settings
@KottiSravya
You will need to convert the string date (since you are reading from csv) to a date format and compare it with the current date plus 30 days.
Here’s an example assuming the table is named ‘employees’ and the joining date is stored in a column called ‘joining_date’ as a string (in ‘YYYY-MM-DD’ format):
SELECT *
FROM employees
WHERE CAST(joining_date AS DATE) = CAST(GETDATE() AS DATE) + 30;
Thanks
Hi @Anshu_Kunal The above query is not working and I’m not able to filter out the accounts which are onboard date is current date+14days and offboard date is current date< 30 days. Please help me with any other query.
HI @Anshu_Kunal it is throwing the below error.
Could you please try this once
FROM employees
WHERE CAST(joining_date AS DATE) = CAST(CURRENT_DATE AS DATE) + 30;
or
SELECT *
FROM employees
WHERE CAST(joining_date AS DATE) = CAST(SYSDATE() AS DATE) + 30;
Hi Gourab,
Query is aggregating successfully while using these queries but none of the accounts are not popping up in SailPoint.
could you please check once the table(csv) and attribute names?Also make sure you have valid data in environment that satisfies the logic
If the accounts are missing a Unique ID, Email or Last Name they wont appear as Identities. Have you checked the Identity Exception Report on the Identity Profile?
Hi @gourab How to concatenate two quires in SQL Loader. Tried using UNION and AND it didn’t worked for me
CAST([End Date] AS DATE) >= CAST(SYSDATE() AS DATE) - 30 CAST([Start Date] AS date)<= CAST(SYSDATE() AS date) + 14;
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.