I want a SQL Query for the following problem

Posted by Anurag_M84 under Sql Server on 3/3/2015 | Points: 10 | Views : 733 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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.

Attachment

Anurag Manke

Reply | Reply with Attachment

Alert Moderator

Responses

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

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


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

0	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

Latest Posts