September 24, 2007 at 2:40 am
I have this new acquisition wherein I have to transfer the data from different structure to a new one. The old structure is this...
tblswipes
select 1 as id, 111 as emp_no, '2007-05-01 07:21:07' as dtr, 0 as dtrtag union all -- ok
select 2 as id, 111 as emp_no, '2007-05-01 21:21:07' as dtr, 1 as dtrtag union all -- ok
select 3 as id, 111 as emp_no, '2007-05-02 07:21:07' as dtr, 0 as dtrtag union all -- ok
select 4 as id, 111 as emp_no, '2007-05-02 21:23:07' as dtr, 1 as dtrtag union all -- ok
select 5 as id, 111 as emp_no, '2007-05-03 07:22:07' as dtr, 0 as dtrtag union all -- ok
select 6 as id, 111 as emp_no, '2007-05-04 07:22:27' as dtr, 1 as dtrtag union all -- ok
select 7 as id, 111 as emp_no, '2007-05-04 07:22:58' as dtr, 0 as dtrtag union all -- ok
select 8 as id, 111 as emp_no, '2007-05-04 12:00:07' as dtr, 1 as dtrtag union all -- ok
select 9 as id, 111 as emp_no, '2007-05-04 12:51:08' as dtr, 0 as dtrtag union all -- ok
select 10 as id, 111 as emp_no, '2007-05-04 21:21:07' as dtr, 1 as dtrtag union all -- ok
select 11 as id, 111 as emp_no, '2007-05-05 07:51:08' as dtr, 0 as dtrtag union all -- ok
select 12 as id, 111 as emp_no, '2007-05-05 21:21:07' as dtr, 1 as dtrtag union all -- ok
select 13 as id, 113 as emp_no, '2007-05-05 07:51:07' as dtr, 0 as dtrtag union all -- ok
select 14 as id, 113 as emp_no, '2007-05-05 21:33:33' as dtr, 1 as dtrtag union all -- ok
Regarding dtrtag, 0 means IN and 1 means OUT. What I did is separating the IN and OUT into a derived table then joining them by emp_no and dtr dates. But I have problems with datas with ID nos 6-10.
Any help would be greatly appreciated.
Result should be like this...
select 111 as emp_no, '2005-05-01' as dtrdate, '2007-05-01 07:21:07' as login, '2007-05-01 21:21:07' as logout union all
select 111 as emp_no, '2005-05-02' as dtrdate, '2007-05-02 07:21:07' as login, '2007-05-02 21:23:07' as logout union all
select 111 as emp_no, '2005-05-03' as dtrdate, '2007-05-03 07:22:07' as login, '2007-05-04 07:22:27' as logout union all
select 111 as emp_no, '2005-05-04' as dtrdate, '2007-05-03 07:22:58' as login, '2007-05-04 12:00:07' as logout union all
select 111 as emp_no, '2005-05-04' as dtrdate, '2007-05-04 12:51:08' as login, '2007-05-04 21:21:07' as logout union all
select 111 as emp_no, '2005-05-05' as dtrdate, '2007-05-05 07:51:08' as login, '2007-05-05 21:21:07' as logout union all
select 113 as emp_no, '2005-05-05' as dtrdate, '2007-05-05 07:51:07' as login, '2007-05-05 21:33:33' as logout
September 24, 2007 at 4:03 am
Jonas,
Hi, if this is a one off you could use a cursor (:sick:) to run through each row in the table to transform it to the new structure. I'm guessing that employee's records will be mixed with their login and out dates.
Having inserted the data given in to a temporary table called #swipes, I think this cursor you could use this cursor to achieve your goal:
CREATE TABLE #newSwipes(emp_no SMALLINT, dtrDate CHAR(8), login DATETIME, logout DATETIME NULL)
DECLARE @emp_no SMALLINT, @dtr DATETIME, @dtrTag BIT
DECLARE csr_swipe CURSOR FOR
SELECT emp_no, dtr, dtrTag FROM #swipes ORDER BY dtr
OPEN csr_swipe
FETCH NEXT FROM csr_swipe INTO @emp_no, @dtr, @dtrTag
WHILE @@FETCH_STATUS = 0
BEGIN
IF @dtrTag = 0 -- Login
INSERT INTO #newSwipes (emp_no, dtrDate, login) SELECT @emp_no, CONVERT(CHAR(8), @dtr, 112), @dtr
ELSE -- Logout
UPDATE #newSwipes SET logout = @dtr
WHERE emp_no = @emp_no
AND logout IS NULL
FETCH NEXT FROM csr_swipe INTO @emp_no, @dtr, @dtrTag
END
CLOSE csr_swipe
DEALLOCATE csr_swipe
SELECT * FROM #newSwipes
DROP TABLE #swipes
DROP TABLE #newSwipes
This method assumes that a user can not log in twice without logging out.
September 24, 2007 at 4:14 am
Thnks! Any idea what this may cause for approximately 2M rows? 🙂
September 24, 2007 at 4:25 am
Because of the performance degradation a cursor so happily supplies this could take anything up to 30 minutes to perform. At the moment, I can't see a set-based answer to this as the emp_no's can be intertwined, although I am looking for one as I, like many others hate (with a vigorous passion) cursors. :hehe:
September 24, 2007 at 5:21 am
Can it be that some user will log in twice without logging off in between? In other words, is the sequence of dtrtag for each user 0-1-0-1-0-1-0-1 etc., and does it always start with 0? If not, you'll have to deal with it accordingly, and decide what to do when the sequence is broken, or starts with 1 and so on.
If the sequence is "clean", you could probably use insert into 2 temporary tables to your advantage - one for login, one for logout. Then you could mark each row with a sequence number (beginning from 1 for each emp_no, ordered by date).. and then just join the tables on emp_no, sequence_no.
If it isn't, then first describe all possibilities, make sure you know what is desired result in each situation, and try to find some other solution - maybe similar to this one, maybe absolutely different. Depends on requirements.
BTW, the cursor may take 30 minutes, but it also could take a lot more IMHO. Depends on many things, including hardware.
September 24, 2007 at 5:27 am
Vladan,
I think that this is possible using the two temporary table method. However, would the two temporary tables joined on emp_no and unique ID not require that the users log on and off in the same order. Would it cover:
User 1 On; User 2 On; User 3 On
User 1 Off; User 3 Off; User 2 Off
?
Ade
September 24, 2007 at 5:39 am
I didn't mean to use unique ID. The tables would be joined on emp_no and sequence_no, where the sequence always start from 1 for each employee - that means only the combination of emp_no and sequence_no would be unique.
This should work regardless of the order the individual users log in and out, as long as they always log out before logging in next time - and vice versa.
September 24, 2007 at 5:40 am
September 24, 2007 at 9:06 am
Please look at these data you provided for the result:
select 111 as emp_no, '2005-05-04' as dtrdate, '2007-05-03 07:22:58' as login, '2007-05-04 12:00:07' as logout
I suppose there is a typo and the value for login should be '2007-05-04 07:22:58'.
I tested the use of 2 temp tables (mentioned above) with provided sample of data and it worked well.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply