Dear All , I am having a table login(empid,logindate,location) logintime column contains both logindate and logoutdate minimum date of perticular day is logintime nad maxim date of same day is logouttime. i want to know ho many person login and logout on hourly basis what i am looking for every hour some person login and same time some person logout so i need a output like sum of all employee who are currently in office on hourly basis. suppose at 8am 20 peson login and at the same time 5 person logout so my count wil be 15 at 8am. means at 8 am 15 people are currently in office. i am looking for result like countofemplogin countopepeoplelogout inoffice hour 20 5 15 8am
select empid,count(login) as countofemplogin,sum(case when login=logout then 0 else 1 end) as countofemplogout from ( select empid,min(loginndate) as login,max(loginndate) as logout from table group by empid ) as t group by empid
LOGIN & LOGOUT
Posted - 05/22/2012 : 05:28:30
