Clarity Service Management

Expand all | Collapse all

Ca SDM 14.1 SQL QUERY

Jump to Best Answer
  • 1.  Ca SDM 14.1 SQL QUERY

    Posted 11-29-2018 02:43 PM

    Hi everybody,

     

      Could you help me and sharing a sql query that return.

     

    CO#, CO START DATE, CO END DATE, ACTIVITY LOG DESCRIPTION, ACTIVITY LOG DATE AND TIME, WHO DID THE ACTIVITY

     

    appreciate your support.

     

    Ca sdm14.1 and MS sql



  • 2.  Re: Ca SDM 14.1 SQL QUERY
    Best Answer

     
    Posted 11-30-2018 12:49 AM

    Try this.  The date '01-01-1970 08:00' corrects for my time zone which is UTF+8 - please adjust for wherever you are.  And of course this doesn't adjust for Daylight Saving if it applies in your location (my state decided some years ago that Daylight Saving was too disruptive for milking cows, so we don't do it!).

    use mdb;
    go

    select CO.chg_ref_num
          ,dateadd(s,CO.start_date, '01-01-1970 08:00') as chg_start_date
          ,dateadd(s,CO.close_date, '01-01-1970 08:00') as chg_end_date
          ,CA.description
          ,dateadd(s,CA.time_stamp, '01-01-1970 08:00') as alg_timestamp
          ,CC.last_name, CC.first_name
      from chg CO left join chgalg CA on CO.id = CA.change_id
                  left join ca_contact CC on CA.analyst = CC.contact_uuid
    ;
    go

    Regards,

    James



  • 3.  Re: Ca SDM 14.1 SQL QUERY

    Posted 12-03-2018 12:49 AM

    Thank JAMES,

       It works perfectly,

    I adjusted time zone to be +3, and ignore daylight saving issue since it is no applied in my country.