This article shows examples of the different applications of SQL fields in a user report.

Example: Create a report that includes a total "Months On Assignment" field.

Listed below are examples of SQL functions which can be utilized in user reports:

Ex. GETDATE() (where the current date is 10/16/2011)

Result: 10/16/2011

Ex. #1 To remove the time portion of a date:

CONVERT (varchar(12), [ASSIGNMENT: Start Date], 101)

(where Start Date is 2/28/2001 11:04:08 AM)

Result: 02/28/2011

Ex. #2 To remove the time portion of a date:

CONVERT (varchar(12), [ASSIGNMENT: Start Date], 103)

(where Start Date is 2/28/2001 11:04:08 AM)

Result: 28/02/2011

Ex. #1 Round (123.4545, 2);

Result: 123.4500

Ex. #2 Round (123.4545, -2);

Result: 100.00

Ex. DATENAME(month, [Assignment: Relocation Effective Date])

(where Relocation Effective Date is 10/28/2001)

Result: October

Ex. DATEPART(month, [Assignment: Relocation Effective Date])

(where Relocation Effective Date is 10/28/2001)

Result: 10

Ex. YEAR(GETDATE())

(where the current date is August 18, 2013)

Result: 2013

Ex. DATEADD(month, 3, GETDATE())

(where the current date is 10/16/2012)

Result: 1/16/2013

Ex. ABS(-10000)

Result: 10000

Ex. LEFT([Employee: Last Name],5)

(where Last Name = ‘Reynolds’)

Result: Reyno

Ex. LEN(‘EQUUS’)

Result: 5

Ex. SUBSTRING(‘EQUUS’, 1,1)

Result: E

Ex. REPLACE('abcdefghicde','cde','xxx')

Result: abxxxfghixxx

Ex. #1 LOWER(‘EQUUS’)

Result: equus

Ex. #2 UPPER(LEFT('EQUUS',1)) + LOWER(RIGHT('EQUUS',LEN('EQUUS')-1))

Result: Equus

Ex. ROW_NUMBER() OVER (ORDER BY [Assignment: Relocation Effective Date])

Result: Each row in the output of the report has a numerical value starting with one.

Ex. LEFT([Auth Stage Assignment Service: Service Id], charindex(‘-‘,[Auth Stage Assignment Service: Service Id]) -1)

(where Service ID = Temporary Living – up to 30 days approved)

Result: Will return all characters to the left of the first ‘-‘ encountered. This example results would be ‘Temporary Living’

Listed below are examples of database functions which can be used in SQL fields.

Ex. DBO.EQ_STD_SERVICE_ORDER_VENDOR_AND_STATUS_LIST_BY_TYPE ([ASSIGNMENT: ID], ‘EQVSDB’, ‘Home Buying’) Effective Date])

Result: If the service is not authorized (regardless if there is a service order or not): “Service not authorized.”

If the service is authorized, but there is no service order: “Service not ordered.”

If the service is authorized and there is a service order: “Fragomen Global Immigration - Ordered”.

If the service is authorized and there are multiple service orders: “Equity Corporate Housing – Service Complete; Oakwood Corporate Housing - Ordered”

Ex. DBO.[EQ_STD_USER_NAME_GET](@SBUILDER_USER_ID)

(where the current user is Matthew Long)

Result: Returns the first and last name of the current user in the system – Matthew Long

Ex. DBO.EQ_STD_DEPENDENT_BASIC_INFO([Assignment: ID], GETDATE())

(where there are 2 dependents: Joshua Mendez born April 8 2011 on assignment and female Elizabeth Mendez born November 27, 2008 not on assignment with primary citizenship of United Kingdom, and the current date is March 28, 2013)

Result: Returns for every dependent on an assignment first & last name, “Gender:” gender, “Date of Birth:” birth date, “Age:” age, “Primary Citizenship:” primary citizenship, and “Status”: status.

Joshua Mendez, Date of Birth: Apr 8 2011, Age: 1, Status: On Assignment; Elizabeth Mendez, Gender: Female, Date of Birth: Nov 27 2008, Age: 4, Primary Citizenship: United Kingdom, Status: Not On Assignment

Ex. DBO.EQ_STD_DEPENDENT_BASIC_INFO_CODES([Assignment: ID], GETDATE())

(where there are 2 dependents: male (with no Client Code configured) Joshua Mendez born April 8 2011 on assignment (configured with no Client Code) and female (with a Client Code of F) Elizabeth Mendez born November 27, 2008 not on assignment (with Client Code configured of NOA) with Primary Citizenship of United Kingdom (GBR), and the current date is March 28, 2013)

Result: Returns for every dependent on an assignment first name, last name, “Date of Birth:” birth date, “Age:” age.

Joshua Mendez, Gender: EQGNML, Date of Birth: Apr 8 2011, Age: 1, Status: EQDPYA; Elizabeth Mendez, Gender: Female, Date of Birth: Nov 27 2008, Age: 4, Primary Citizenship: GBR, Status: NOA

Ex. DBO.EQ_STD_COUNTRY_CODE_FROM_NAME([Employee: Country of Birth])

(where the Country of Birth is Germany)

Result: Returns the 3 character ISO code for the country. DEU

Ex. DBO.EQ_STD_HOST_COUNTRY_ISO2_FROM_ASSIGNMENT_ID([Assignment: ID])

(where the To Country is South Africa)

Result: Returns the 2 character ISO code for the country, which is: ZA

Ex. DBO.EQ_STD_HOME_COUNTRY_ISO2_FROM_ASSIGNMENT_ID([Assignment: ID])

(where the From Country is Germany)

Result: Returns the 2 character ISO code for the country, which is: DE

Ex. DBO. EQ_STD_STATE_PROVINCE_CODE_FROM_NAME ([Temp Living Detail: Country], [Temp Living Detail: State/Province])

(where the Country is Canada and the Province is Ontario)

Result: Returns the ISO code for the State/Province, which is: CA-ON

Ex. DBO.EQ_STD_ISO2_COUNTRY_CODE_FROM_NAME ([Employee: Hire Country])

(where [Employee: Hire Country] for the employee is Canada)

Result: Returns the two character ISO code for the country, which is: CA

Ex. DBO. EQ_STD_ CLASSIFICATION_CLIENT_CODE_FROM_DESCRIPTION_UNIQUE_NAME ([Employee: Gender], ‘EMPLOYEE!GENDER’)

(where [Employee: Gender] for the employee is Male and the client code for Male = M)

Result: Returns the Client Code for Gender, as shown on the Lookup Maintenance screen, which is: M

Ex. DBO. EQ_STD_ CLASSIFICATION_CLIENT_CODE_FROM_DESCRIPTION_UNIQUE_NAME ([Employee: Gender], ‘EMPLOYEE!GENDER’)

(where [Employee: Gender] for the employee is Male and the code for Male = EQGNML)

Result: Returns the Code for Gender, as shown on the Lookup Maintenance screen, which is: EQGNML

Ex. DBO. EQ_STD_COMPANY_SEGMENT_CODE_FROM_ NAME ([Assignment: From Segment Level 1 Lookup], 1, 1)

(where [Assignment: From Segment Level 1 Lookup] for the assignment being reported on is Americas with code AM; and the company id = 1)

Result: Returns the Code for Segment 1, Company 1, Americas, as shown on the Company Segment Lookups screen, which is: AM

Ex. DBO.EQ_STD_CUSTOM_FIELD_CODE_FROM_UNIQUE_NAME ([Assignment (Custom Field): Large List], ‘ASSIGNMENT_CF!CF_TEXT032’, 1)

(where Assignment Custom Field Text032 has a Caption of Large List. The assignment being reported on has “Headquarters” as the value for the Large List field and Headquarters is defined with code HQ for company id = 1)

Result: Returns the Code for Custom Field Assignment Text032, Company 1as shown on the Company Custom Field definition screen, Lookup Values section, which is: HQ

Ex. DBO.[EQ_STD_COMPANY_SEGMENT_CODE_FROM_ASSIGNMENT] ( [Assignment: ID], 3, 'TO' )

Result: Returns the Code for To Segment 3 for a given assignment, which is: C88

Parameter notes:

Ex. #1 DBO.EQ_STD_ACCOUNT_TAXABILITY_GET ([Account: ID], NULL, [Assignment: To Country] )

(where the [Account: ID] is an integer value 684 for the Account '1000' and [Assignment: To Country] is 'United States').

Ex. #2 DBO.EQ_STD_ACCOUNT_TAXABILITY_GET ([Account: ID], [Assignment Derived Calculations: To Country Code (3 char)], NULL )

(where the [Account: ID] is an integer value 684 for the Account '1000' and [Assignment Derived Calculations: To Country Code (3 char)] is 'USA')

Result: Returns 'Taxable'

Ex. DBO.EQ_STD_WEBPART_ASSIGNMENT_COORDINATOR([Assignment: ID], ‘EQRJRELOCO’)

Result: Displays the Relocation Coordinators contact information from the resource table; including photo if it exists

Ex. DBO.EQ_STD_EXCHANGE_RATE_GET([Assignment: From Currency], [Assignment: To Currency], GETDATE(), NULL)

Result: Displays the exchange rate for the Assignment From Currency and Assignment To Currency, as of current date. If using Company level exchange rate, please replace NULL with [Company: Id]

Ex. DBO.EQ_STD_USER_TYPE_GET([Service Order: Last Update By])

Result: Displays the user type that last updated a Service Order. User types it may display, for example, include: Vendor, Internal, External Client and External Employee

Ex. DBO.EQ_STD_USER_FIRST_LAST_GET([Assignment: Create By])

Result: Displays the full name of the user that created the assignment instead of the User_ID

Ex. DBO.EQ_STD_USER_EMAIL_GET_BY_LOGIN([Assignment: Create By])

Result: Displays the email address of the user that created the assignment instead of the User_ID