Calculate length of service of an employee in Years, Months and Days using T-SQL when there is a gap-CodePudding

Is there any way to calculate length of service of an Employee when there is a gap in his tenure. I have a period1 from '08/09/2003' to '06/25/2009', period 2 is from '06/10/2015' to '03/31/2022' or GETDATE(). can someone explain me how can I calculate the whole period(Length of service) when there is a gap like this for that Employee ?

All dates above are in 'MM/DD/YYYY' format.

So, the Period1 service is - 5 Years, 10 Months, 16 Days
Period2 service is - 6 Years, 9 Months, 21 Days
The requirements is
total service should be - 12 Years, 8 Months, 7 Days

I know to calculate one period length but having issues while I try to calculate length when there is a gap

CodePudding user response：

This soluction works pretty good. Although, counting days this way is kind off weird. If you start work at June 22nd and end July 22nd, have you then worked 1 month? or 30 days?

DECLARE @ServiceTbl TABLE (StartDate Date, EndDate Date)
DECLARE @StartDate date, @EndDate date
DECLARE @Sum TABLE (Years int, months int, days int)

INSERT INTO @ServiceTbl 
SELECT '08/09/2003', '06/25/2009'
UNION SELECT '06/10/2015', null    

DECLARE cur CURSOR LOCAL STATIC FORWARD_ONLY FOR
  SELECT StartDate, ISNULL(EndDate, GetDate())
  FROM @ServiceTbl
OPEN cur
WHILE 1=1
BEGIN
    FETCH NEXT FROM cur INTO @StartDate, @EndDate
    if @@FETCH_STATUS<>0 break

  ;WITH CTE AS(
    SELECT 
        0 as countYear
      , 0 as countMonth
      , DATEDIFF(Day, @StartDate, EOMONTH(@StartDate, 0)) as CountDays

      , EOMONTH(@StartDate, 1) as nextDate
      , @EndDate as EndDate
      , 0 as id
    UNION ALL SELECT 
        CASE 
          WHEN nextDate < EndDate 
          AND countMonth   1 = 12 
            THEN countYear   1
          ELSE countYear END
      , CASE 
          WHEN nextDate < EndDate 
          AND countMonth   1 < 12
            THEN countMonth   1

          WHEN nextDate < EndDate
            THEN 0
          ELSE countMonth END

      , CountDays   CASE 
          WHEN EndDate < EOMONTH(nextDate, 1) 
            THEN DATEDIFF(day, nextDate, EndDate)
          ELSE 0 END
    
      , EOMONTH(nextDate, 1)
      , EndDate
      , id   1
    FROM CTE
    WHERE nextDate < EndDate
  )
    INSERT INTO @Sum
    SELECT TOP 1 countYear, countMonth, CountDays
    FROM CTE
    ORDER BY id Desc

END
CLOSE cur DEALLOCATE cur
   


SELECT SUM(Years)   FLOOR((SUM(months)   FLOOR(SUM(Days) / 31)) / 12) as Years
  , (SUM(months)   FLOOR(SUM(Days) / 31)) % 11 as Months
  , SUM(days) % 30 as Days
FROM @Sum

CodePudding user response：

You cannot calculate the years, months and days for each period and then sum them, because you don't know how to sum the days (31, 30, 29 or 28)

So, maybe if we do sum up all these values, and then add them to the enddate of the first period then we can calculate the values from the startdate and this extended endate ?

The outcome will never be exact, because of the difference in months length, but this maybe is as close that you can get

I tested it in this DBFiddle

But, I don't get your expected result because I still don't see how you get 9 months and 21 days from your second period.

Take a look at the dBFiddle, if it is not what you are looking for then maybe it helps inspire you in the right direction

select t2.period,
       t2.startdate,
       t2.enddate,
       
       datediff(month, t2.startdate, t2.enddate) / 12 as years,
       datediff(month, t2.startdate, t2.enddate) - ((datediff(month, t2.startdate, t2.enddate) / 12) * 12) as months,
       datediff(day, 
                datefromparts(2022, datepart(month, t2.enddate), datepart(day, t2.startdate)),
                datefromparts(2022, datepart(month, t2.enddate), datepart(day, t2.enddate))
     ) as days
       
       
from   (select temp.period,
               min(temp.startdate) as startdate,
               dateadd(day, sum(temp.days), dateadd(month, sum(temp.months), dateadd(year, sum(temp.years), min(temp.startdate)))) as enddate
        from   ( select t.period,
                        t.startdate,
                        t.enddate,
                        datediff(month, t.startdate, t.enddate) / 12 as years,
                        datediff(month, t.startdate, t.enddate) - ((datediff(month, t.startdate, t.enddate) / 12) * 12) as months,
                        datediff(day, 
                                 datefromparts(2022, datepart(month, t.enddate), datepart(day, t.startdate)),
                                 datefromparts(2022, datepart(month, t.enddate), datepart(day, t.enddate))
                       ) as days
                 from   table1 t
              ) temp
        group by temp.period
      ) t2