I want a SQL Query for the following problem

Posted by Anurag_M84 under Sql Server on 3/3/2015 | Points: 10 | Views : 318 | Status : [Member] | Replies : 2
I have a table in my Database which contains the punch time of Employee, I want to update the reader mode column of the
table with the values (I or O) according to the swipe_datetime of the Employee.

the issue is that the when the first swipe_datetime of the employee should be considered as reader_mode I
while the next should be considered as O

I am attaching a sample data along with this so that you can provide me the correct sql query.

I hope I will get the answer very soon.

Thanks in Advance for the help.

Anurag Manke


Responses

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

Up
0
Down

--Sample Data
declare @tab table( swipe_datetime datetime, Reader_Mode char(1))
insert @tab
SELECT GETDATE() , NULL union all
SELECT GETDATE()-0.001 , NULL union all
SELECT GETDATE()-0.010 , NULL union all
SELECT GETDATE()-0.040 , NULL union all
SELECT GETDATE()-0.100 , NULL union all
SELECT GETDATE()-0.400 , NULL

--Query to update according to the requirement
UPDATE temp
SET Reader_Mode = CASE WHEN RN%2 = 0 THEN 'O' ELSE 'I' END
FROM (SELECT swipe_datetime, ROW_NUMBER() OVER(ORDER BY swipe_datetime desc) RN, Reader_Mode FROM @tab ) temp

--Verify the Reader_Mode after UPDATE
SELECT * FROM @tab


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: Bandi on: 3/5/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
let me know if the above solution doesn't suits the requirement.. .

As per your explanation, query will fit for your requirement...

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

Login to post response