Transform from other DB structure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Transform from other DB structure

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-09-24 : 02:15:28

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

Want Philippines to become 1st World COuntry? Go for World War 3...

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2007-09-24 : 09:15:54

It's looks like you're going to have to move through the data sequentially. How consistent is the data? Will there always be a login for every logout?

If your data is extremely consistent, you could go quick and dirty and split the data into two tables, each with a rowid. One table would be for logins and the other for logouts. Then you could join on the rowID.

Or you could put your own key on the data. Add a rownum column(or put in a new table structure) and order it in emp_no, date order and put a clustered index on rownum. Then add one more column for your new key. Then do something like this.

declare @new_key as int
declare @emp_no as int

Update table
Set new_key = @new_key = case when @emp_no = emp_no and dtrtag = 1 then @new_key else rownum end,
@emp_no = emp_no

Then your login and logouts should be easy to pair.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources