Table joins using dates - SQL Server Forums

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

Transact-SQL (2000)

Table joins using dates

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-09-29 : 07:51:37

Hello,

Im doing some work where I have a 2 tables that im having real problems joining together.
I have simplified the problem (and written some Test SQL) and it seems I need to join using dates and ranges?

I have 2 tables #table1 and #table2;

Table1 contains Activity Start and End dates.
Table2 contains Activity Dates – (like a daily log)

I have pasted some T-SQL to try explain my problem and simplified the fields etc…

Can anyone tell me how its best to join the data so I result in the #FinalTable example?

Any advice will be much appreciated
Thanks

---------------------------------------------
/*
Please paste T-SQL into query window
*/
---------------------------------------------
CREATE TABLE #table1

(
CustID nvarchar(6),
ActivityStartDate [datetime] NULL,
ActivityEndDate [datetime] NULL,
DataAssociated char(2)
)

GO
SET ANSI_PADDING OFF

Insert into #table1
select '012345','2008-01-09 00:00:00.000','2008-01-10 00:00:00.000','23' UNION all
select '012345','2008-01-10 00:00:00.000','2008-01-14 00:00:00.000','19' UNION all
select '012345','2008-01-20 00:00:00.000','2008-01-23 00:00:00.000','08'

select * from #table1

CREATE TABLE #table2
(
CustID nvarchar(6),
ActivityDate [datetime] NULL
)

Insert into #table2
select '012345','2008-01-09 00:00:00.000' UNION all
select '012345','2008-01-10 00:00:00.000' UNION all
select '012345','2008-01-11 00:00:00.000' UNION all
select '012345','2008-01-12 00:00:00.000' UNION all
select '012345','2008-01-13 00:00:00.000' UNION all
select '012345','2008-01-14 00:00:00.000' UNION all
select '012345','2008-01-20 00:00:00.000' UNION all
select '012345','2008-01-21 00:00:00.000' UNION all
select '012345','2008-01-22 00:00:00.000' UNION all
select '012345','2008-01-23 00:00:00.000'

select * from #table2

--Table I would like to make using a join on #table1 and #table2

CREATE TABLE #FinalTable

(
CustID nvarchar(6),
ActivityStartDate [datetime] NULL,
ActivityEndDate [datetime] NULL,
DataAssociated char(2),
ActivityDate [datetime] NULL
)

GO
SET ANSI_PADDING OFF

Insert into #FinalTable
select '012345','2008-01-09 00:00:00.000','2008-01-10 00:00:00.000','23','2008-01-09 00:00:00.000' UNION all
select '012345','2008-01-10 00:00:00.000','2008-01-14 00:00:00.000','19','2008-01-10 00:00:00.000' UNION all
select '012345','2008-01-10 00:00:00.000','2008-01-14 00:00:00.000','19','2008-01-11 00:00:00.000' UNION all
select '012345','2008-01-10 00:00:00.000','2008-01-14 00:00:00.000','19','2008-01-12 00:00:00.000' UNION all
select '012345','2008-01-10 00:00:00.000','2008-01-14 00:00:00.000','19','2008-01-13 00:00:00.000' UNION all
select '012345','2008-01-10 00:00:00.000','2008-01-14 00:00:00.000','19','2008-01-14 00:00:00.000' UNION all
select '012345','2008-01-20 00:00:00.000','2008-01-23 00:00:00.000','08','2008-01-20 00:00:00.000' UNION all
select '012345','2008-01-20 00:00:00.000','2008-01-23 00:00:00.000','08','2008-01-21 00:00:00.000' UNION all
select '012345','2008-01-20 00:00:00.000','2008-01-23 00:00:00.000','08','2008-01-22 00:00:00.000' UNION all
select '012345','2008-01-20 00:00:00.000','2008-01-23 00:00:00.000','08','2008-01-23 00:00:00.000'

select * from #FinalTable

drop table #table1
drop table #table2
drop table #FinalTable

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 07:58:17

[code]SELECT *
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.CustID = t1.CustID
WHERE t2.ActivityDate >= t1.ActivityStartDate
AND t2.ActivityDate < t1.ActivityEndDate[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 07:58:39

[code]SELECT *
FROM #Table1 t1
INNER JOIN #Table2 t2
on t1.CustID=t2.CustID
t2.ActivityDate BETWEEN t1.ActivityStartDate AND t1.ActivityEndDate[/code]

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-09-29 : 09:48:33

thats great - got both working on my system.
placed each version in execution plan and each statement split resources at exactly 50%!
...so no winner :-)

This has been most useful + i think i will use this type of date join again!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 10:54:22

But they will yield different results...
Depending on your business rules, you have to decide which one is correct.

My suggestion is an "open-ended" suggestion which also takes into account time part if when business rules change in the future.

Visakhs suggestion inlcudes the ending date (time) too.

E 12°55'05.63"
N 56°04'39.26"

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-09-29 : 11:06:46

spot on again peso!
I had noticed they yielded different results with Live data.
I will take your comments on-board.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources