Genesys CTI User Forum

Genesys CTI User Forum => Genesys CTI Technical Discussion => Topic started by: brendanb on January 24, 2020, 11:16:10 AM

Title: Infomart script start and end time
Post by: brendanb on January 24, 2020, 11:16:10 AM

Hi

I have created the following infomart script. The script is working as intended except that I would like to add start and end time to the result. I can not find a table that keeps the time and date in a readable format. Do I need to convert the time and date or is there a table that holds the information

SELECT        RESOURCE_.RESOURCE_NAME, RESOURCE_.RESOURCE_SUBTYPE, RESOURCE_.AGENT_FIRST_NAME, RESOURCE_.AGENT_LAST_NAME, INTERACTION_FACT.SOURCE_ADDRESS,
                        INTERACTION_FACT.INTERACTION_ID, TECHNICAL_DESCRIPTOR.TECHNICAL_RESULT, DATE_TIME.LABEL_MI
FROM            MEDIATION_SEGMENT_FACT INNER JOIN
                        DATE_TIME ON MEDIATION_SEGMENT_FACT.START_DATE_TIME_KEY = DATE_TIME.DATE_TIME_KEY INNER JOIN
                        INTERACTION_FACT ON MEDIATION_SEGMENT_FACT.INTERACTION_ID = INTERACTION_FACT.INTERACTION_ID INNER JOIN
                        RESOURCE_ ON MEDIATION_SEGMENT_FACT.RESOURCE_KEY = RESOURCE_.RESOURCE_KEY INNER JOIN
                        TECHNICAL_DESCRIPTOR ON MEDIATION_SEGMENT_FACT.TECHNICAL_DESCRIPTOR_KEY = TECHNICAL_DESCRIPTOR.TECHNICAL_DESCRIPTOR_KEY
WHERE        (INTERACTION_FACT.SOURCE_ADDRESS = 'some email address') AND (INTERACTION_FACT.INTERACTION_ID = 12345678)

Regards

Title: Re: Infomart script start and end time
Post by: brendanb on January 24, 2020, 11:59:59 AM

Hi

I actually found a solution to this. I just added the following line

DATE_TIME.CAL_DATE + CAST(MEDIATION_SEGMENT_FACT.START_TS - DATE_TIME.DATE_TIME_KEY AS float) / CAST(86400 AS float)

The query now looks like this.

SELECT        RESOURCE_.RESOURCE_NAME, RESOURCE_.RESOURCE_SUBTYPE, RESOURCE_.AGENT_FIRST_NAME, RESOURCE_.AGENT_LAST_NAME, INTERACTION_FACT.SOURCE_ADDRESS,
                        INTERACTION_FACT.INTERACTION_ID, TECHNICAL_DESCRIPTOR.TECHNICAL_RESULT, DATE_TIME.CAL_DATE + CAST(MEDIATION_SEGMENT_FACT.START_TS - DATE_TIME.DATE_TIME_KEY AS float)
                        / CAST(86400 AS float) AS Start_Date
FROM            MEDIATION_SEGMENT_FACT INNER JOIN
                        DATE_TIME ON MEDIATION_SEGMENT_FACT.START_DATE_TIME_KEY = DATE_TIME.DATE_TIME_KEY INNER JOIN
                        INTERACTION_FACT ON MEDIATION_SEGMENT_FACT.INTERACTION_ID = INTERACTION_FACT.INTERACTION_ID INNER JOIN
                        RESOURCE_ ON MEDIATION_SEGMENT_FACT.RESOURCE_KEY = RESOURCE_.RESOURCE_KEY INNER JOIN
                        TECHNICAL_DESCRIPTOR ON MEDIATION_SEGMENT_FACT.TECHNICAL_DESCRIPTOR_KEY = TECHNICAL_DESCRIPTOR.TECHNICAL_DESCRIPTOR_KEY
WHERE        (INTERACTION_FACT.SOURCE_ADDRESS = 'some email address') AND (INTERACTION_FACT.INTERACTION_ID = 1213456)

Title: Re: Infomart script start and end time
Post by: ynlyst on February 05, 2020, 09:33:46 AM

  select DATEADD(ss, start_ts, '19700101') AS Start_Ts