UFN_CORPORATION_GETEMPLOYEES_WITHDATE
Returns the employees for the given constituent based on the currently selected employee relationship types.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ORGANIZATIONID | uniqueidentifier | IN | |
| @ASOFDATE | datetime | IN |
Definition
Copy
CREATE function [dbo].[UFN_CORPORATION_GETEMPLOYEES_WITHDATE]
(
@ORGANIZATIONID uniqueidentifier,
@ASOFDATE datetime = null
)
returns table
as
return (
select
distinct RELATIONSHIP.RELATIONSHIPCONSTITUENTID as [ID]
from dbo.RELATIONSHIP
inner join dbo.EMPLOYEECORPORATERELATIONSHIPTYPE on RELATIONSHIP.RELATIONSHIPTYPECODEID = EMPLOYEECORPORATERELATIONSHIPTYPE.RELATIONSHIPTYPECODEID
where RELATIONSHIP.RECIPROCALCONSTITUENTID = @ORGANIZATIONID
and (
(@ASOFDATE is null)
or
--The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...
--The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...
(@ASOFDATE between isnull(cast(RELATIONSHIP.STARTDATE as date), @ASOFDATE) and isnull(dateadd(ms, -003, dateadd(d, 1, cast(cast(RELATIONSHIP.ENDDATE as date) as datetime))), @ASOFDATE))
)
);