Author Topic: Infomart script start and end time  (Read 1280 times)

Offline brendanb

  • Newbie
  • *
  • Posts: 32
  • Karma: 0
Infomart script start and end time
« on: January 24, 2020, 08:16:10 PM »
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

Offline brendanb

  • Newbie
  • *
  • Posts: 32
  • Karma: 0
Re: Infomart script start and end time
« Reply #1 on: January 24, 2020, 08:59:59 PM »
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)


Offline ynlyst

  • Newbie
  • *
  • Posts: 9
  • Karma: 0
Re: Infomart script start and end time
« Reply #2 on: February 05, 2020, 06:33:46 PM »
  select DATEADD(ss, start_ts, '19700101') AS Start_Ts