Query to evaluation a date - 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

SQL Server Development (2000)

Query to evaluation a date

Author

Topic

pmak
Starting Member

41 Posts

Posted - 2006-06-27 : 21:49:39

Two tables A & B
Table A has two fields, item_id (primary key), date_1(datetime)
Table B has three fields, id(primary key),item_ID(secondary key), date_2(datetime)

Table A one to many relationship to Table B with the item_id field

I need a query to return the records on Table B where item_id is equal to item_id in Table A, also the date_2 in Table B has the closest value to the date_1 in Table A
Example
Table A
item_id date_1
1 06/25/2006
2 06/12/2006

Table B
id item_id date_2
1 1 06/20/2006
2 1 06/23/2006
3 1 06/27/2006
4 2 06/10/2006
5 2 06/11/2006
6 2 06/14/2006

*if two of the date_2 has the same closest date to date_1, selec the closest date_2 date before the date_1 date.

For the above example, the query will return the record with id 2 and 5 in Table B.

Thanks

Paul Mak

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-06-27 : 22:45:18

Here is one possible way:


set nocount on
declare @TableA table (item_id int, date_1 datetime)
insert @TableA
select 1, '06/25/2006' union all
select 2, '06/12/2006'

declare @TableB table (id int, item_id int, date_2 datetime)
insert @tableb
select 1, 1, '06/20/2006' union all
select 2, 1, '06/23/2006' union all
select 3, 1, '06/27/2006' union all
select 4, 2, '06/10/2006' union all
select 5, 2, '06/11/2006' union all
select 6, 2, '06/14/2006'

select	b.*
from	@tableA a
join	@tableB b
		on b.item_id = a.item_id
where	b.id in (
		select top 1 id 
		from @tableB c
		where c.item_id = a.item_id 
		order by abs(datediff(day, a.date_1, c.date_2)) asc
			,c.date_2
		)

output:
id          item_id     date_2                 
----------- ----------- -------------------------
2           1           2006-06-23 00:00:00.000
5           2           2006-06-11 00:00:00.000

Be One with the Optimizer
TG

pmak
Starting Member

41 Posts

Posted - 2006-06-27 : 22:59:06

Thank you TG. However in my real SQL database, Table A has over 17,000 rows of records and Table B has over 600,000 rows of records. How can I construct the union query with this number of rows of records? Also I forgot to mention in my first post if the date_1 equal to date_2, this will be the record to be returned by the query.

Paul Mak

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-28 : 00:20:03

the UNION ALL part of the query is only for TG to create the data to demonstrate the query. You only required the query in blue color.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources