Merge two tables based on last time.

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 based on last time.

Author

Topic

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-23 : 16:19:29

Table 1:
user time_stamp Ad Imp_num
A 3/14/2010 20:40:08 AA 1
A 3/14/2010 20:40:02 AA 2
A 3/15/2010 22:57:00 AA 3
A 3/16/2010 11:40:03 AA 4
A 3/16/2010 11:40:03 AB 5
A 3/20/2010 10:36:00 AA 6
A 3/22/2010 7:26:48 AA 7
B 4/4/2010 8:03:38 AA 1
B 4/4/2010 8:05:38 AA 2
B 4/4/2010 8:06:29 AA 3
B 4/4/2010 8:07:25 AA 4
B 4/4/2010 8:08:19 AA 5
C 4/3/2010 20:05:46 AB 1
C 4/3/2010 20:07:13 AB 2
D 3/19/2010 0:26:36 AA 1
D 3/19/2010 0:27:41 AA 2
D 3/19/2010 0:30:05 AA 3
D 3/19/2010 0:31:22 AA 4
D 3/19/2010 0:32:29 AA 5
D 3/19/2010 0:33:47 AA 6
D 3/19/2010 0:34:22 AB 7
D 3/19/2010 0:36:25 AB 8

Table 2:
user time_stamp Ad
A 3/14/2010 20:40:06 AA
B 4/4/2010 8:08:25 AA
B 4/4/2010 8:08:42 AA
D 3/19/2010 0:38:28 AA
D 3/19/2010 0:38:37 AA
D 3/19/2010 0:38:38 AA
C 4/3/2010 20:07:24 AB

Joined Table:
user time_stamp Ad Imp_num click click_time
A 3/14/2010 20:40:08 AA 1 0 NULL
A 3/14/2010 20:40:02 AA 2 1 3/14/2010 20:40:06
A 3/15/2010 22:57:00 AA 3 0 NULL
A 3/16/2010 11:40:03 AA 4 0 NULL
A 3/16/2010 11:40:03 AB 5 0 NULL
A 3/20/2010 10:36:00 AA 6 0 NULL
A 3/22/2010 7:26:48 AA 7 0 NULL
B 4/4/2010 8:03:38 AA 1 0 NULL
B 4/4/2010 8:05:38 AA 2 0 NULL
B 4/4/2010 8:06:29 AA 3 0 NULL
B 4/4/2010 8:07:25 AA 4 0 NULL
B 4/4/2010 8:08:19 AA 5 1 4/4/2010 8:08:25
B 4/4/2010 8:08:19 AA 5 1 4/4/2010 8:08:42
C 4/3/2010 20:05:46 AB 1 1 NULL
C 4/3/2010 20:07:13 AB 2 1 4/3/2010 20:07:24
D 3/19/2010 0:26:36 AA 1 0 NULL
D 3/19/2010 0:27:41 AA 2 0 NULL
D 3/19/2010 0:30:05 AA 3 0 NULL
D 3/19/2010 0:31:22 AA 4 0 NULL
D 3/19/2010 0:32:29 AA 5 0 NULL
D 3/19/2010 0:33:47 AA 6 1 3/19/2010 0:38:28
D 3/19/2010 0:33:47 AA 6 1 3/19/2010 0:38:37
D 3/19/2010 0:33:47 AA 6 1 3/19/2010 0:38:38
D 3/19/2010 0:34:22 AB 7 0 NULL
D 3/19/2010 0:36:25 AB 8 0 NULL

Join table 1 and table2 where:
user.table1=user.table2,
ad.table1=ad.table2,
Assign click=1 and table2.time_stamp in joined table
if user and ad are same table1.time_stamp <= table2.time_stamp and day.table1=day.table2 (day in time_stamp)and assign this to the latest time stamp satisfying all these condition, otherwise assign 0 and null value in joined table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 00:45:41

[code]SELECT t1.*,
CASE WHEN t2.time_stamp IS NOT NULL 1 ELSE 0 END,
t2.time_stamp
FROM Table1 t1
OUTER APPLY(SELECT TOP 1 time_stamp
FROM Table2
WHERE user=t1.user
AND Ad = t1.Ad
AND time_stamp> t1.time_stamp
AND time_stamp < DATEADD(dd,DATEDIFF(dd,0,t1.time_stamp)+1,0)
)t2
[/code]

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

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-24 : 01:04:36

CASE expression should be:

quote:

Originally posted by visakh16

SELECT t1.*,
CASE WHEN t2.time_stamp IS NOT NULL THEN 1 ELSE 0 END,
t2.time_stamp
FROM Table1 t1
OUTER APPLY(SELECT TOP 1 time_stamp
            FROM Table2
            WHERE user=t1.user
            AND Ad = t1.Ad
            AND time_stamp> t1.time_stamp
            AND time_stamp < DATEADD(dd,DATEDIFF(dd,0,t1.time_stamp)+1,0)
           )t2

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 01:25:44

oops ...missed that
thanks for the catch

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 01:35:01

oops ...missed that
thanks for the catch

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

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-24 : 01:59:57

quote:
Originally posted by visakh16

oops ...missed that
thanks for the catch

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

Welcome. Why did you send two same post?

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-27 : 10:49:20

Thanks for your reply. But this does not work.It does not give me the same output as I mentioned in the post. Can you check please?

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-27 : 10:55:22

We need to take the top timestamp from table 1, not table 2 and if everything matches, (add two new columns from )table2 records to table 1 with the top time stamp.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 12:33:36

[code]
SELECT t1.*,
CASE WHEN t2.[ time_stamp] IS NOT NULL THEN 1 ELSE 0 END,
t2.[ time_stamp]
FROM TableA t1
OUTER APPLY(SELECT [ time_stamp]
FROM TableB
WHERE [user]=t1.[user]
AND Ad = t1.Ad
AND [ time_stamp]> t1.[ time_stamp]
AND [ time_stamp] < DATEADD(mi,DATEDIFF(mi,0,t1.[ time_stamp])+1,0)
)t2
[/code]
i dont understand last part you get for D. it doesnt fall within the 1 minute slot as others so not sure how to generalise

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

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-27 : 14:31:19

No, we cannot generalize it on 1 or 2 minute. We want to assign two new columns to the last timestamp if everything matches. I have 2 million records in some cases the difference is 1 hour, some 1 minutes,...so I dont want to generalize on time...I want to add 1 to the latest time, when everything matches.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 05:50:48

i want to add 1
does that mean add a single time. i dont think so as you're associating three different values as per sample

D 3/19/2010 0:33:47 AA 6 1 3/19/2010 0:38:28
D 3/19/2010 0:33:47 AA 6 1 3/19/2010 0:38:37
D 3/19/2010 0:33:47 AA 6 1 3/19/2010 0:38:38

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

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-28 : 09:49:52

I am adding three different values from table 2 into table 1 latest time, which is 3/19/2010 0:33:47

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-28 : 09:50:58

Hello Visakhm! Thanks for taking time to answer my query. Here is the idea:

Records from Table 1:
user time_stamp Ad Imp_num
D 3/19/2010 0:26:36 AA 1
D 3/19/2010 0:27:41 AA 2
D 3/19/2010 0:30:05 AA 3
D 3/19/2010 0:31:22 AA 4
D 3/19/2010 0:32:29 AA 5
D 3/19/2010 0:33:47 AA 6
D 3/19/2010 0:34:22 AB 7
D 3/19/2010 0:36:25 AB 8

Records from Table 2
user time_stamp Ad
D 3/19/2010 0:38:28 AA
D 3/19/2010 0:38:37 AA
D 3/19/2010 0:38:38 AA

Merged Table
user time_stamp Ad Imp_num click click_time
D 3/19/2010 0:26:36 AA 1 0 NULL
D 3/19/2010 0:27:41 AA 2 0 NULL
D 3/19/2010 0:30:05 AA 3 0 NULL
D 3/19/2010 0:31:22 AA 4 0 NULL
D 3/19/2010 0:32:29 AA 5 0 NULL
D 3/19/2010 0:33:47 AA 6 1 3/19/2010 0:38:28
D 3/19/2010 0:33:47 AA 6 1 3/19/2010 0:38:37
D 3/19/2010 0:33:47 AA 6 1 3/19/2010 0:38:38
D 3/19/2010 0:34:22 AB 7 0 NULL
D 3/19/2010 0:36:25 AB 8 0 NULL

For Imp_num=7,8 the ad in table1 does not match ad in table 2 so we add 0 and null value.
For Imp_num 1 to 6, the ad in table 1 matches the ad in table 2, so we go to time stamp.
If user_id and ad matches then we make sure time_stamp in table 1 is less than timestamp in table2. If no, we assign click=0 click_time=NULL
If yes, we further assign the click=1 and timestamp from table 2 to the latest time in table 1.Since there are 3 values in table 2
that satisfies all these conditions we add all of them in merged table.
3/19/2010 0:33:47 is the latest time(or largest time) in table 1, user and ad is same and tbl1_time_stamp
is less than table2 timestamp so we assign all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 11:43:51

[code]
SELECT [user],[time_stamp],Ad,Imp_num,
CASE WHEN Seq=1 THEN 1 ELSE 0 END AS click,
CASE WHEN Seq=1 THEN timeafter ELSE NULL END AS click_time
FROM
(
SELECT *,DENSE_RANK() OVER (PARTITION BY [user],Ad ORDER BY CASE WHEN t2.timeafter IS NULL THEN '1900-01-01' ELSE t1.[time_stamp] END DESC ) AS Seq
FROM TableA t1
OUTER APPLY(SELECT [time_stamp] as timeafter
FROM TableB
WHERE [user]=t1.[user]
AND Ad = t1.Ad
AND [time_stamp]> t1.[time_stamp]
)t2
)t
ORDER BY [user],Ad,Imp_num

output
--------------------------------------------------
user time_stamp Ad Imp_num click click_time
A 2010-03-14 20:40:08.000 AA 1 0 NULL
A 2010-03-14 20:40:02.000 AA 2 1 2010-03-14 20:40:06.000
A 2010-03-15 22:57:00.000 AA 3 0 NULL
A 2010-03-16 11:40:03.000 AA 4 0 NULL
A 2010-03-20 10:36:00.000 AA 6 0 NULL
A 2010-03-22 07:26:48.000 AA 7 0 NULL
A 2010-03-16 11:40:03.000 AB 5 1 NULL
B 2010-04-04 08:03:38.000 AA 1 0 NULL
B 2010-04-04 08:03:38.000 AA 1 0 NULL
B 2010-04-04 08:05:38.000 AA 2 0 NULL
B 2010-04-04 08:05:38.000 AA 2 0 NULL
B 2010-04-04 08:06:29.000 AA 3 0 NULL
B 2010-04-04 08:06:29.000 AA 3 0 NULL
B 2010-04-04 08:07:25.000 AA 4 0 NULL
B 2010-04-04 08:07:25.000 AA 4 0 NULL
B 2010-04-04 08:08:19.000 AA 5 1 2010-04-04 08:08:25.000
B 2010-04-04 08:08:19.000 AA 5 1 2010-04-04 08:08:42.000
C 2010-04-03 20:05:46.000 AB 1 0 NULL
C 2010-04-03 20:07:13.000 AB 2 1 2010-04-03 20:07:24.000
D 2010-03-19 00:26:36.000 AA 1 0 NULL
D 2010-03-19 00:26:36.000 AA 1 0 NULL
D 2010-03-19 00:26:36.000 AA 1 0 NULL
D 2010-03-19 00:27:41.000 AA 2 0 NULL
D 2010-03-19 00:27:41.000 AA 2 0 NULL
D 2010-03-19 00:27:41.000 AA 2 0 NULL
D 2010-03-19 00:30:05.000 AA 3 0 NULL
D 2010-03-19 00:30:05.000 AA 3 0 NULL
D 2010-03-19 00:30:05.000 AA 3 0 NULL
D 2010-03-19 00:31:22.000 AA 4 0 NULL
D 2010-03-19 00:31:22.000 AA 4 0 NULL
D 2010-03-19 00:31:22.000 AA 4 0 NULL
D 2010-03-19 00:32:29.000 AA 5 0 NULL
D 2010-03-19 00:32:29.000 AA 5 0 NULL
D 2010-03-19 00:32:29.000 AA 5 0 NULL
D 2010-03-19 00:33:47.000 AA 6 1 2010-03-19 00:38:28.000
D 2010-03-19 00:33:47.000 AA 6 1 2010-03-19 00:38:37.000
D 2010-03-19 00:33:47.000 AA 6 1 2010-03-19 00:38:38.000
D 2010-03-19 00:34:22.000 AB 7 1 NULL
D 2010-03-19 00:36:25.000 AB 8 1 NULL

[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources