May 29, 2016 at 6:01 am
I am working on two tables:
TableA
Id Time
1 08:00:00
2 08:30:00
3 09:00:00
4 09:30:00
5 10:00:00
TableB
Id Time Location
1 07:05:00 Dallas
2 08:31:00 London
3 08:00:00 Paris
4 09:40:00 Dubai
5 10:00:00 Frankfurt
Table A should first chose just one closest record from Table B by time where TableB.Time>TableA.time+30 mins. Then if table A does not have any matching rows from table B, Table A can then be joined with Table B on first come first serve method from unused rows from table B in ascending order . So the resulting table should look like:
Result table
Time Time(B) Location
08:00:00 08:31:00 London
08:30:00 09:40:00 Dubai
09:00:00 10:00:00 Frankfurt
09:30:00 07:05:00 Dallas
10:00:00 08:00:00 Paris
I have tried:
Select a.Time, b.Time as Time(B), Location
from TableA a
join TableB b
on b.Time>dateadd(minute, 30, a.time)
But this gives me each row from table A joined to multiple qualifying row from table B.
Any help would be appreciated.
May 29, 2016 at 7:07 am
You're almost there. A join will always return every valid row, so yes, you'll have multiple joins here.
A better approach would be using APPLY, as follows:
SELECT a.Time, b.Time AS [Time(B)], Location
FROM TableA a
CROSS APPLY (SELECT TOP 1 * FROM TableB ca WHERE ca.Time > DATEADD(minute, 30, a.time) ORDER BY ca.Time ASC) b
I've noticed that you are using greater than, rather than greater than or equal to. Is this intentional? For example, if table a contained a time of 09:00, and table b had a time of 09:30, this would not yield a result.
Cheers!
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 29, 2016 at 8:59 am
I thought it worked but it didn't. I used cross apply and then created two new temp table that didn't have time in the resulting table from cross apply, and joined those two for the remainder. Thank you for the help though. And yes, using greater than was intentional, we didn't need data for the first 30 minutes, but needed data from the 31st min of the time-slab. I have tried further, but any other help would be appreciated.
May 29, 2016 at 9:01 am
itsyourssankalpa (5/29/2016)
That works. I used cross apply and then created two new temp table that didn't have time in the resulting table from cross apply, and joined those two for the remainder. Thank you for the help. And yes, using greater than was intentional, we didn't need data for the first 30 minutes, but needed data from the 31st min of the time-slab.
glad to hear you have solved it....for the benefit of others who may come across this post in the future, would you care to share your solution.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 29, 2016 at 9:56 am
This is what I did.
I am sure there is more efficient way to do this though.
if object_id ('tempdb..#Temp') is not null
drop table #temp
if object_id ('tempdb..#Temp1') is not null
drop table #temp1
if object_id ('tempdb..#Temp2') is not null
drop table #temp2
if object_id ('tempdb..#Temp3') is not null
drop table #temp3
SELECT a.Time, b.Time AS [Time(B)], Location
into #temp
FROM TableA a
CROSS APPLY (SELECT TOP 1 * FROM TableB ca WHERE ca.Time > DATEADD(minute, 30, a.time) ORDER BY ca.Time ASC) b
select *, row_number() over (order by Time) as RowNumber into #temp1 from TableA where time not in (Select Time from #temp)
select *, row_number() over (order by Time) as RowNumber into #temp2 from TableB where time not in (Select [Time(B)] from #temp)
select c.Time, d.Time as [Time(B)], d.Location
into #temp3
from #temp1 c
join #temp2 d
on c.RowNumber=d.RowNumber
select * from #temp
union
select * from #temp3
May 29, 2016 at 10:05 am
itsyourssankalpa (5/29/2016)
This is what I did.I am sure there is more efficient way to do this though.
if object_id ('tempdb..#Temp') is not null
drop table #temp
if object_id ('tempdb..#Temp1') is not null
drop table #temp1
if object_id ('tempdb..#Temp2') is not null
drop table #temp2
if object_id ('tempdb..#Temp3') is not null
drop table #temp3
SELECT a.Time, b.Time AS [Time(B)], Location
into #temp
FROM TableA a
CROSS APPLY (SELECT TOP 1 * FROM TableB ca WHERE ca.Time > DATEADD(minute, 30, a.time) ORDER BY ca.Time ASC) b
select *, row_number() over (order by Time) as RowNumber into #temp1 from TableA where time not in (Select Time from #temp)
select *, row_number() over (order by Time) as RowNumber into #temp2 from TableB where time not in (Select [Time(B)] from #temp)
select c.Time, d.Time as [Time(B)], d.Location
into #temp3
from #temp1 c
join #temp2 d
on c.RowNumber=d.RowNumber
select * from #temp
union
select * from #temp3
that doesnt match your expected results......"Dubai" is repeated
if object_id ('tempdb..tableA') is not null
drop table tableA
if object_id ('tempdb..tableB') is not null
drop table tableB
if object_id ('tempdb..#Temp') is not null
drop table #temp
if object_id ('tempdb..#Temp1') is not null
drop table #temp1
if object_id ('tempdb..#Temp2') is not null
drop table #temp2
if object_id ('tempdb..#Temp3') is not null
drop table #temp3
CREATE TABLE tableA(
Id INTEGER NOT NULL
,Time TIME NOT NULL
);
INSERT INTO tableA(Id,Time) VALUES (1,'08:00:00');
INSERT INTO tableA(Id,Time) VALUES (2,'08:30:00');
INSERT INTO tableA(Id,Time) VALUES (3,'09:00:00');
INSERT INTO tableA(Id,Time) VALUES (4,'09:30:00');
INSERT INTO tableA(Id,Time) VALUES (5,'10:00:00');
CREATE TABLE tableb(
Id INTEGER NOT NULL
,Time TIME NOT NULL
,Location VARCHAR(9) NOT NULL
);
INSERT INTO tableb(Id,Time,Location) VALUES (1,'07:05:00','Dallas');
INSERT INTO tableb(Id,Time,Location) VALUES (2,'08:31:00','London');
INSERT INTO tableb(Id,Time,Location) VALUES (3,'08:00:00','Paris');
INSERT INTO tableb(Id,Time,Location) VALUES (4,'09:40:00','Dubai');
INSERT INTO tableb(Id,Time,Location) VALUES (5,'10:00:00','Frankfurt');
SELECT a.Time, b.Time AS [Time(B)], Location
into #temp
FROM TableA a
CROSS APPLY (SELECT TOP 1 * FROM TableB ca WHERE ca.Time > DATEADD(minute, 30, a.time) ORDER BY ca.Time ASC) b
select *, row_number() over (order by Time) as RowNumber into #temp1 from TableA where time not in (Select Time from #temp)
select *, row_number() over (order by Time) as RowNumber into #temp2 from TableB where time not in (Select [Time(B)] from #temp)
select c.Time, d.Time as [Time(B)], d.Location
into #temp3
from #temp1 c
join #temp2 d
on c.RowNumber=d.RowNumber
select * from #temp
union
select * from #temp3
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 30, 2016 at 3:25 am
Sorry I misunderstood the question initially.
Is this dataset likely to grow at all? If you have multiple "applicable" resuts for a single locations (like dubaiin ths example), I can quickly see this falling over. Making either the SQL need to be quite complex, or it may need to be done iteritively (eww) if you have quite a large list set of data for both times and locations.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply