Minimum Time Difference between Tables

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

General SQL Server Forums

New to SQL Server Programming

Minimum Time Difference between Tables

Author

Topic

vcs1161
Starting Member

10 Posts

Posted - 2013-09-03 : 12:10:25

I receive the following result set from TableA (In Time)
7/9/2013 9:27:00.000 AM
7/9/2013 10:24:00.000 AM
7/9/2013 11:25:00.000 AM
7/9/2013 1:23:00.000 PM
7/10/2013 7:27:00.000 AM

Then we receive the following result from TableB (Out Time)
7/9/2013 9:30:00.000 AM
7/9/2013 10:29:00.000 AM
7/9/2013 1:37:00.000 PM
[NULL]
[NULL]

We may not always get Out Times in TableB so I want to merge these into one table to have the In Time and Out Time in separate columns in that one table. In this example with the red type those should be In Time and Out Time for mapped unique identifiers from each table and yet the purple color coded example would have an In Time of 11:25 AM and the Out Time would remain as NULL.

I am using this block of code but is not working the way I want it to because the 11:25am In Time is getting mapped to the 1:37pm Out Time.

and out_time = (select min (out_time)
FROM tableB WHERE
tableB.record# = tableA.record#
and tableB.loc_id = tableA.loc_id

GROUP BY tableB.record#, tableB.loc_id )

It seems I need to focus on the minimum datediff for each record line but can't figure that part out. Any suggestions or tips is greatly appreciated.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-03 : 13:32:04

Something like this perhaps? It would be easier if you have some other columns in the table that helps you order them

;WITH cte AS
(
	SELECT 
		a1.InTime AS InTime,
		a2.NextTime AS NextTime
	FROM
		TableA a1
		OUTER APPLY
		(
			SELECT MIN(Intime) AS NextTime
			FROM TableA a2
			WHERE a2.InTime > a1.InTime
		) a2
)
SELECT
	c1.Intime,
	c2.OutTime
FROM
	cte c1
	OUTER APPLY
	(
		SELECT MIN(c2.OutTime) AS OutTime
		FROM Table2 c2
		WHERE c2.Outtime >= c1.InTime
		AND c2.OutTime <= c1.InTime
	) c2;

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-09-04 : 02:23:37

quote:

Originally posted by James K

Something like this perhaps? It would be easier if you have some other columns in the table that helps you order them

;WITH cte AS
(
	SELECT 
		a1.InTime AS InTime,
		a2.NextTime AS NextTime
	FROM
		TableA a1
		OUTER APPLY
		(
			SELECT MIN(Intime) AS NextTime
			FROM TableA a2
			WHERE a2.InTime > a1.InTime
		) a2
)
SELECT
	c1.Intime,
	c2.OutTime
FROM
	cte c1
	OUTER APPLY
	(
		SELECT MIN(c2.OutTime) AS OutTime
		FROM Table2 c2
		WHERE c2.Outtime >= c1.InTime
		AND c2.OutTime <= c1.InTimeNextTime
	) c2;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources