Min with DateDiff Problem - 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)

Min with DateDiff Problem

Author

Topic

devinev
Starting Member

16 Posts

Posted - 2004-09-29 : 21:39:11

I have two tables, T1 and T2, and I need to update columns in T1 from corresponding columns in T2 based on the datetime column (D2) in T2 being the closest match to the datetime column (D1) in T1. I just cannot get it right. What folllows is one of the many numerous attempts I have made to get what I want to give you an idea of what I am looking for. The language construct is incorrect.

Update T1 Set F1=F1, F2=F2 from T2
Where exists (select Min(DateDiff(ss,,T1.D1,T2.D2))
where T1.F3 = T2.F3)

Help

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-29 : 21:50:35

This Might help
create table #t1(F1 int,F2 int)
INSERT INTO #t1
select 1,1
union all
select 2,1
union all
select 3,1

select * from #t1
create table #t2(F1 int,F2 int)
INSERT INTO #t2
select 1,2
union all
select 2,2
union all
select 3,2

select * from #t2
update #t1 set #t1.F2=#t2.F2 from #t2 where #t1.F1=#t2.F1

select * from #t1

DROP table #t1
DROP table #t2

mk_garg

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-29 : 22:10:20

closest date overall, or closest date just before or just after your starting date?

- Jeff

devinev
Starting Member

16 Posts

Posted - 2004-10-02 : 12:01:50

I think I need to rephrase my problem. I have two tables. I wish to update three fields in all rows in table 1 from table 2 based on the datetime field in Table 1 being equal to the datetime field in Table 2 or the closest datetime field from Table 2. For the closest datetime situation Table 2's datetime field it can be before or after the datetime field in table 1.

Thanks MK_garg20 but what you provided will not fill the bill.

-- Vee

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-02 : 13:40:16

Update T1 Set
F1=(select top 1 F1 from T2 where T2.F3=T1.F3 order by Min(abs(DateDiff(ss,T1.D1,T2.D2)))) ,
F2=(select top 1 F2 from T2 where T2.F3=T1.F3 order by Min(abs(DateDiff(ss,T1.D1,T2.D2))))
FROM T1

devinev
Starting Member

16 Posts

Posted - 2004-10-02 : 20:47:10

Thanks VIG.
One would think that your suggestion would work. I tried it with some minor modification (Changed "From T1" to "From T2" at the end of the statement) and received the following error:
"Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression."
Got any ideas what this means?
-
vee

devinev
Starting Member

16 Posts

Posted - 2004-10-02 : 22:10:44

Thanks to all who replied especially VIG who set me on the right track. The following appeared to work:

Update T1 Set
F1=(select top 1 F1 from T2 where T2.F3=T1.F3 order by DateDiff(ss,T1.D1,T2.D2)asc) ,
F2=(select top 1 F2 from T2 where T2.F3=T1.F3 order by DateDiff(ss,T1.D1,T2.D2)asc)
FROM T2

- Vee

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-03 : 04:34:34

2 devinev
Sorry,Min - it's my mistake.
But you wrote:"For the closest datetime situation Table 2's datetime field it can be before or after the datetime field in table 1."
That is why I think that: order by abs(DateDiff(ss,T1.D1,T2.D2)) - better decision
P.S Sorry for my bad English

devinev
Starting Member

16 Posts

Posted - 2004-10-03 : 11:59:39

Vig
I think you are right but SQL server will not allow me to use an aggregate in this way. The SQL statement would have to be in a different type of construct.

- Vee

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-03 : 12:09:54

2 devinev
abs() is not aggregate function

devinev
Starting Member

16 Posts

Posted - 2004-10-05 : 10:00:25

Vig

You are right abs() is not an aggregate - I was still in min(abs) mode. I tried the absolute function and it works great. Thanks again.

- Vee

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources