Need to fetch active employee list who are unalloc...
# suiteqlr
raghav
04/05/2023, 12:32 PMNeed to fetch active employee list who are unallocated today, to fetch this there are 2 steps, can someone please help with the query.
1. Fetch allocated employees for today. - Allocation start date on or before today, and allocation end date on or after today. (if we group by resource, will get the allocated employees list).
2. Now we to filter those employees out from active employee list. and we get the unallocated list.
raghav
04/05/2023, 1:05 PMSomething Like this.
Copy code
SELECT name, internalid
FROM EMPLOYEE
WHERE internalid NOT IN (
SELECT resource.internalid
FROM resourceallocation
WHERE startdate <= today && enddate >= today
GROUP BY resource.internalid
)c
Clay Roper
04/05/2023, 2:20 PM@raghav What's wrong with the query example you posted?
Aside from
internalidr
raghav
04/05/2023, 2:25 PMI am trying to execute this in a suitescript.
Don't have much experience with the query module..
Not sure what is wrong in the script.
c
Clay Roper
04/05/2023, 5:04 PMAre you able to execute
query.runSuiteQL({ query: queryString })queryStringr
raghav
04/06/2023, 10:12 AMI have updated the query and its giving result now, but its not correct.
Copy code
SELECT entityid, id FROM employee WHERE id NOT IN (SELECT allocationResource FROM resourceallocation WHERE startDate <= CURRENT_DATE AND endDate >= CURRENT_DATE GROUP BY allocationResource)raghav
04/06/2023, 10:28 AMWorking query for us I think, testing it further.
those 2 custom checkbox fields need to be false, and remaining stated initially in the problem statement.
Copy code
SELECT id, entityid FROM employee WHERE custentity_implementationteam= 'F' AND custentity_employee_inactive= 'F' AND id NOT IN (SELECT allocationResource FROM resourceallocation WHERE startDate <= CURRENT_DATE AND endDate >= CURRENT_DATE GROUP BY allocationResource)3 Views