Matching entries with mismatch timestamps

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

Transact-SQL (2008)

Matching entries with mismatch timestamps

Author

Topic

WebKill
Starting Member

32 Posts

Posted - 2014-04-25 : 02:12:47

I have two tables, one with records, one with log entries of records loaded. The log entry starts before the records load so the timestamp is before the timestamp of the records in the datatable. If there was only one logtable entry per day it wouldn't be so bad but there could be multiple.

I have fixed the process so that going forward the timestamps will match up, but I wanted to fix past records by replacing the datatable datetime with the logtable one, but I am having trouble matching the specific logtable entry to the datatable items.

DECLARE @DataTable TABLE
(
ActivityTime DATETIME,
ID varchar(10)
)
INSERT INTO @DataTable VALUES
('2014-04-08 8:01:00.000', 'R1'),
('2014-04-08 8:01:00.000', 'R2'),
('2014-04-08 8:02:00.000', 'R3'),
('2014-04-08 8:02:00.000', 'R4'),
('2014-04-08 8:03:00.000', 'R5'),
('2014-04-08 8:03:00.000', 'R6'),
('2014-04-08 8:11:00.000', 'R7'),
('2014-04-08 8:11:00.000', 'R8'),
('2014-04-08 8:12:00.000', 'R9'),
('2014-04-08 8:12:00.000', 'R10'),
('2014-04-08 8:13:00.000', 'R11'),
('2014-04-08 8:13:00.000', 'R12')

DECLARE @LogTable TABLE
(
LogTime DATETIME,
recordcount int
)
INSERT INTO @LogTable VALUES
('2014-04-08 8:00:00.000', 6),
('2014-04-08 8:10:00.000', 6)

In this single day example, I would want DataTable records R1 - R6 to have the activitytime updated to '2014-04-08 8:00:00.000', and R7 - R12 to be updated to '2014-04-08 8:10:00.000'

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-25 : 04:07:34

[code]

DECLARE @DataTable TABLE
(
ActivityTime DATETIME,
ID varchar(10)
)
INSERT INTO @DataTable VALUES
('2014-04-08 8:01:00.000', 'R1'),
('2014-04-08 8:01:00.000', 'R2'),
('2014-04-08 8:02:00.000', 'R3'),
('2014-04-08 8:02:00.000', 'R4'),
('2014-04-08 8:03:00.000', 'R5'),
('2014-04-08 8:03:00.000', 'R6'),
('2014-04-08 8:11:00.000', 'R7'),
('2014-04-08 8:11:00.000', 'R8'),
('2014-04-08 8:12:00.000', 'R9'),
('2014-04-08 8:12:00.000', 'R10'),
('2014-04-08 8:13:00.000', 'R11'),
('2014-04-08 8:13:00.000', 'R12')

DECLARE @LogTable TABLE
(
LogTime DATETIME,
recordcount int
)
INSERT INTO @LogTable VALUES
('2014-04-08 8:00:00.000', 6),
('2014-04-08 8:10:00.000', 6),
('2014-04-08 8:20:00.000', 6)

;with LogTable
AS
( select
LogTime
,recordCount
,ROW_NUMBER() OVER(Order By LogTime ASC) as Rn
from
@LogTable
)

UPDATE A
SET
A.ActivityTime=LT.LogTime
FROM
@DataTable A
INNER JOIN LogTable LT
ON A.ActivityTime >= LT.LogTime
INNER JOIN LogTable LT2
ON A.ActivityTime <= LT2.LogTime
AND LT.rn+1=LT2.Rn

select * from @DataTable
[/code]

output
[code]
ActivityTime ID
2014-04-08 08:00:00.000 R1
2014-04-08 08:00:00.000 R2
2014-04-08 08:00:00.000 R3
2014-04-08 08:00:00.000 R4
2014-04-08 08:00:00.000 R5
2014-04-08 08:00:00.000 R6
2014-04-08 08:10:00.000 R7
2014-04-08 08:10:00.000 R8
2014-04-08 08:10:00.000 R9
2014-04-08 08:10:00.000 R10
2014-04-08 08:10:00.000 R11
2014-04-08 08:10:00.000 R12
[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-25 : 04:43:13

another way, the same output



UPDATE  A
SET 
	A.ActivityTime=LT.LogTime
FROM
	@DataTable A
	OUTER APPLY
	(
		SELECT TOP 1 LogTime
		FROM LogTable
		WHERE ActivityTime > = LogTime
		ORDER BY LogTime DESC )LT

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources