I want to have a query for the following problem

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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 ?? Mark as Answer if its helpful to you --- Srihari Anurag_M84, if this helps please login to Mark As Answer. \| Alert Moderator

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 ??

Mark as Answer if its helpful to you
---
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

0	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

0	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 Mark as Answer if its helpful to you --- Srihari Anurag_M84, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts