Identifying Night Shifts - 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)

Identifying Night Shifts

Author

Topic

karthi2009
Starting Member

23 Posts

Posted - 2009-05-12 : 04:32:00

Hi,

We are generating reports based on the access card information(IN-Time & OUT-Time). We are able to calculate Total IN-Time and Total OUT-Time for each resource in the general shift(Dayshift). We are not able to calculate the times for Night shift employees.

For eg.
--------------------------------
Emp# | Date | Status | Time
--------------------------------
1 | Mar1 | IN | 20:00
2 | Mar2 | OUT | 06:00
--------------------------------
Please guide us how to calculate the times for such data.

Thanks in advance,

Karthikeyan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-12 : 04:56:38

What is the datatype of Time?

Madhivanan

Failing to plan is Planning to fail

karthi2009
Starting Member

23 Posts

Posted - 2009-05-12 : 05:08:09

DateTime only.

quote:
Originally posted by madhivanan

What is the datatype of Time?

Madhivanan

Failing to plan is Planning to fail

Karthikeyan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-12 : 05:12:57

You can use the DATEDIFF function.
Post the code that is working for Dayshift employees

Madhivanan

Failing to plan is Planning to fail

karthi2009
Starting Member

23 Posts

Posted - 2009-05-12 : 05:15:02

Hi,

I wanted to add more information.
---------------------------
Emp# int,
Status varchar(100),
Date DateTime
---------------------------
We are using the same 'Date' column for showing both Date and Time in the output.

The input table value is
------------------------------------------------
Emp# | Date | Status
------------------------------------------------
1 | Mar1 09:00 | IN
1 | Mar1 12:00 | OUT
1 | Mar1 13:00 | IN
1 | Mar1 18:00 | OUT
2 | Mar1 21:00 | IN
2 | Mar2 06:00 | OUT

The output we want to show is

------------------------------------------------
Emp#| Date |TotalINTime(hrs) | TotalOUTTime(hrs)
------------------------------------------------
1 | Mar1 | 09 | 1
2 | Mar1 | 09 | 0
------------------------------------------------

Please let us know in case of any additional info is reqd.

Thanks,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-12 : 05:36:44

Dont edit your reply very often. If you want to give informations post that as a new reply

Madhivanan

Failing to plan is Planning to fail

karthi2009
Starting Member

23 Posts

Posted - 2009-05-12 : 05:40:43

Hi,
Sure I will follow.
As I am posting doubts in forums for the first time,
Please help me out in this regard,
Thanks for your valuable responses,

Karthikeyan

TheOne
Starting Member

6 Posts

Posted - 2009-05-12 : 05:41:05

Hi
madhivanan,

if in case for performance we are indexing the date column ..
when the number of rows are huge..that time datediff() function
will not be useful ...so y not simply take hours and subtract

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-12 : 05:52:38

hi karthik,
try this once
declare @t table (Emp int, Date datetime, Status varchar(32))
insert into @t select 1 , '03/01/2009 09:00' , 'IN' union all select
1 , '03/01/2009 12:00' , 'OUT' union all select
1 , '03/01/2009 13:00' , 'IN' union all select
1 , '03/01/2009 18:00' , 'OUT' union all select
2 , '03/01/2009 21:00' , 'IN' union all select
2 , '03/02/2009 06:00' , 'OUT'

select t.*,datediff(hh,t.date,h.date)as dateval into #temp from (select row_number() over(order by emp) as rid, * from @t) t
inner join
(select row_number() over(order by emp) as rid, * from @t)as h on h.rid = t.rid+1 and h.emp = t.emp

select * from #temp
select emp, LEFT(CONVERT(VARCHAR(20), DATE, 100),6)as date,sum(case when status = 'in' then dateval end) incount,
sum(case when status='out' then dateval end) as outcount from #temp
group by emp,LEFT(CONVERT(VARCHAR(20), DATE, 100),6)

drop table #temp

karthi2009
Starting Member

23 Posts

Posted - 2009-05-12 : 08:19:49

Hi bklr,
We are able to get the output based on this concept. But still we are getting it partially as there is a lot of tailgated entries also in the table values. will you please help us to sort it out.

------------------------------------------------
Emp# | Date | Status
------------------------------------------------
1 | Mar1 09:00 | IN
1 | Mar1 12:00 | OUT
1 | Mar1 13:00 | IN (Emp# 1 Tailgated for OUT)
2 | Mar1 21:00 | IN
2 | Mar1 22:00 | OUT
2 | Mar2 06:00 | OUT (Emp# 2 Tailgated for IN)

Thanks,

Karthikeyan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources