joining tables without keys - 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)

joining tables without keys

Author

Topic

MrSethT
Starting Member

3 Posts

Posted - 2007-11-07 : 12:44:37

Let’s say I have these 2 tables

Events TimeStamp
------------- ----------------
seth logged on 11/7/07 1:00pm
Rick logged on 11/7/07 1:05pm
seth logged on 11/7/07 10:23pm

and

Data TimeStamp
--------- ----------------
5 11/7/07 1:01pm
6 11/7/07 1:02pm
7 11/7/07 1:03pm
8 11/7/07 1:03pm
9 11/7/07 1:08pm
9 11/7/07 1:15pm
10 11/7/07 1:30pm
3 11/7/07 4:06pm
3 11/7/07 4:07pm
3 11/7/07 4:08pm
3 11/7/07 4:30pm
3 11/7/07 10:24pm
3 11/7/07 10:25pm
3 11/7/07 10:27pm
3 11/7/07 10:27pm
44 11/7/07 10:45pm
5 11/7/07 11:03pm
56 11/7/07 11:42pm
6 11/7/07 11:43pm

How do you merget them to look like this?

Data TimeStamp Events
--------- ---------------- ------------
5 11/7/07 1:01pm seth logged on
6 11/7/07 1:02pm seth logged on
7 11/7/07 1:03pm seth logged on
8 11/7/07 1:03pm seth logged on
9 11/7/07 1:08pm Rick logged on
9 11/7/07 1:15pm Rick logged on
10 11/7/07 1:30pm Rick logged on
3 11/7/07 4:06pm Rick logged on
3 11/7/07 4:07pm Rick logged on
3 11/7/07 4:08pm Rick logged on
3 11/7/07 4:30pm Rick logged on
3 11/7/07 10:24pm seth logged on
3 11/7/07 10:25pm seth logged on
3 11/7/07 10:27pm seth logged on
3 11/7/07 10:27pm seth logged on
44 11/7/07 10:45pm seth logged on
5 11/7/07 11:03pm seth logged on
56 11/7/07 11:42pm seth logged on
6 11/7/07 11:43pm seth logged on

I just can't seem to get it myself
Thanks for anything,
-Seth

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-11-07 : 13:03:07

quote:
5 11/7/07 1:01pm seth logged on

According to the first table, Seth logged on at '11/7/07 1:00pm'... not '11/7/07 1:01pm' as your "merged" table concludes

Please review the guidelines here: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Nathan Skerl

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 13:07:49

as long as the event timestamp is unique you can do something like this...

declare @sample table (person varchar(10), logon datetime)

insert @sample
select 'seth', '11/7/07 1:00pm'
union all
select 'Rick', '11/7/07 1:05pm'
union all
select 'seth', '11/7/07 10:23pm'

declare @sample2 table (data int, time_stamp datetime)
insert @sample2
select 5, '11/7/07 1:01pm'
union all
select 6 ,'11/7/07 1:02pm'
union all
select 7 ,'11/7/07 1:03pm'
union all
select 8 ,'11/7/07 1:03pm'
union all
select 9 ,'11/7/07 1:08pm'
union all
select 9 ,'11/7/07 1:15pm'
union all
select 10 ,'11/7/07 1:30pm'
union all
select 3 ,'11/7/07 4:06pm'
union all
select 3 ,'11/7/07 4:07pm'
union all
select 3 ,'11/7/07 4:08pm'
union all
select 3 ,'11/7/07 4:30pm'
union all
select 3 ,'11/7/07 10:24pm'
union all
select 3 ,'11/7/07 10:25pm'
union all
select 3 ,'11/7/07 10:27pm'
union all
select 3 ,'11/7/07 10:27pm'
union all
select 44 ,'11/7/07 10:45pm'
union all
select 5 ,'11/7/07 11:03pm'
union all
select 56 ,'11/7/07 11:42pm'
union all
select 6 ,'11/7/07 11:43pm'

select data.data, data.time_stamp, person.person
from @sample2 as data
join
(
select sample2.time_stamp, max(sample.logon) as last_logon
from @sample2 as sample2
join @sample as sample
on sample2.time_stamp > sample.logon
group by sample2.time_stamp
) as last
on last.time_stamp = data.time_stamp
join @sample as person
on person.logon = last.last_logon

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-07 : 13:10:17

[code]DECLARE @event TABLE
(
Events varchar(15),
timestamp datetime
)
INSERT INTO @event
SELECT 'seth logged ON', '11/7/07 1:00pm' UNION ALL
SELECT 'Rick logged ON', '11/7/07 1:05pm' UNION ALL
SELECT 'seth logged ON', '11/7/07 10:23pm'

DECLARE @data TABLE
(
Data int,
timestamp datetime
)

INSERT INTO @data
SELECT 5, '11/7/07 1:01pm' UNION ALL
SELECT 6, '11/7/07 1:02pm' UNION ALL
SELECT 7, '11/7/07 1:03pm' UNION ALL
SELECT 8, '11/7/07 1:03pm' UNION ALL
SELECT 9, '11/7/07 1:08pm' UNION ALL
SELECT 9, '11/7/07 1:15pm' UNION ALL
SELECT 10, '11/7/07 1:30pm' UNION ALL
SELECT 3, '11/7/07 4:06pm' UNION ALL
SELECT 3, '11/7/07 4:07pm' UNION ALL
SELECT 3, '11/7/07 4:08pm' UNION ALL
SELECT 3, '11/7/07 4:30pm' UNION ALL
SELECT 3, '11/7/07 10:24pm' UNION ALL
SELECT 3, '11/7/07 10:25pm' UNION ALL
SELECT 3, '11/7/07 10:27pm' UNION ALL
SELECT 3, '11/7/07 10:27pm' UNION ALL
SELECT 44, '11/7/07 10:45pm' UNION ALL
SELECT 5, '11/7/07 11:03pm' UNION ALL
SELECT 56, '11/7/07 11:42pm' UNION ALL
SELECT 6, '11/7/07 11:43pm'

SELECT d.Data, d.timestamp, e.Events
FROM @data d
INNER JOIN
(
SELECT *,
next_TimeStamp = (SELECT MIN(timestamp) FROM @event x WHERE x.timestamp > e.timestamp)
FROM @event e
) e
ON d.timestamp >= e.timestamp
AND (
d.timestamp < e.next_TimeStamp
OR e.next_TimeStamp IS NULL
)[/code]

KH
[spoiler]Time is always against us[/spoiler]

MrSethT
Starting Member

3 Posts

Posted - 2007-11-07 : 13:12:57

The real tables are really complicated and it wouldn't help for you to know all of the other columns and tables that are involved. What it comes down to is I have to be able to see who wrote each data point and all I have to go on is a table telling me when they logged in. So, I made up these guys off the top of my head.

What I have so far is something like....

SELECT Comment_ as User_, max(fact_event.Stime) as Time_, fact_data.data_uid
FROM fact_data left join FACT_Event on fact_event.Stime<fact_data.STime
group by comment_, fact_data.data_uid

but if you really want all the other junk I guess I can give that to

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-11-07 : 13:29:37

Ok, i see what youre after now... i assumed the data point was a Login Audit, not a data point post-login.

Nathan Skerl

MrSethT
Starting Member

3 Posts

Posted - 2007-11-07 : 14:21:16

great reply khtan!!

exactly what I was looking for, and it works. That was impressive

Thanks to you all,
-Seth

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 15:01:18

SQL Server 2005?

SELECT		d.Data,
		d.Timestamp,
		d.Events
FROM		(
			SELECT		d.Data,
					d.Timestamp,
					e.Events,
					DENSE_RANK() OVER (PARTITION BY d.Timestamp ORDER BY e.Timestamp DESC) AS RecID
			FROM		@Data AS d
			INNER JOIN	@Event AS e ON e.Timestamp <= d.Timestamp
		) AS d
WHERE		d.RecID = 1

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources