Create two new variables in the table.

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

Transact-SQL (2005)

Create two new variables in the table.

Author

Topic

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-30 : 15:25:58

user imp_time imp_count click click_time
a 4/3/2010 9:48:03 1 0 NULL
a 4/8/2010 8:38:51 2 1 4/8/2010 14:21:11
a 4/8/2010 14:21:05 3 1 4/8/2010 14:21:11
a 4/8/2010 14:45:02 4 0 NULL
a 4/8/2010 20:18:05 5 0 NULL
a 4/10/2010 14:05:08 6 0 NULL
b 3/10/2010 12:45:19 1 1 3/10/2010 15:10
b 3/10/2010 12:48:26 2 0 NULL
b 3/10/2010 12:50:39 3 1 3/10/2010 15:10
b 3/10/2010 12:56:12 4 0 NULL
b 3/10/2010 12:57:04 5 0 NULL
b 3/10/2010 12:57:53 6 0 NULL
b 3/10/2010 13:02:44 7 0 NULL
b 3/10/2010 13:06:15 8 0 NULL
b 3/10/2010 13:11:22 9 0 NULL
b 3/10/2010 14:18:08 10 1 3/10/2010 15:10

I want to create two new variables in this table, final_click and final_click_time.

user imp_time imp_count click click_time final_click final_click_time
a 4/3/2010 9:48:03 1 0 NULL 0 NULL
a 4/8/2010 8:38:51 2 1 4/8/2010 14:21:11 0 NULL
a 4/8/2010 14:21:05 3 1 4/8/2010 14:21:11 1 4/8/2010 14:21:11
a 4/8/2010 14:45:02 4 0 NULL 0 NULL
a 4/8/2010 20:18:05 5 0 NULL 0 NULL
a 4/10/2010 14:05:08 6 0 NULL 0 NULL
b 3/10/2010 12:45:19 1 1 3/10/2010 15:10 0 NULL
b 3/10/2010 12:48:26 2 0 NULL 0 NULL
b 3/10/2010 12:50:39 3 1 3/10/2010 15:10 0 NULL
b 3/10/2010 12:56:12 4 0 NULL 0 NULL
b 3/10/2010 12:57:04 5 0 NULL 0 NULL
b 3/10/2010 12:57:53 6 0 NULL 0 NULL
b 3/10/2010 13:02:44 7 0 NULL 0 NULL
b 3/10/2010 13:06:15 8 0 NULL 0 NULL
b 3/10/2010 13:11:22 9 0 NULL 0 NULL
b 3/10/2010 14:18:08 10 1 3/10/2010 15:10 1 3/10/2010 15:10

For each user, we check where click=1. We assign the final_click=1 to the highest imp_time where click=1 and for others we assign final_click= 0.
We assign the final_click_time same as click_time where click=1 and imp_time is greatest among all click=1.
Thanks.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-30 : 15:52:09

This?

select a.*, case when b.imp_time is null then 0 else 1 end as final_click, b.imp_time as final_click_time
from table1 a left join
(
select [user], max(imp_time) as imp_time
from table1 where click = 1
group by [user]
) b
on a.[user] = b.[user] and a.imp_time = b.imp_time

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-30 : 15:53:57

Here's some sample data and the result
Sample Data

declare @t table 
([user] varchar(1), imp_time datetime, imp_count int, click int, click_time datetime)
insert @t
select 'a', '4/3/2010 9:48:03', 1, 0, NULL
union all select 'a', '4/8/2010 8:38:51', 2, 1, '4/8/2010 14:21:11'
union all select 'a', '4/8/2010 14:21:05', 3, 1, '4/8/2010 14:21:11'
union all select 'a', '4/8/2010 14:45:02', 4, 0, NULL
union all select 'a', '4/8/2010 20:18:05', 5, 0, NULL
union all select 'a', '4/10/2010 14:05:08', 6, 0, NULL
union all select 'b', '3/10/2010 12:45:19', 1, 1, '3/10/2010 15:10'
union all select 'b', '3/10/2010 12:48:26', 2, 0, NULL
union all select 'b', '3/10/2010 12:50:39', 3, 1, '3/10/2010 15:10'
union all select 'b', '3/10/2010 12:56:12', 4, 0, NULL
union all select 'b', '3/10/2010 14:18:08', 10, 1, '3/10/2010 15:10'

Query

select a.*, case when b.imp_time is null then 0 else 1 end as final_click, b.imp_time as final_click_time
from @t a left join
(
select [user], max(imp_time) as imp_time
from @t where click = 1
group by [user]
) b
on a.[user] = b.[user] and a.imp_time = b.imp_time

Result

user imp_time                imp_count   click       click_time              final_click final_click_time
---- ----------------------- ----------- ----------- ----------------------- ----------- -----------------------
a    2010-04-03 09:48:03.000 1           0           NULL                    0           NULL
a    2010-04-08 08:38:51.000 2           1           2010-04-08 14:21:11.000 0           NULL
a    2010-04-08 14:21:05.000 3           1           2010-04-08 14:21:11.000 1           2010-04-08 14:21:05.000
a    2010-04-08 14:45:02.000 4           0           NULL                    0           NULL
a    2010-04-08 20:18:05.000 5           0           NULL                    0           NULL
a    2010-04-10 14:05:08.000 6           0           NULL                    0           NULL
b    2010-03-10 12:45:19.000 1           1           2010-03-10 15:10:00.000 0           NULL
b    2010-03-10 12:48:26.000 2           0           NULL                    0           NULL
b    2010-03-10 12:50:39.000 3           1           2010-03-10 15:10:00.000 0           NULL
b    2010-03-10 12:56:12.000 4           0           NULL                    0           NULL
b    2010-03-10 14:18:08.000 10          1           2010-03-10 15:10:00.000 1           2010-03-10 14:18:08.000

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-30 : 16:13:49

Great. Thanks.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-30 : 16:59:38

You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources