days employee worked - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

days employee worked

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2011-10-13 : 11:39:29

Hi,
I want to know how much days an employee worked until now since he employed in company.
I running this :-

select EMPLOYID,LASTNAME,FRSTNAME,BRTHDATE,STRTDATE,GETDATE() AS CURRENTDATE
,DATEDIFF(DAY,STRTDATE,GETDATE()) AS DAYS_WORKED
from UPR00100
WHERE INACTIVE ='0'
ORDER BY STRTDATE DESC

I getting in days, BUT i want in years,months,day

How can i get.
thanks

Regards,
Sushant
DBA
West Indies

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-13 : 12:11:57

try


select	STRTDATE, SDATE,
	year_worked 	= datediff(month, STRTDATE, SDATE) / 12,
	month_worked	= datediff(month, STRTDATE, SDATE) % 12,
	days_worked	= datediff(day, SDATE, getdate())
from	(
		select	STRTDATE, SDATE = case 	when day(getdate()) >= day(STRTDATE)
						then dateadd(day, day(STRTDATE) - day(getdate()), getdate())
						else dateadd(month, -1, dateadd(day, day(STRTDATE) - day(getdate()), getdate()))
						end
		from	UPR0100
	) s

KH
[spoiler]Time is always against us[/spoiler]

skybvi
Posting Yak Master

193 Posts

Posted - 2011-10-13 : 12:41:52

where can i put other columns, whcich i intend to see in the table

I put it

EMPLOYID,LASTNAME,FRSTNAME,DEPRTMNT in the first select and it says
Invalid columns

Regards,
Sushant
DBA
West Indies

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 12:50:53


select	STRTDATE, SDATE,
	year_worked 	= datediff(month, STRTDATE, SDATE) / 12,
	month_worked	= datediff(month, STRTDATE, SDATE) % 12,
	days_worked	= datediff(day, SDATE, getdate()),
        EMPLOYID,LASTNAME,FRSTNAME,DEPRTMNT
from	(
		select	STRTDATE, SDATE = case 	when day(getdate()) >= day(STRTDATE)
						then dateadd(day, day(STRTDATE) - day(getdate()), getdate())
						else dateadd(month, -1, dateadd(day, day(STRTDATE) - day(getdate()), getdate()))
						end,
                EMPLOYID,LASTNAME,FRSTNAME,DEPRTMNT
		from	UPR0100
	) s

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2011-10-13 : 13:44:09

thanks Visakh and Khtan

Regards,
Sushant
DBA
West Indies

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources