SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Matching entries with mismatch timestamps
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WebKill
Starting Member

29 Posts

Posted - 04/25/2014 :  02:12:47  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
434 Posts

Posted - 04/25/2014 :  04:07:34  Show Profile  Reply with Quote


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


output

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




sabinWeb MCP
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
434 Posts

Posted - 04/25/2014 :  04:43:13  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000