February 16, 2011 at 6:15 am
How can I pair two records by date, but only use each record once?
pt
Smith, 3/16/2010@0930
Smith, 3/16/2010@1100
Smith, 3/17/2010@1300
pr
Smith, 3/17/2010@1300
Smith, 3/18/2010@1525
Smith, 3/18/2010@2137
select * from
(select pt.*,pr.*, row_no = row_number() over (partition by pt.name order by pr.followup)
from pt join pr on pr.followup between pt.dateseen and dateadd(HOUR,36,pt.dateseen) ) t
where row_no = 1
results in the following pairs:
Smith, 3/16/2010@0930,Smith, 3/17/2010@1300
Smith, 3/16/2010@1100,Smith, 3/17/2010@1300
Smith, 3/17/2010@1300,Smith, 3/18/2010@1525
need it to return the following:
Smith, 3/16/2010@0930,Smith, 3/17/2010@1300
Smith, 3/16/2010@1100,Smith, 3/18/2010@1525
Smith, 3/17/2010@1300,Smith, 3/18/2010@2137
I'm stumped. Thanks in advance for your help.
February 16, 2011 at 7:06 am
How about this?
declare @data1 table ( Name varchar(10), datepar varchar(25))
insert into @data1
select 'Smith', '3/16/2010@0930'
union all select 'Smith', '3/16/2010@1100'
union all select 'Smith', '3/17/2010@1300'
declare @data2 table (Name varchar(10), datepar varchar(25))
insert into @data2
select 'Smith', '3/17/2010@1300'
union all select 'Smith', '3/18/2010@1525'
union all select 'Smith', '3/18/2010@2137'
; with firsttable as
(
select Rn = ROW_NUMBER() OVER(ORDER BY (SELECT 0)) ,Name , datepar
FROM @data1
),
secondtable as
(
select Rn = ROW_NUMBER() OVER(ORDER BY (SELECT 0)) ,Name , datepar
FROM @data2
)
select FT.Name , FT.datepar , st.Name , st.datepar
from firsttable FT
inner join secondtable ST
ON FT.Rn = ST.Rn
{Edit : Added table definitions}
February 16, 2011 at 7:20 am
Just wondering, what leads you to believe the second result is the correct result?
February 16, 2011 at 1:02 pm
I need the records to be paired only once.
Cold Coffee, how could I use the code you listed if I need to pair with data range?
pr.followup between pt.dateseen and dateadd(HOUR,36,pt.dateseen)
February 16, 2011 at 3:01 pm
OK, so long as you don't care which is paired with which that should work well for you.
February 17, 2011 at 12:33 pm
The following doesn't work. Any assistance is greatly appreciated. I need to join data1 to the first record in data2 in the time range, but it cannot have been previously paired.
declare @data1 table ( Name varchar(10), datepar datetime)
insert into @data1
select 'Smith', cast('3/16/2010 09:30' as datetime)
union all select 'Smith', cast('3/16/2010 11:00' as datetime)
union all select 'Smith', cast('3/17/2010 13:00' as datetime)
union all select 'Smith', cast('3/20/2010 09:00' as datetime)
declare @data2 table (Name varchar(10), datepar datetime)
insert into @data2
select 'Smith', cast('3/16/2010 10:00' as datetime)
union all select 'Smith', cast('3/17/2010 13:00' as datetime)
union all select 'Smith', cast('3/19/2010 08:00' as datetime)
union all select 'Smith', cast('3/18/2010 15:25' as datetime)
union all select 'Smith', cast('3/18/2010 21:37' as datetime)
union all select 'Smith', cast('3/20/2010 11:05' as datetime)
; with firsttable as
( select Rn = ROW_NUMBER() OVER(ORDER BY (SELECT 0)) ,Name , datepar
FROM @data1 ),
secondtable as
( select Rn = ROW_NUMBER() OVER(ORDER BY (SELECT 0)) ,Name , datepar
FROM @data2 )
select FT.Name , FT.datepar, st.Name , st.datepar
from firsttable FT
inner join secondtable ST
ON FT.Rn = ST.Rn and ST.datepar between ft.datepar and dateadd(HOUR,72,FT.datepar)
/*
should join the following: (but does not)
Smith, 3/16/2010 09:30 Smith, 3/16/2010 10:00
Smith, 3/16/2010 11:00 Smith, 3/17/2010 13:00
Smith, 3/17/2010 13:00 Smith, 3/18/2010 15:25
Smith, 3/20/2010 09:00 Smith, 3/20/2010 11:05
February 17, 2011 at 1:27 pm
I think you just encountered my point. You haven't spelled out all your business rules yet. You just added another one to your list, but a solution that does what you've askes will probably fail on the next test data set. I'll whip up something that generatea the results you asked for based on the data set you provided but I can almost guarantee it will fail with a different data set, or when you come up with another business rule.
February 17, 2011 at 6:16 pm
Here you go. It's not really a good solution, but it's not a particularly well-defined business problem either. And the tables used in the example are really lacking. This solution uses a poor-man's cursor to get at what you want and then RBAR's its way to the result.
declare @data1 table ( Name varchar(10), datepar datetime)
insert into @data1
select 'Smith', cast('3/16/2010 09:30' as datetime)
union all select 'Smith', cast('3/16/2010 11:00' as datetime)
union all select 'Smith', cast('3/17/2010 13:00' as datetime)
union all select 'Smith', cast('3/20/2010 09:00' as datetime)
declare @data2 table (Name varchar(10), datepar datetime)
insert into @data2
select 'Smith', cast('3/16/2010 10:00' as datetime)
union all select 'Smith', cast('3/17/2010 13:00' as datetime)
union all select 'Smith', cast('3/19/2010 08:00' as datetime)
union all select 'Smith', cast('3/18/2010 15:25' as datetime)
union all select 'Smith', cast('3/18/2010 21:37' as datetime)
union all select 'Smith', cast('3/20/2010 11:05' as datetime);
DECLARE @Temp TABLE
(
Num1 int,
Num2 int,
Name1 varchar(10),
Name2 varchar(10),
datepar1 datetime,
datepar2 datetime
);
WITH CTE
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY f.datepar) AS num,
f.Name,
f.datepar
FROM @data1 f
),
CTE2
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY s.datepar) AS num,
s.Name,
s.datepar
FROM @data2 s
)
INSERT INTO @Temp
(
Num1,
Num2,
Name1,
Name2,
datepar1,
datepar2
)
SELECT c1.num,
c2.num,
c1.Name,
c2.Name,
c1.datepar,
c2.datepar
FROM CTE c1
INNER JOIN CTE2 c2
ON c2.datepar BETWEEN c1.datepar AND dateadd(HOUR, 72, c1.datepar);
DECLARE @m INT,
@i INT = 1;
SELECT @m = MAX(Num1)
FROM @Temp;
DECLARE @r TABLE
(
Num2 int PRIMARY KEY,
Name1 varchar(10),
Name2 varchar(10),
datepar1 datetime,
datepar2 datetime
);
WHILE @i <= @m
BEGIN
INSERT INTO @r
(
Num2,
Name1,
Name2,
datepar1,
datepar2
)
SELECT TOP(1) t1.Num2,
t1.Name1,
t1.Name2,
t1.datepar1,
t1.datepar2
FROM @Temp t1
WHERE t1.Num1 = @i
AND NOT EXISTS
(
SELECT 1
FROM @r r1
WHERE t1.Num2 = r1.Num2
);
SET @i = @i + 1;
END;
SELECT Name1,
datepar1,
Name2,
datepar2
FROM @r;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply