I want to have a query for the following problem

Posted by Anurag_M84 under Sql Server on 8/26/2015 | Points: 10 | Views : 531 | Status : [Member] | Replies : 4
Hi All,

I have a table of Employee Attendance in which the Employees Attendance is recorded in a Datetime Format.
This table is in SQL SERVER 2005 so does not have a time Field.

The Format is as per shown below
aDateTime emp_code CardNo
8-17-15 9:41 AM E1534516 234516
8-18-15 8:56 AM E1534517 234517
8-18-15 9:20 AM E1534516 234516
8-18-15 6:35 PM E1534517 234517
8-19-15 9:08 AM E1534517 234517
8-19-15 6:11 PM E1534517 234517
8-24-15 9:13 AM E1534517 234517
8-24-15 6:23 PM E1534517 234517
8-25-15 9:09 AM E1534517 234517
8-25-15 7:31 PM E1534517 234517
8-26-15 9:16 AM E1534517 234517

I want to Add a new column Mode in this table which will have a Value of In and Out as per the Time,
Can you help me out in how to populate the values for each Employee in this new column?

Any help regarding this is highly respected.

A Lots of Thanks in Advance for the answers provided by you.

Thanks and Regards,
Anurag

Anurag Manke


Responses

Posted by: Bandi on: 8/26/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
declare @EmployeeAttendance table (aDateTime datetime,	emp_code	varchar(100), CardNo int)
insert @EmployeeAttendance
SELECT '8-17-15 9:41 AM', 'E1534516', 234516 union all
SELECT '8-18-15 8:56 AM', 'E1534517', 234517 union all
SELECT '8-17-15 9:20 AM', 'E1534516', 234516 union all
SELECT '8-18-15 6:35 PM', 'E1534517', 234517 union all
SELECT '8-19-15 9:08 AM', 'E1534517', 234517 union all
SELECT '8-19-15 6:11 PM', 'E1534517', 234517 union all
SELECT '8-24-15 9:13 AM', 'E1534517', 234517 union all
SELECT '8-24-15 6:23 PM', 'E1534517', 234517 union all
SELECT '8-25-15 9:09 AM', 'E1534517', 234517 union all
SELECT '8-25-15 7:31 PM', 'E1534517', 234517 union all
SELECT '8-26-15 9:16 AM', 'E1534517', 234517

--I want to Add a new column Mode in this table which will have a Value of In and Out as per the Time,
--Can you help me out in how to populate the values for each Employee in this new column?

SELECT *, case ROW_NUMBER() OVER( Partition by emp_code, cast(aDateTime as date) order by aDatetime ) when 1 then 'In' when 2 THEN 'Out' END as Mode
FROM @EmployeeAttendance


output
aDateTime	        emp_code	CardNo	Mode 
2015-08-17 09:20:00.000 E1534516 234516 In
2015-08-17 09:41:00.000 E1534516 234516 Out
2015-08-18 08:56:00.000 E1534517 234517 In
2015-08-18 18:35:00.000 E1534517 234517 Out
2015-08-19 09:08:00.000 E1534517 234517 In
2015-08-19 18:11:00.000 E1534517 234517 Out
2015-08-24 09:13:00.000 E1534517 234517 In
2015-08-24 18:23:00.000 E1534517 234517 Out
2015-08-25 09:09:00.000 E1534517 234517 In
2015-08-25 19:31:00.000 E1534517 234517 Out
2015-08-26 09:16:00.000 E1534517 234517 In


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Anurag_M84, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 8/27/2015 [Member] Starter | Points: 25

Up
0
Down
Hi Bandi,

In a day, there can be many IN and OUT. Instead of writing case, when 1 when 2 when 3 when 4.......

IS there any other way ??

like, if row_number is ODD number then IN and row_number is EVEN number then OUT

How can i put above condition ( ODD number and EVEN number) in Query ??

---
Srihari

Anurag_M84, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/27/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
SELECT CASE ROW_NUMBER() OVER( ORDER BY EMPID)%2 WHEN 1 THEN 'ODD'
WHEN 0 THEN 'EVEN' END OddEven
, *
FROM dbo.Employee


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Anurag_M84, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 9/2/2015 [Member] Starter | Points: 25

Up
0
Down
Hi bandi,

I'm rewriting your code, with small modification, i hope it works


SELECT *, case ROW_NUMBER() OVER( Partition by emp_code, cast(aDateTime as date) order by aDatetime )%2 when 1 then 'In ' when 0 THEN 'Out ' END as Mode
FROM @EmployeeAttendance


---
Srihari

Anurag_M84, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response