CODEWITHSUNDEEP
sql-server-2005sql-server-2008
user1037721
user1037721

Reputation: 1

How to get the intime and outitme using sqlquery

I have a table with fields name, id, date, intime/outtime, datetime.

In this, I need to find the first intime, last outtime and the duration between first intime and last outtime of every employee.

If the employee is swiped in, then this is his 1st intime. If he moved out then it is his outtime. So, at the end of the day, his final outtime and then the duration from 1st intime and last outtime has to be calculated.

When I took the minimum of datetime, I got the 1st intime. But, if I take the maximum of outtime I get the current status that can be in/out.

While, I need to get the last out time.

Upvotes: 0

Views: 365

Answers (3)

Andriy M
Andriy M

Reputation: 77707

Possibly something like this:

SELECT
  name,
  date,
  FirstInTime = MIN(CASE [intime/outtime] WHEN 1 THEN datetime END),
  LastOutTime = MAX(CASE [intime/outtime] WHEN 2 THEN datetime END)
FROM atable
GROUP BY
  name,
  date

If the first event is always an in-time, then the FirstInTime column could be defined simply as

  …
  FirstInTime = MIN(datetime),
  …

Upvotes: 0

dude
dude

Reputation: 4972

  select name,MAX(convert(char(8),datetimecolumn,108)as outtime,MIN(convert(char(8),datetimecolumn,108) as intime , 
  (MAX( convert(char(8),datetimecolumn,108 ) - MIN( convert(char(8),datetimecolumn,108 ) )AS duration from Table

OR

SELECT name,intime/outime 
 (SELECT MAX([convert(char(8),datetimecolumn,108]) AS outtime FROM ... WHERE ...)
- (SELECT MIN([convert(char(8),datetimecolumn,108]) AS inTime FROM ... WHERE ...)
 AS duration  from TABLE

Upvotes: 1

Damith
Damith

Reputation: 63105

SELECT name, date , MIN(convert(char(8),datetimecolumn,108) AS intime , MAX(convert(char(8),datetimecolumn,108) AS outtime, (outtime -intime) as duration 
FROM myTable
GROUP BY name, date

Upvotes: 1

Related Questions