Reporting Tables

This topic describes the Syntelate XA database tables used for:

Call Reporting
Email Reporting
Webchat Reporting

As well as tables, this topic also describes Useful Functions and Reporting Tables that you may also find helpful for reporting.

Note: In this topic, any words that are formatted in UPPERCASE refer to field names or table names. Items within single quotes refer to actual row values from one of the tables that the item depends on.

Call Reporting

All types of call use two reporting tables:

REPORTING_AGENT_BY_CHANNEL
REPORTING_CUSTOMER_BY_CHANNEL

Both tables depend on and aggregate data from the CALLSTATUSLOGGING and INTERACTION_1 tables.

A call, of any type, is identified by the CLIENTNO and you can use the CLIENTNO to examine customer call journeys. To examine agent call journeys, use the CALLLOGID.

Note: The CLIENTNO and the LKTL_CLIENTNO from the CALLSTATUSLOGGING and INTERACTION_1 tables respectively are synonymous with each other. For tables with names like INTERACTION_X the ‘X’ represents an integer, usually 1 but not always; for example, INTERACTION_1 or INTERACTION_1_HISTORY.

CALLSTATUSLOGGING

When reporting is enabled, Syntelate XA records every change of call state in the CALLSTATUSLOGGING table.

Note: In reporting, we use the term call state when talking about chats and emails as well as voice calls.

The CALLSTATUSLOGGING table includes one record for each change of call state. It specifies all the captured information about the change of call state, such as the time that it happened and the interaction it relates to.

The following is a description of the columns of the CALLSTATUSLOGGING table.

Field	Description
CALLSTATID	A unique ID for the record.
CLIENTNO	References the interaction in the INTERACTION_X table. For the following call states, Syntelate XA records -1: LoginWorkzone LogoutWorkzone Ready PendingNotReady NotReady For other call states that are not associated with a particular record in the INTERACTION_X table, such as Ringing or Waiting, Syntelate XA records 0.
CALLLOGID	An ID shared by all call states for the same interaction and the same agent. A single call will therefore generate several records (for Dialling, Talking, and WrapUp, say), all with the same CALLLOGID but each with a unique CALLSTATID. A call involving agent consult, conference, or transfer will generate records with a CALLOGID for agent A and other records with a CALLLOGID for agent B. For chat records, this is always NULL (CALLLOGREF is used instead).
CPGNO	References the universe in the CAMPAIGNS table. For the following call states, Syntelate XA records 0: Ready PendingNotReady NotReady For the following call states, Syntelate XA records null: LoginWorkzone LogoutWorkzone
CALLSTATE	The state into which the call was moved.
LOCALISEDCALLSTATE	The state into which the call was moved (in the agent’s language).
STATETIMEAGT	The date and time that the call entered the state (in the agent’s time zone). For chat records, this is always NULL.
STATETIMEUTC	The date and time that the agent entered the state (in UTC Coordinated Universal Time).
USEDBYAGENT	The agent’s username (references the AGENT table).
LOGAGENTNO	Not used by Syntelate XA.
CALLLOGREF	For voice calls, the unique reference number from the phone system. For chat records, the chat ID (shared by all call states that relate to the same chat). Otherwise NULL.
COMPLETECODE	The completion code (when the call state is Completed). This references the CODE field in the COMPLETION_CODES table.
CTI_USER	The CTI agent name.
STATEENDTIMEUTC	The date and time that the call left this state (in UTC).
TRANSFERTO	The number transferred to.
REASONCODE	For a call state of NotReady or PendingNotReady, the not ready reason code. For the initial NotReady state when an agent logs in to a workzone, this field is blank. References the CTINOTREADYREASONS table.

REPORTING_AGENT_BY_CHANNEL

This table aggregates agent data for all calls.

Note: The field JOBNAME is only relevant if you use Avaya POM.

Field	Format	Description
REPORTING_ID	INTEGER	Interval PK. Not required for reports.
UNIVERSE_ID	INTEGER	LKTL_CPG_NO.
TENNANT_ID	INTEGER	Not used.
CHANNEL	VARCHAR(20)	The type of call. From LKTL_WLMTYPE on INTERACTION_X.
CHANNELTYPE	VARCHAR(100)	Not used.
LKTL_CLIENTNO	INT	The identifier for the database. Unique to each interaction except callbacks, which are placed in the queue with the same identifier. From LKTL_CLIENTNO (PK) in INTERACTION_X table and effective FK on CALLSTATUSLOGGING and other INTERACTION tables.
CALLLOGID	INT	Represents a single agent call. CALLLOGID in CALLSTATUSLOGGING table.
HISTORY_ID	INT	Not used.
WORKZONE_ID	INT	The WORKZONE_ID from the CALLSTATUSLOGGING table.
CREATED	DATETIME	LKTL_CREATED from INTERACTION_X table for this LKTL_CLIENTNO.
CREATED_UTC	DATETIME	Adjusted from LKTL_CREATED on INTERACTION_X.
DAYDATE	DATE	Local date derived from LKTL_CREATED.
AGENTNAME	VARCHAR(100)	USEDBYAGENT on the CALLSTATUSLOGGING table. PK on the AGENT table where the actual name of the agent is stored.
AGENTSTART	DATETIME	Not used.
AGENTEND	DATETIME	Not used.
COMPLETE	INT	1 for complete, otherwise 0. From the latest LKTL_COMPLETE value on INTERACTION_X table.
AGENT_COMPLETECODE	VARCHAR(10)	The completion code that the adviser has selected.
TRANSFERRED	INT	A call state of ‘TRANSFERRED’ exists for this CALLLOGID.
TIME_CONFERENCE	INT	Total time in this CALLSTATE in milliseconds. Note that some of these call states may either not be valid for particular call types or just may not be used.
TIME_CONSULT	INT
TIME_CONSULT_ACTIVE	INT
TIME_CONSULT_PASSIVE	INT
TIME_CONSULT_RECEIVER	INT
TIME_DIAL	INT
TIME_DIALLING	INT
TIME_HOLD	INT
TIME_PREVIEW	INT
TIME_RINGING	INT	Total time in this CALLSTATE in milliseconds. Note that some of these call states may either not be valid for particular call types or just may not be used.
TIME_TALKING	INT
TIME_WAITING	INT
TIME_WRAPUP	INT
TIME_TOTALHANDLE	INT	The sum of these columns: TIME_CONFERENCE TIME_HOLD TIME_PREVIEW TIME_DIALLING TIME_RINGING TIME_TALKING TIME_CONSULT TIME_WRAPUP
TIME_AGGREGATESTATES	INT	The sum of these columns: TIME_TOTALHANDLE (see above) TIME_DIAL TIME_WAITING
TIME_AGENTSTARTTOEND	INT	The time difference from the first instance to the last instance of a particular CLIENTNO, CALLLOGID and AGENTNAME.
DateTimeModified	DATETIME	The date and time the row was updated or inserted.
JOBNAME	VARCHAR(30)	LKTL_CTI from INTERACTION_X table.

REPORTING_CUSTOMER_BY_CHANNEL

This table aggregates customer data for all calls.

Note: The field JOBNAME is only relevant if you use Avaya POM.

FIELD	FORMAT	DESCRIPTION
REPORTING_ID	INT	Interval PK. Not required for reports.
UNIVERSE_ID	INT	LKTL_CPG_NO.
CHANNEL	VARCHAR(20)	The type of call. From LKTL_WLMTYPE on INTERACTION_X.
LKTL_CLIENTNO	INT	The identifier for the database. Unique to each interaction except callbacks, which are placed in the queue with the same identifier. From LKTL_CLIENTNO (PK) in INTERACTION_X table and effective FK on CALLSTATUSLOGGING and other INTERACTION tables.
WORKZONE_ID	INT	The WORKZONE_ID from the CALLSTATUSLOGGING table.
CREATED	DATETIME	LKTL_CREATED from INTERACTION_X table for this LKTL_CLIENTNO.
DAYDATE	DATE	Local date derived from LKTL_CREATED.
CUSTOMER_STARTTIME_AGT		The earliest local start time of any of the following CALLSTATES: 'Talking', 'Transferred', 'Consult', 'Conference' or 'Hold'.
CUSTOMER_ENDTIME_AGT		The latest local end time of any of the following CALLSTATES: 'Talking', 'Transferred', 'Consult', 'Conference' or 'Hold'.
FIRST_AGENTNAME		The agent whom the call started with.
LAST_AGENTNAME		The agent whom the call ended with.
COMPLETE		'1' if the LKTL_COMPLETED on INTERACTION_X = ‘Y’, otherwise '0'.
COMPLETE_CODE		The completion code that was selected by the last agent who handled the record.
NULL_ENDUTC	INT	'1' if an end time exists for a call state, otherwise '0'.
TRANSFERRED		The number of times the call was transferred. Otherwise NULL.
TRANSFERRED_LAST_PHONENO		The last telephone number that a call was transferred to.
TIME_CONFERERENCE	INT	Total time in this CALLSTATE in milliseconds. Note that some of these call states may either not be valid for particular call types or just may not be used.
TIME_CONSULT	INT
TIME_HOLD	INT
TIME_TALKING	INT
TIME_TRANSFER	INT
TIME_TOTALCALLSTATES	INT	The sum of these columns: TIME_CONFERENCE TIME_CONSULT TIME_HOLD TIME_TALKING TIME_TRANSFER
TIME_TOTAL_CUSTOMER	INT	As above for TIME_TOTALCALLSTATES.
DATETIME_MODIFIED	DATETIME	The date and time the row was updated or inserted.
JOBNAME	VARCHAR(30)	LKTL_CTI from INTERACTION_X table.

Email Reporting

Email reporting uses two tables:

REPORTING_EMAIL_AGENT_DETAIL
REPORTING_EMAIL_CUSTOMER_DETAIL

REPORTING_EMAIL_AGENT_DETAIL

This table provides the data that's displayed on the report called Email - Agent Experience.

Field	Format	Description
UNIQUEMESSAGE_ID	UNIQUEIDENTIFIER	UNIQUEMESSAGE_ID in INTERACTION_X_MESSAGE.
MAILBOX	INT	IMMAILBOXNO (PK) in IMMAILBOXNO.
CLIENTNO	INT	LKTL_CLIENTNO (PK) in INTERACTION_X table. Also used as effective FK by CALLSTATUSLOGGING and other INTERACTION tables.
CALLLOGID	INT	Relates to CALLLOGID in CALLSTATUSLOGGING table and INTERACTION_X_HISTORY table.
CALLSTATID	INT	PK on CALLSTATUSLOGGING table.
EMAIL_DIRECTION_REPORT	VARCHAR(1)	‘I’ for ‘Inbound’, ‘O’ for Outbound.
USEDBYAGENT	VARCHAR(100)	The agent that dealt with the email. Joins to the AGENTNAME on the AGENT table.
NAMEOFAGENT	VARCHAR(100)	The name of the agent in the AGENT table. Included to remove need for a join on AGENT table.
DAYDATE	DATETIME	The date that the email was presented to the agent.
UTC_OFFSET	INT	The offset in hours between STATETIMEAGT and STATETIMEUTC in CALLSTATUSLOGGING.
STATETIMEAGT	DATETIME	Local date and time the email was presented.
STATEENDTIMEAGT	DATETIME	Local date and time the email ended.
STATEENDTIME_IN_DAY	INT	'1' if the STATEENDTIMEAGT and STATETIMEAGT are on the same day.
EMAIL_DURATION_MS	INT	Time between the STATEENDTIMEAGT and STATETIMEAGT in milliseconds. Check is in place for overflow.
COMPLETED_CALLSTATID	INT	The CALLSTATID on the CALLSTATUSLOGGING table where a ‘Completed’ CALLSTATE was found for this CLIENTNO and CALLLOGID.
COMPLETED_CODE	VARCHAR(50)	Either the COMPLETE_CODE from the email or a calculated value for exceptions.
EMAIL_COMPLETE	INT	'1' if COMPLETED on the CALLSTATUSLOGGING = ‘Y’. Forwarded emails can be counted as completed if the parameter @CountForwardAsComplete = '1' in the stored procedure SP_Populate_REPORTING_EMAIL_CUSTOMER_DETAIL.
EMAIL_SENT	INT	'1' if ‘EMSENT’ complete code on the CALLSTATUSLOGGING table. Does not differentiate between unsolicited, reply or forwarded.
EMAIL_ANSWERED	INT	'1' if the email has been answered. An email is considered answered if it has a reply email, or if a completion code of Spam or No Reply Required is found.
EMAIL_TRANSFERRED	INT	'1' if the email indicates it has been transferred.
EMAIL_PARKED	INT	'1' if the email has been parked for a predetermined amount of time.
EMAIL_SPAM	INT	'1' if the email is marked as spam.
EMAIL_NOREPLYREQUIRED	INT	'1' if the email is marked as no reply required.
EMAIL_CANCELLED	INT	'1' if the email has been cancelled. This is an unsolicited email which has been closed with no change or parked.
EMAIL_ESCALATED	INT	'1' if the email has been escalated.
EMAIL_CLOSEDNOCHANGE	INT	'1' if an inbound email has been returned to the queue using the ‘Close No Change’ button.
EMAIL_INPROGRESS	INT	'1' if an email has no end time for that CALLLOGID.
EMAIL_SYSTEMENDEMAIL	INT	'1' if it has no ‘Completed’ call state for that CALLLOGID but has an end time populated by a subsequent CALLSTATE.
EMAIL_UNSOLICITED	INT	'1' if the agent has raised an outbound email outwith an inbound email.
REPLY_EMAILFOUND	INT	'1' if original email address is found in a sent email.
FORWARD_EMAILFOUND	INT	'1' if any email addresses other than the original one are found in a sent email.
NEWMESSAGE_EMAILFOUND	INT	'1' if an email with this CLIENTNO exists on the INTERACTION_X_NEWMESSAGE table. It should only appear if the report updating overlaps a sent email before the server sends it. The row is re-evaluated on the next report run if this is '1'.
AUDIT_NOTE	VARCHAR(50)	Usually NULL. Otherwise, a small system-populated note with comments around to help track email outcomes.
MODIFIED_DATETIME	DATETIME	The date and time the record was updated or inserted.

REPORTING_EMAIL_CUSTOMER_DETAIL

This table provides the data that's displayed on the report called Email - Customer Experience .

Field	Format	Description
CUSTOMER_REPORT_ID	UNIQUEIDENTIFIER	PK. Not required for reports.
HISTORY_NO	INT	LKTL_HISTORYNO (PK) in INTERACTION_X_HISTORY table.
MESSAGE_NO	INT	LKTL_MESSAGENO (PK) in INTERACTION_X_MESSAGE table.
UNIQUEMESSAGE_ID	UNIQUEIDENTIFIER	UNIQUEMESSAGE_ID in INTERACTION_X_MESSAGE table.
MAILBOX_NO	INT	IMMAILBOXNO (PK) in IMMAILBOXNO.
CREATED_DATETIME	DATETIME	The date and time the email arrived in the INTERACTION_X_MESSAGE table.
CREATED_DATE	DATETIME	The date the email arrived in the INTERACTION_X_MESSAGE table.
CLIENTNO	INT	LKTL_CLIENTNO (PK) in INTERACTION_X table. Also used as effective FK by CALLSTATUSLOGGING and other INTERACTION tables. Can appear on multiple rows where the client has subsequently replied to a previously completed email.
CALLLOGID	INT	Relates to CALLLOGID in CALLSTATUSLOGGING table and INTERACTION_X_HISTORY table.
CUSTOMERINBOUNDEMAILS_COUNT	INT	The total emails on the same subject a customer has sent in up to and including the one presented to the agent.
COMPLETECODE	VARCHAR(10)	The current code that the agent/supervisor has used when dealing with the email.
COMPLETED	INT	'1' if the email has a completed status, otherwise '0'.
EMAIL_TYPE	VARCHAR(1)	‘I’ for inbound or ‘O’ for outbound.
EMAIL_OUTBOUNDTYPE	VARCHAR(1)	Should always be ‘N’ if EMAIL_TYPE is 'O', otherwise 'NULL'.
UNSOLICITED	INT	'1' if EMAIL_OUTBOUNDTYPE is 'N', otherwise '0'.
REPLY	INT	CUSTOMERINBOUNDEMAILS_COUNT if one or more replies have been found for this CLIENTNO and CALLLOGID.
FORWARD	INT	CUSTOMERINBOUNDEMAILS_COUNT if one or more emails marked as FORWARD have been found for this CLIENTNO and CALLLOGID.
ANSWERED	INT	CUSTOMERINBOUNDEMAILS_COUNT if one or more REPLY, SPAM, or NO REPLY REQUIRED emails have been found for this CLIENTNO and CALLLOGID.
TRANSFERRED	INT	CUSTOMERINBOUNDEMAILS_COUNT if one or more emails marked as TRANSFERRED have been found for this CLIENTNO and CALLLOGID.
ESCALATED	INT	CUSTOMERINBOUNDEMAILS_COUNT if one or more emails marked as ESCALATED have been found for this CLIENTNO and CALLLOGID.
PAUSED	INT	CUSTOMERINBOUNDEMAILS_COUNT if the email is currently parked.
SPAM	INT	CUSTOMERINBOUNDEMAILS_COUNT if one or more emails marked as SPAM have been found for this CLIENTNO and CALLLOGID.
NOREPLYREQUIRED	INT	CUSTOMERINBOUNDEMAILS_COUNT if one or more No Reply Required have been found for this CLIENTNO and CALLLOGID.
FORWARD_EMAIL_FOUND	INT	CUSTOMERINBOUNDEMAILS_COUNT if any email addresses other than the original one are found in a sent email.
REPLY_EMAIL_FOUND	INT	CUSTOMERINBOUNDEMAILS_COUNT if original email address is found in a sent email.
LOCKEDBY	VARCHAR(100)	If this is not 'NULL' then record is currently locked by this agent. In normal circumstances this means the email is in progress.
COMPLETED_DATETIME	DATETIME	The first instance of a complete flag being set for this CLIENTNO and CALLLOGID.
WORKING_SLA_TIME_SECS	INT	The seconds in working hours allowed for an answered email. Calculated from the value set in IMMAILBOX for this MAILBOX_NO.
WORKING_RESPONSE_TIME_SECS	INT	The working seconds until an answered email.
IN_WORKING_SLA_TIME	INT	CUSTOMERINBOUNDEMAILS_COUNT if WORKING_RESPONSE_TIME_SECS <= WORKING_SLA_TIME_SECS.
TOTAL_SLA_TIME_SECS	INT	The seconds overall allowed for an answered email. Calculated from the value set in IMMAILBOX for this MAILBOX_NO.
TOTAL_RESPONSE_TIME_SECS	INT	The overall time until the email is answered.
IN_TOTAL_SLA_TIME	INT	CUSTOMERINBOUNDEMAILS_COUNT if TOTAL_RESPONSE_TIME_SECS <= TOTAL_SLA_TIME_SECS.
SUPERVISOR_DEALT_WITH	INT	'1' if a supervisor has opened the email prior to it being sent to an agent and assigned a COMPLETE_CODE. Otherwise '0'.
DATETIME_MODIFIED	DATETIME	The date and time the record was updated or inserted.

Webchat Reporting

Webchat reporting uses three tables:

WEBCHATAGENTREPORTING
REPORTINGAGENTOVERVIEW
WEBCHATCUSTOMERREPORTING

Each table depends on various other tables, which are explained in the descriptions below.

As with calls, a chat, of any type, is identified by the CLIENTNO and you can use the CLIENTNO to examine customer chat journeys. To examine agent chat journeys, use the CALLLOGID.

WEBCHATAGENTREPORTING

Field	Format	Description
REPORTING_ID	INT	Interval PK. Not required for reports.
LKTL_CLIENTNO	INT	The identifier for the database. Unique to each interaction except Call Backs which are placed in the queue with the same identifier. From LKTL_CLIENTNO (PK) in INTERACTION_X table and effective FK on CALLSTATUSLOGGING and other INTERACTION tables.
HISTORY_ID	INT	PK from INTERACTION_X_HISTORY table.
CREATED	DATETIME	LKTL_CREATED from INTERACTION_X _HISTORY table for this LKTL_CLIENTNO.
AGENTNAME	VARCHAR (30)	USEDBYAGENT on the CALLSTATUSLOGGING table. PK on the AGENT table where the actual name of the agent is stored.
AGENTSTART	DATETIME	The time the agent is first allocated the chat.
AGENTEND	DATETIME	The time the agent ends or transfers the chat
TRANSFERRED	INT	'1' if a ‘Chat Transferred’ CALLSTATE exists for this agent and CLIENTNO, otherwise '0'.
TRANSFERREDOUT	INT	'1' if the ENDTOPIC and STARTTOPIC are not equal, Otherwise '0'. (See below for these two fields.)
COMPLETE	INT	'1' if the LKTL_COMPLETED on the INTERACTION_X_HISTORY table = ‘Y’, otherwise '0'.
AGENT_COMPLETECODE	VARCHAR (100)	The LKTL_COMPLETECODE selected by the initial agent who handled the chat.
FINAL_COMPLETECODE	VARCHAR (100)	The LKTL_COMPLETECODE selected by the final agent who handed the chat.
STARTTOPIC	INT	The IMMAILBOXNO that the Chat started in.
ENDTOPIC	INT	The IMMAILBOXNO that the Chat ended in.
RESPONSETIME	INT	The average time (seconds) to for the agent to respond to this message.
TOTALCHATTIME	INT	Time (seconds) between AGENTSTART and AGENTEND.
WRAPTIME	INT	'0' if the agent ends the chat before the client. Otherwise, the time (seconds) between the client ending the chat and the agent ending the chat.
WRAPPED	INT	'1' if WRAPTIME > 0, otherwise '0'.

REPORTINGAGENTOVERVIEW

The REPORTINGAGENTOVERVIEW table aggregates key agent data by day.

It depends on the tables:

CALLSTATUSLOGGING
CTINOTREADYREASONS

Field	Format	Description
REPORTING_ID	INT	Interval PK. Not required for reports.
AGENTNAME	VARCHAR (30)	USEDBYAGENT on the CALLSTATUSLOGGING table. PK on the AGENT table where the actual name of the agent is stored.
WORKZONE	INT	WORKZONE_ID from the CALLSTATUSLOGGING table.
STATUSDATE	DATETIME	The date that the following four fields are aggregated over.
LOGGEDIN_SECS	INT	The sum of time differences between CALLSTATES of ‘Login WorkZone’ and ‘LogoutWorkZone’.
NOTREADY_SECS	INT	The sum of all ‘NotReady’ time segments.
NOTREADY_BREAKS_SECS	INT	The sum of all ‘NotReady’ time segments. Where the BREAK_FLG is 1 on the CTINOTREADYREASONS table.
CHATTIME	INT	The sum of all ‘Chat’ time segments.

WEBCHATCUSTOMERREPORTING

The WEBCHATCUSTOMERREPORTING table aggregates customer data for chats.

It depends on the tables:

CALLSTATUSLOGGING
INTERACTION_X
INTERACTION_X_MESSAGE

Field	Format	Description
REPORTING_ID	INT	Interval PK. Not required for reports.
LKTL_CLIENTNO	INT	The identifier for the database. Unique to each interaction except Callbacks which are placed in the queue with the same identifier. From LKTL_CLIENTNO (PK) in INTERACTION_X table and effective FK on CALLSTATUSLOGGING and other INTERACTION tables.
CREATED	DATETIME	The date and time the row was added to the database.
ABANDONED	INT	'1' if the chat was abandoned, otherwise '0'.
ALLOCATED	INT	LKTL_CREATED from INTERACTION_X _HISTORY table for this LKTL_CLIENTNO.
TRANSFERS	INT
AGENTSTART	DATETIME	The time of the first instance of CALLSTATE ‘Agent Allocated Chat’ for this CLIENTNO.
AGENTEND	DATETIME	The time of the last instance of CALLSTATE ‘Agent Ended Chat’ for this CLIENTNO.
LKTL_COMPLETECODE	VARCHAR (100)
STARTTOPIC	INT	The IMMAILBOXNO that the Chat started in.
ENDTOPIC	INT	The IMMAILBOXNO that the Chat ended in.
WAITTIME_INITIAL	INT	The time (seconds) from the customer starting a chat until it is assigned to an agent.
WAITTIME_TRANSFER	INT	The total time (seconds) that a customer waited for a chat to be transferred to another agent.
RESPONSETIME	INT	The average time (seconds) for agent(s) to reply to a message.
TOTALCHATTIME	INT	Time (seconds) between AGENTSTART and AGENTEND.
COMPLETED	INT	'1' if an LKTL_COMPLETED = ‘Y’ on INTERACTION_X for this Client. Otherwise '0'.
WAITTIME_ABANDONED	INT	Time (seconds) between a customer starting a chat and ending it before it can be allocated to an agent.
TRANSFERRED	INT	'1' if a ‘Chat Transferred’ CALLSTATE exists for this agent and CLIENTNO, otherwise '0'.
TRANSFERREDOUT	INT	'1' if the transfer was to a different IMMMAILBOXNO, otherwise '0'.

Useful Functions

The following two functions are used frequently to take a lot of the complexity out of presenting formatted dates, groups of dates or times.

FN_SecondsToHHMMSS

This is a scalar function that takes a float value in seconds, then rounds it to the nearest second and converts it into a string in the form of hours, minutes and seconds.

dbo.FN_SecondsToHHMMSS(@Seconds AS FLOAT) Returns VARCHAR(13)

Maximum value is ‘999999:59:59’ from a @Second value of 35999999999.0

Note: Although the date is recorded as a string in SQL, it is recognised by Excel as a valid time.

EXAMPLE

DECLARE @Sec FLOAT = 10000.265 SELECT TimeValue = dbo.FN_SecondsToHHMMSS(@Sec)

Returns 2:46:40.

fnt_GET_TIME_INTERVALS

fnt_GET_TIME_INTERVALS is a table function. It returns a table that allows grouping of dates over time intervals of 30 minutes, hour, day, week or month, between two dates inclusively.

Note: For the current version, week intervals are hardcoded to start on a Sunday.

This is the function:

dbo.fnt_GET_TIME_INTERVALS ( @StartDate DATETIME , @EndDate DATETIME , @Interval INT , @WorkingHours BIT = 0 )

@StartDate and @EndDate are self-evident and generally input as dates but you can pick an actual date and time if @Interval is 1 or 2.
@Interval is a value from 1 to 5 representing 30 minutes, 1 hour, 1 day, 1 week and 1 month respectively.
@WorkingHours is either 0 for all times in a day or 1 for working hours which are taken from table Config_WorkingHours.

This function generally uses a join directly to determine the interval a record belongs to or you can read the function into a temp table before the join.

This function returns a table value of:

( Interval_Id INT IDENTITY(1,1) NOT NULL Interval INT NOT NULL IntervalStart DATETIME NOT NULL IntervalEnd DATETIME NOT NULL IntervalLabel VARCHAR(100) NOT NULL AvgRequired INT NOT NULL )

EXAMPLE

To return a table between the start date and end date with the interval being one week:

DECLARE @Start DATETIME = '20191112' @End DATETIME = '20191127' @Interval INT = 4 @wrkHrs BIT = 0 SELECT * INTO #DateRange FROM dbo.fnt_GET_TIME_INTERVALS (@Start,@End,@Interval,@WrkHrs)

Interval_Id	Interval	IntervalStart	IntervalEnd	IntervalLabel	AvgRequired
1	4	2019-11-12 00:00:00.000	2019-11-16 00:00:00.000	Tue 12-Nov–Sat 16-Nov-19	5
2	4	2019-11-17 00:00:00.000	2019-11-23 00:00:00.000	Sun 17-Nov–Sat 23-Nov-19	7
3	4	2019-11-24 00:00:00.000	2019-11-27 00:00:00.000	Sun 24-Nov–Wed 27-Nov-19	4

From this table the IntervalStart and IntervalEnd can be used to determine which group a particular record should be in. Note that the interval labels in this instance only show a full week for the second row. The AverageRequired illustrates that dates can return parts of weeks (or months).

Feedback