Door log report datediff SQL Server 2008

Question

I'm currently trying to produce data for when employees swipe in and out of various doors in the building. The data is saved in a table like -

enter image description here

Ideally id like to do a datediff for the minutes but am struggling to get it to work! anyone have any thoughts as to the best way forward?

score 2 · Answer 1 · answered Jul 31 '13 at 09:58

2

you can try this way,

select uid, serialno, datediff(m, max(time), min(time) ) from tablename 
group by uid, serialno

answered Jul 31 '13 at 09:58

Hiren gardhariya

issue i'm having is that for example the upstairs npc can be in and out 6 times in a day meaning a max and min will on get me the initial and final time difference - sorry should have been clearer – GPH Jul 31 '13 at 10:41

score 0 · Answer 2 · answered Jul 31 '13 at 09:56

0

Group on userId, serialNumber, max(time) and min(time) and then add a calculated column with the time difference.

answered Jul 31 '13 at 09:56

Gerrie Schenck

2 Answers2