join 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)

join dates

Author

Topic

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-10-07 : 03:33:03

[code]
How can i join these tables

derived table
[due dates]
1/15/2005
1/30/2005
2/15/2005
2/28/2005

table 1
[date_paid]
1/13/2005
1/30/2005
2/18/2005

how to join these two tables so that it would result to

[due dates] [date_paid]
1/15/2005 1/13/2005
1/30/2005 1/30/2005
2/15/2005 NULL
2/28/2005 2/18/2005

1. join [date_paid] less than first due date (1/15/2005)
2. join [date_paid] between previous due_date and current due date
ex. 1/15/2005 - 1/30/2005 joins 1/13/2005
1/30/2005 + 1 day - 2/15/2005 joins 1/30/2005

[/code]

TCC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-07 : 23:38:50

[code]
declare @due_dates table
(
[due_date] datetime
)
insert into @due_dates
select '1/15/2005' union all
select '1/30/2005' union all
select '2/15/2005' union all
select '2/28/2005'

declare @date_paid table
(
[date_paid] datetime
)
insert into @date_paid
select '1/13/2005' union all
select '1/30/2005' union all
select '2/18/2005'

select *
from @due_dates d left join @date_paid p
on p.date_paid <= d.due_date
and p.date_paid > isnull((select max(due_date)
from @due_dates x
where x.due_date < d.due_date), 0)
order by d.due_date

/* RESULT :
due_date date_paid
----------- -----------
2005-01-15 2005-01-13
2005-01-30 2005-01-30
2005-02-15 NULL
2005-02-28 2005-02-18

(4 row(s) affected)
*/
[/code]

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-10-16 : 05:02:38

thanks a lot bro

TCC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources