Dat Functions - 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)

Dat Functions

Author

Topic

victord
Yak Posting Veteran

64 Posts

Posted - 2002-09-04 : 10:38:01

Dear Folks,

I have two tables in the same database. The first(tmp1) one is supposed to update the second(tmp2) with data based on a date field in tmp1.(The selection criteria is based on date field being equal to system date).

Now tmp1.date column has dates from as far back as a year up till today. Tmp2 is to be updated with data from Tmp1 data(Based on the Tmp1 date field being equal to the current system date).
Further more if the system date is on monday then the selection criteria will be the previous friday's date, else the selection criteria will be the previous day.(Based on the date field being the current system date). Below is an example code of what i am trying to achieve.

update tmp2
set student_id = tmp1.student_id
from tmp1,tmp2
where tmp1.[Identity] = tmp2.[Identity]
and tmp1.date_achieved =Case day(getdate())
when 2 then getDate()-3
else getDate()-1
end

What is the best way to achieve this process.

Thanks in Advance
Vic

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-04 : 11:18:40

Not sure what the question is - you look like you are almost there

you will probably need to compare the date without time if you use an equality.
Also why not usea variable for the date - easier for testing

declare @date datetime
select @date = case when datepart(dw,getdate()) = 2 then dateadd(dd,-3,getdate() else dateadd(dd,-1,getdate()) end
select @date = convert(varchar(8),@date,112)

update tmp2
set student_id = tmp1.student_id
from tmp1
where tmp1.[Identity] = tmp2.[Identity]
and tmp1.date_achieved = @date

You might want to schedule this to run every day.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

victord
Yak Posting Veteran

64 Posts

Posted - 2002-09-06 : 07:52:17

[Thanks NR,
That was spot on.Thanks again, well appreciated. Vic]
Not sure what the question is - you look like you are almost there

you will probably need to compare the date without time if you use an equality.
Also why not usea variable for the date - easier for testing

declare @date datetime
select @date = case when datepart(dw,getdate()) = 2 then dateadd(dd,-3,getdate() else dateadd(dd,-1,getdate()) end
select @date = convert(varchar(8),@date,112)

update tmp2
set student_id = tmp1.student_id
from tmp1
where tmp1.[Identity] = tmp2.[Identity]
and tmp1.date_achieved = @date

You might want to schedule this to run every day.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
[/quote]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources