Merge two tables. - SQL Server Forums

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)

Merge two tables.

Author

Topic

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-20 : 17:10:44

I have two tables:
Table A
user_id time creative site
18023231 4/10/2010 8:23:03 1x1 Pixel AOL
18023231 4/10/2010 9:33:25 B EA Pogo
18023231 4/10/2010 9:33:25 B EA Pogo
18023231 4/10/2010 9:33:56 A AOL
18023231 4/10/2010 9:33:56 A AOL
18023231 4/10/2010 10:23:34 B EA Pogo
18023231 4/10/2010 10:23:34 B EA Pogo
18023231 4/10/2010 12:17:34 A Weatherbug
18023231 4/10/2010 12:17:34 A Weatherbug
18023231 4/10/2010 14:46:06 B AOL
18023231 4/10/2010 14:46:06 B AOL
18023231 4/10/2010 14:50:01 A AOL
18023231 4/10/2010 14:50:01 A AOL
18023231 4/10/2010 17:51:09 1x1 Pixel AOL
18023231 4/10/2010 17:51:09 1x1 Pixel AOL

Table B

user_id time creative site
18023231 4/10/2010 12:55:00 A Weatherbug

I want to get following output:
user_id time creative site click
18023231 4/10/2010 8:23:03 1x1 Pixel AOL 0
18023231 4/10/2010 9:33:25 B EA Pogo 0
18023231 4/10/2010 9:33:25 B EA Pogo 0
18023231 4/10/2010 9:33:56 A AOL 0
18023231 4/10/2010 9:33:56 A AOL 0
18023231 4/10/2010 10:23:34 B EA Pogo 0
18023231 4/10/2010 10:23:34 B EA Pogo 0
18023231 4/10/2010 12:17:34 A Weatherbug 1
18023231 4/10/2010 12:17:34 A Weatherbug 1
18023231 4/10/2010 14:46:06 B AOL 0
18023231 4/10/2010 14:46:06 B AOL 0
18023231 4/10/2010 14:50:01 A AOL 0
18023231 4/10/2010 14:50:01 A AOL 0
18023231 4/10/2010 17:51:09 1x1 Pixel AOL 0
18023231 4/10/2010 17:51:09 1x1 Pixel AOL 0

Basically add click =1 in merged file, where everthing is same (user_id, creative, site) and time differs by 1 hour and click=0 otherwise.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-21 : 00:29:42

quote:
Originally posted by tamancha.1
18023231 4/10/2010 12:17:34 A Weatherbug 1
18023231 4/10/2010 12:17:34 A Weatherbug 1

A little more clarification is reqd. Why both Weatherbug is having value of 1 for click. The time difference between the two is not 1 hour.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 01:33:30

[code]
select a.*.
case when b.user_id is not null and datediff(hh,a.time,b.time) <= 1 then 1 else 0 end as click
from TableA a
LEFT JOIN TableB b
ON b.user_id = a.user_id
and b.creative = a.creative
and b.site = a.site
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources