Get Total hours inside the office

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)

Get Total hours inside the office

Author

Topic

vikramsinh
Starting Member

4 Posts

Posted - 2009-02-20 : 05:20:12

i have following table as In out entry of office employee

Name InOUT Time
vikram IN 2009-12-12 12:00:00.000 PM
vikram OUT 2009-12-12 01:00:00.000 PM
Vikram IN 2009-12-12 01:30:00.000 PM
vikram OUT 2009-12-12 06:00:00.000 PM
vikram IN 2009-12-12 06:10:00.000 PM
vikram OUT 2009-12-12 08:10:00.000 PM

i want show total time (hours) Employee inside the office .
like follows

Name TotalHoursIN
Vikram 7.20

Thanks in advance
Vikram

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-20 : 05:33:28

SELECT Name,inout,
( select sum(case when inout = 'in' then time else 0 end) from urtable where name = t.name)
from urtable t

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 05:33:53

select outtime - intime
from (
select sum(case when inout='in' and dateadd(dd,datediff(dd,0,time),0) = '2009-12-12' then datepart(hh,time) end) as intime,
sum(case when inout='out' and dateadd(dd,datediff(dd,0,time),0) = '2009-12-12' then datepart(hh,time) end) as outtime
from @table) t

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-02-20 : 05:37:33

declare @t1 table(name varchar(20), INOUT varchar(10),time datetime)
insert into @t1
select 'vikram','IN','2009-12-12 12:00:00.000 PM' union
select 'vikram','OUT','2009-12-12 01:00:00.000 PM' union
select 'vikram','IN','2009-12-12 01:30:00.000 PM' union
select 'vikram','OUT','2009-12-12 06:00:00.000 PM' union
select 'vikram','IN','2009-12-12 06:10:00.000 PM' union
select 'vikram','OUT','2009-12-12 08:10:00.000 PM' union
select 'viky','IN','2009-12-12 06:10:00.000 PM' union
select 'viky','OUT','2009-12-12 08:10:00.000 PM'

select NAME,ot - it
from (
select name,sum(case when INOUT='in' and dateadd(dd,datediff(dd,0,TIME),0) = '2009-12-12' then datepart(hh,TIME) end) as it,
sum(case when INOUT='out' and dateadd(dd,datediff(dd,0,time),0) = '2009-12-12' then datepart(hh,TIME) end) as ot
from @t1
group by NAME
) t

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 05:48:56

quote:
Originally posted by Nageswar9

SELECT Name,inout,
( select sum(case when inout = 'in' then time else 0 end) from urtable where name = t.name)
from urtable t

not sure if this is right.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 05:55:27

quote:
Originally posted by bklr

select outtime - intime
from (
select sum(case when inout='in' and dateadd(dd,datediff(dd,0,time),0) = '2009-12-12' then datepart(hh,time) end) as intime,
sum(case when inout='out' and dateadd(dd,datediff(dd,0,time),0) = '2009-12-12' then datepart(hh,time) end) as outtime
from @table) t

Would work for only '2009-12-12'

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 05:56:42

quote:
Originally posted by sridhar.dbe

declare @t1 table(name varchar(20), INOUT varchar(10),time datetime)
insert into @t1
select 'vikram','IN','2009-12-12 12:00:00.000 PM' union
select 'vikram','OUT','2009-12-12 01:00:00.000 PM' union
select 'vikram','IN','2009-12-12 01:30:00.000 PM' union
select 'vikram','OUT','2009-12-12 06:00:00.000 PM' union
select 'vikram','IN','2009-12-12 06:10:00.000 PM' union
select 'vikram','OUT','2009-12-12 08:10:00.000 PM' union
select 'viky','IN','2009-12-12 06:10:00.000 PM' union
select 'viky','OUT','2009-12-12 08:10:00.000 PM'

select NAME,ot - it
from (
select name,sum(case when INOUT='in' and dateadd(dd,datediff(dd,0,TIME),0) = '2009-12-12' then datepart(hh,TIME) end) as it,
sum(case when INOUT='out' and dateadd(dd,datediff(dd,0,time),0) = '2009-12-12' then datepart(hh,TIME) end) as ot
from @t1
group by NAME
) t

same for this I think.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 05:57:14

quote:
Originally posted by sakets_2000

quote:
Originally posted by bklr

select outtime - intime
from (
select sum(case when inout='in' and dateadd(dd,datediff(dd,0,time),0) = '2009-12-12' then datepart(hh,time) end) as intime,
sum(case when inout='out' and dateadd(dd,datediff(dd,0,time),0) = '2009-12-12' then datepart(hh,time) end) as outtime
from @table) t

Would work for only '2009-12-12'

inthat place they will pass the required date to show the totaltime
for every employee they will calculate the time for only one day

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 05:57:27

Try this,

select name,  sum(datediff(minute,InTime,OutTime) )/60.0 from (
select name, INOUT,INTIME=time,OutTime=(select min(time) from entry b where [name]=a.[name] and INOUT='OUT' and time>a.time)
from yourtable a 
where INOUT='IN')s
group by name

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 05:59:33

quote:
inthat place they will pass the required date to show the totaltime
for every employee they will calculate the time for only one day

He never mentioned this, Besides why would you want the query to be changed so often ?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 06:10:09

You could use this if you want to see the split by days,

select name,left(convert(datetime,intime,112),10) as day, sum(datediff(minute,InTime,OutTime) )/60.0 from (
select name, INOUT,INTIME=time,OutTime=(select min(time) from entry b where [name]=a.[name] and INOUT='OUT' and time>a.time)
from yourtable a 
where INOUT='IN')s
group by name,left(convert(datetime,intime,112),10)

This would need some modification though if there are cases where user logs in one day and gets out on the other.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources