Id UserId UserFirstName DOJ Location 1 5 Ashvini 2016-05-03 00:00:00.000 Mumbai 2 18 Deepak 2016-06-13 00:00:00.000 Mumbai 3 23 Devesh 2016-05-02 00:00:00.000 Mumbai 4 25 Dibyajyoti Arabinda 2016-07-06 00:00:00.000 Mumbai 5 27 Gaurav 2016-08-16 00:00:00.000 Mumbai 6 38 Izhar 2016-05-23 00:00:00.000 Mumbai 7 39 Jacob 2016-04-21 00:00:00.000 Mumbai 8 47 Mamun Al 2016-08-04 00:00:00.000 Mumbai 9 49 Manoj 2016-08-22 00:00:00.000 Mumbai 10 54 Nagesh 2016-06-01 00:00:00.000 Mumbai 11 55 Namit 2016-07-04 00:00:00.000 Mumbai
Declare @count int,@Count1 int, @DOJ datetime,@COuntdate int,@Id int set @count =(select count(UserID) from #MyTemp1) set @Count1=1 while(@Count1<=@count) begin set @Id=(select Id from #MyTemp1 where Id=@Count1) set @DOJ = (select DOJ from #MyTemp1 where @Id=@Count1) set @COuntdate= (select DATEDIFF(MONTH, (select DOJ from #MyTemp1 where Id=@Count1),GETDATE()) as Vintage) --set @COuntdate= (select DATEDIFF (month, @DOJ, getdate())) --CASE -- WHEN DATEPART(day, @DOJ) > DATEPART(day, getdate()) THEN 1 ELSE 0 --END print @COuntdate END set @Count1=@Count1+1
SELECT SUM(T.NoOfMonthsOfDOJ) AS TotalMonths FROM ( SELECT DATEDIFF(MONTH, DOJ, CONVERT(DATE, GETDATE())) AS NoOfMonthsOfDOJ --number of months for each employee FROM TmpTable ) AS T
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)