2 Replies Latest reply: Feb 6, 2013 4:15 AM by 976169 RSS

    Length of service.

    976169
      Good afternoon,

      I'm working on the following and don't seen to be able to figure it out. I'm trying to cast the folowing as a char so that I can add 'Yr(s)' the the end

      TIMESTAMPDIFF(SQL_TSI_Month, cast(IFNULL("Employee Attributes"."Employee Adjusted Service Date", "Employee Attributes"."Latest Start Date") as timestamp),Current_Date)/12

      I have tried the following but this doesn't seem to work

      concat(concat(CAST(TIMESTAMPDIFF(SQL_TSI_Month, IFNULL("Employee Attributes"."Employee Adjusted Service Date", "Employee Attributes"."Latest Start Date"),Current_Date)/12) as char),'Yr(s)')
      I will then add

      concat(cast(MOD(TIMESTAMPDIFF(SQL_TSI_Month, IFNULL("Employee Attributes"."Employee Adjusted Service Date" , "Employee Attributes"."Latest Start Date"),Current_Date),12) as char),'Mth(s)')

      So that am then able to pull back the number of Yr(s) and Mth(s) that a person has worked any help would be greatly appreciated.

      Many thanks

      Owen

      Edited by: 973166 on 06-Feb-2013 02:15
        • 1. Re: Length of service.
          Srini VEERAVALLI
          Try this
          cast(cast(TIMESTAMPDIFF(SQL_TSI_Month, cast(IFNULL("Employee Attributes"."Employee Adjusted Service Date", "Employee Attributes"."Latest Start Date") as timestamp), current_date)/12 as int) as char)||'Yrs' || cast(MOD(TIMESTAMPDIFF(SQL_TSI_Month, cast(IFNULL("Employee Attributes"."Employee Adjusted Service Date", "Employee Attributes"."Latest Start Date") as timestamp), current_date), 12) as char)||'Mths'

          In detail:
          cast(MOD(TIMESTAMPDIFF(SQL_TSI_Month, Employee Adjusted Service Date, 12) as char)||'Mths'
          Returns months part

          cast(cast(TIMESTAMPDIFF(SQL_TSI_Month, Employee Adjusted Service Date, current_date)/12 as int) as char)||'Yrs'
          Returns Year part

          Take care of ifnull part in your report with proper datatype

          If helps mark as correct

          Edited by: Srini VEERAVALLI on Feb 4, 2013 11:28 AM
          • 2. Re: Length of service.
            976169
            it works but when the casting the first portion as an int means that if i take the dates 03/2012 and 02/2013 away from each other it brings back 1 Yr and 11 months. I originallly used the following:

            concat(concat(CAST (TIMESTAMPDIFF(SQL_TSI_YEAR,IFNULL("Employee Attributes"."Employee Adjusted Service Date" , "Employee Attributes"."Latest Start Date") ,Current_Date) as CHAR),' Yr(s) '), concat(cast(MOD(TIMESTAMPDIFF(SQL_TSI_Month, IFNULL("Employee Attributes"."Employee Adjusted Service Date" , "Employee Attributes"."Latest Start Date"),Current_Date),12) as char),'Mth(s)'))

            This had the same effect which is why I started to use months when I divided it by 12 it brought back the right value.

            Edited by: 973166 on 06-Feb-2013 02:15