difficult timed execution stored proc

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)

difficult timed execution stored proc

Author

Topic

syenchik
Starting Member

4 Posts

Posted - 2002-03-13 : 09:21:24

Hello. I am trying to write a stored procedure that executes at midnight every night and checks an employee's hire date from one table and changes a field on another table if today's date is exactly 5 years greater than the start date and the hire date is after 1996.

Here is the code:

CREATE PROCEDURE [usp_check_hiredate]
AS
DECLARE @idfiveyear [int]
SET @idfiveyear=(SELECT DISTINCT id from t_user where hire_date = DATEADD(yy, -5, GETDATE()) AND YEAR(hire_date)>1996)
UPDATE t_timeoff
SET accrual_rate=4.69 WHERE user_id=@idfiveyear and type='vacation'
GO

It's not doing it. Can anyone help me?

S

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-13 : 09:28:48

You can't put multiple rows into a scalar variable (the method you have will only retrieve the last ID in the result set). This should work:

CREATE PROCEDURE [usp_check_hiredate] AS
UPDATE t_timeoff
SET accrual_rate=4.69
FROM t_timeoff INNER JOIN t_user ON (t_timeoff.user_id=t_user.id)
WHERE t_timeoff.type='vacation'
AND t_user.hire_date = DATEADD(yy, -5, GETDATE()) AND YEAR(t_user.hire_date)>1996

This joins the two tables together in order to relate the appropriate rows, and the WHERE clause restricts the update to the 5+ year employees.

Jay99

468 Posts

Posted - 2002-03-13 : 09:30:54

A couple things. Seems to me, that if your start date was exactly five years ago today, the chances of that being pre-1996 are pretty slim, considering is 2002 and all . . .

Also, GETDATE() is giving you date and time to the .001 second, and I am betting 'mad coin' that your hire_date is a datetime populated with a default of getdate() at the time of hire. You hire_data = dateadd(blah) is failing because the dataadd is returning five years ago, just after midnight and your hire_date is prolly like 8:02 am . . . (just a guess though)

Jay

EDIT: yeah, and what Rob said . . .

Edited by - Jay99 on 03/13/2002 09:32:10

syenchik
Starting Member

4 Posts

Posted - 2002-03-13 : 09:53:31

Thanks guys. I'll try the proc you sent and see if that works. Good point with the 1996 thing. The time in hire_date should be set to midnight as a default, though, because it's all manually entered, not with getdate().

Thanks

S

Jay99

468 Posts

Posted - 2002-03-13 : 10:01:26

quote:

The time in hire_date should be set to midnight as a default, though, because it's all manually entered, not with getdate().

maybe so, but I would still take the time out of the comparison. What if the agent doesn't kick of the scheduled job until 00:00:00.001 what if your update takes 5 seconds? . . .

Jay

syenchik
Starting Member

4 Posts

Posted - 2002-03-13 : 10:12:16

Hmm . . . that's true. How can I get around this problem?

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-13 : 10:41:59

CREATE PROCEDURE [usp_check_hiredate] AS
UPDATE t_timeoff
SET accrual_rate=4.69
FROM t_timeoff INNER JOIN t_user ON (t_timeoff.user_id=t_user.id)
WHERE t_timeoff.type='vacation'
AND MONTH(t_user.hire_date) = MONTH(DATEADD(yy, -5, GETDATE()))
AND DAY(t_user.hire_date) = DAY(DATEADD(yy, -5, GETDATE()))
AND YEAR(t_user.hire_date) = YEAR(DATEADD(yy, -5, GETDATE()))
AND YEAR(t_user.hire_date)>1996

I think this will work. If someone knows a more efficient way to accomplish this, I would love to know.

I wish Microsoft would make their functions in SQL comparable to their functions in VB. (ie Date() is Date, Time() is Time, Now() is getDate(), Mid() is Substring or vice versa)

Jeremy

syenchik
Starting Member

4 Posts

Posted - 2002-03-13 : 10:45:53

Thanks, I'll try that.

Jay99

468 Posts

Posted - 2002-03-13 : 12:14:14

or use datadiff with whatever threshold you want . . . ie

abs( datediff(hh, hire_date, getdate() - 5 years)) < 12

Jay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources